Can perform all our usual operations within a schema CREATE, DROP, ALTER DROP VIEW DROP TABLE ALTER TABLE

A schema looks like a distinct database Many of the same operations can be performed within both E.g CREATE objects, assign permissions etc So why not have a separate DB instead of multiple schemas? Database is the main physical container contains data and log files and all the schemas within it. All backup and restore operations are performed at database level Schema is a logical container Enables ease of permissions setting Decision whether to partition by schema or database (e.g SalesDB, PersonnelDB) needs to be taken based on specific organisational considerations

A view can be thought of as a ‘virtual table’ Does not necessarily exist in physical form As opposed to base tables whose tuples are actually stored in database files Views are used to ‘focus, simplify and customise the perception a user or group has of a database. Views can be used as security mechanisms by letting users access data through the view, without granting permissions to directly access the underlying base tables of the view

If the same query is frequently executed on a database it makes sense to define a view based on results of the query and use a simpler query to retrieve tuples of interest from the view Particularly useful if the original query is complex – involving a number of joins Reducing need for querying users to specify join conditions reduces scope for error User can simply perform following query on OrderDetails SELECT FROM OrderDetails WHERE CustNo = 12345

Views can be queried using same SELECT operations as base tables Join operations can be performed between views and tables Should we really do this? Views can improve DB security Provides a projection of specified base data items Can be a subset of a specific entity E.g. a StaffMember view may omit social security and other sensitive fields from underlying entity

Week 10 Flashcards by Unknown Unknown

Schemas, views, access controls

All are ways of partitioning, segregating and controlling access to data within a database
Why do we want to do this?
- Security – Prevent unauthorised access to read and (heaven forbid) modify data
- Usability – Presenting a simplified view of underlying data structures can make working with data easier, particularly for non –expert users
- Flexibility – partitioning according to users can allow experimentation without affecting other users

How well did you know this?

Not at all

Perfectly

Database schemas
What’s a schema?

A SQL database contains multiple objects such as tables, views, stored procedures, indexes, triggers etc
A schema is a logical collection of database objects
- Loosely analogous to a namespace in languages such as Java, C#
Note: this material focusses on ‘full’ SQL – some features may not be implemented in certain DBMS that implement a subset of full
SQL (such as SQLite)

How well did you know this?

Not at all

Perfectly

How can we organise schemas?

Logical grouping by object function
- Sales schema contains objects pertinent to sales dept
- Personnel schema contains staff objects
Logical grouping by user
- Individual users/groups may be given their own space in a database
- E.g. each developer in a team may have their own ‘sandpit’ in a development database where they can make changes to their own set of objects without affecting other developers|

How well did you know this?

Not at all

Perfectly

Schema partition by object domain

How well did you know this?

Not at all

Perfectly

Schema partition by user – developers
working on ‘Sales’

How well did you know this?

Not at all

Perfectly

Conceptual schema definition

To create a schema:
CREATE SCHEMA [schema_name]
Authorisation [owner_name]
In our user based example:
CREATE SCHEMA Carol
Authorisation Carol
[owner_name] is (initially) only user able to create and access objects within the schema
A user can be specified as belonging to a default schema

How well did you know this?

Not at all

Perfectly

Conceptual schema definition 2

To create tables for a schema explicitly
CREATE TABLE [schema_name].[table_name]
[table definition…]
To create tables for a schema implicitly
CREATE TABLE [table_name]
[table definition …]
* Default/current schema for defining user is used

How well did you know this?

Not at all

Perfectly

Schema operations

Can perform all our usual operations within a schema
- CREATE, DROP, ALTER
- DROP VIEW
- DROP TABLE
- ALTER TABLE

How well did you know this?

Not at all

Perfectly

Cross schema considerations

Objects can have the same name in different schemas
- Sue.Customer, John.Customer
To access an object outside of default/assigned schema specify the schema name
- For Sue:
  - SELECT * FROM Customer, SELECT * FROM Sue.Customer are analogous
  - SELECT * FROM John.Customer accesses the [John] schema
    - Subject to access permissions
- Similar with Joins etc

How well did you know this?

Not at all

Perfectly

Schemas or databases

A schema looks like a distinct database
- Many of the same operations can be
  performed within both
  - E.g CREATE objects, assign permissions etc
- So why not have a separate DB instead of multiple schemas?
Database is the main physical container contains data and log files and all the schemas within it.
- All backup and restore operations are performed at database level
Schema is a logical container
- Enables ease of permissions setting
Decision whether to partition by schema or database (e.g SalesDB, PersonnelDB) needs to be taken based on specific organisational considerations

How well did you know this?

Not at all

Perfectly

Views

A view can be thought of as a ‘virtual table’
- Does not necessarily exist in physical form
- As opposed to base tables whose tuples are actually stored in database files
Views are used to ‘focus, simplify and customise the perception a user or group has of a database. Views can be used as security mechanisms by letting users access data through the view, without granting permissions to directly access the underlying base tables of the view

How well did you know this?

Not at all

Perfectly

Creating a view

How well did you know this?

Not at all

Perfectly

Building a view from base entities

How well did you know this?

Not at all

Perfectly

If the same query is frequently executed on a database it makes sense to define a view based on results of the query and use a simpler query to retrieve tuples of interest from the view
- Particularly useful if the original query is complex – involving a number of joins
- Reducing need for querying users to specify join conditions reduces scope for error
User can simply perform following query on OrderDetails
- SELECT * FROM OrderDetails WHERE CustNo = 12345

How well did you know this?

Not at all

Perfectly

Views can be queried using same SELECT operations as base tables
Join operations can be performed between views and tables
- Should we really do this?
Views can improve DB security
- Provides a projection of specified base data items
  - Can be a subset of a specific entity
    - E.g. a StaffMember view may omit social security and other sensitive fields from underlying entity

How well did you know this?

Not at all

Perfectly

Access control : security in SQL
Mandatory access control

Level based access
Each database object is assigned a classification level
Levels form a strict ordering
E.g. top secret > secret > confidential > public > not classified
Each subject (users or programs) is given a clearance level
To access an object, a subject requires the necessary clearance to read or write…
See the Bell-LaPadula access control model (1974)
Very few (no?) DBMS use this approach
- Why?
  - Seniority vs. appropriateness of access

Discretionary access control

Each user is given appropriate access rights (or privileges) on specific database objects
Users obtain certain privileges when they create an object and can pass some or all of these privileges on to other users at their
discretion
This is the approach used in SQL

Authorisation identifier

A SQL identifier used to establish the identity of a user
The DBA sets up your username and usually a password
- Other mechanisms exist for some DBMS, e.g Microsoft SQL Server trusted connections, which use underlying windows domain credentials
Every SQL statement executed by the DBMS is performed on behalf of a specific user

Authorisation identifier
Access rights associated with a user determine:

What database objects a user can reference and
What operations can be performed by the user on which objects

Ownership

Each object created in SQL has an owner
The owner is identified by the auth identifier defined in the authorization clause of the schema to which the object belongs
Owner is initially the only person who knows the object exists and subsequently performs operations on that object

Privileges

ISO standard defines the following privileges among others
SELECT – To retrieve data from a table/view
INSERT – To insert new rows into a table, can be restricted to specific columns
UPDATE – To modify rows of data in a table, can be restricted to specific columns
DELETE – To delete rows of data from a table
REFERENCES – To reference columns of a table named in integrity constraints, can be restricted to specific columns

Create table

When you create a table you are the owner and have full privileges
Other users have no access and must be granted privileges by the owner
When you create a view you are the owner of the view, but you may not have full privileges
You must have select privileges on the base table(s) to create the view in the first place

The grant command

Grant example

GRANT ALL PRIVILEGES
ON Staff
TO manager
WITH GRANT OPTION

The user manager can now retrieve rows from the Staff table and also insert, update and delete
The manager can pass these privileges on to other users

Revoking privileges from users

* The REVOKE statement can remove all or some of the privileges previously granted * REVOKE [privilege_list | ALL PRIVILEGES ] * ON object_name * FROM [Authorization_list | PUBLIC]

Revoke example

REVOKE ALL PRIVILEGES ON Staff FROM manager * Remove all the privileges you gave to the manager on the Staff table

Role based security

* In most DBMS, typical approach is not to give permissions to individual users * Instead define groups or roles based on the activities certain types of user enact * E.g. Accounts, Sales, Managers, Auditors * Allocated user to one or more groups as appropriate * Grant object priviliges to each group as appropriate * DBA is a special role – has full privileges over entire database

Security consideration #1

* Don’t over grant permissions * Best practice as with all aspects of software development and system administration: * Start with a totally locked down database and grant access as necessary * Closing down an open system invariably leaves holes and security risks!