CP363 : Security

Why security?

• Legal and ethical issues (underage offender's names, privacy laws)
• Policy issues ("we don't do that" - release medical information, can't look at other students marks)
• System issues (who do we allow to update or delete - allows easy control and accountability)
• Security levels (unclassified (U), confidential (C), secret (S), top secret (TS), you gotta die for seeing it)

1. Large databases must usually allow different type of access to different users. (see/update all, some, none of a database)

   Two kinds of database security mechanisms:

   • Discretionary security mechanisms: grant access privileges to data objects in a specified mode
   • Mandatory security mechanisms: data is classified into categories (security classes). Users with given security level may see only data at their level or below.
2. Must control access to the database as a whole:
   • Access Control - control access to database through user id and passwords
3. Data must be protected when not in the database itself, such as when being transmitted to an external user (i.e. a terminal)
   • data encryption
4. Need controls on access to statistical databases
   • idea is to allow viewing of aggregate data without access to details - too fine an aggregation can pinpoint individual data

DBA Account has powers beyond that of mere mortal users (superuser account). DBA Account: allows:

1. account creation - controls general access
2. privilege granting and revocation - controls discretionary authorization
3. security level assignment - controls mandatory authorization

Database accounts and passwords are kept in an encrypted account table. User must log in to use database.

User actions can be kept in the system log - log keeps track of who, as well as what and when. Allows a database audit - can look for tampering.

Discretionary Access Control

An authorization identifier refers to a user account or group of user accounts.

There are two privilege levels:

1. Account Level - assigns privileges according to who the user is, indepedent of tables. Are users allowed to create tables or views? Procedures, triggers, or functions?
2. Table Level - assigns privileges to particular table or view. Are users allowed to see particular tables or attributes? Are they allowed to update, delete, or insert tuples in a table? (Could raise a cascade problem).

An owner account is assigned to a table, view, procedure, function when created.

Ex: CREATE TABLE DBA.Researcher ...

The privileges are:

• alter: change the table
• delete: delete tuples
• insert: insert tuples
• reference: reference this table for constraints: ex: foreign keys
• select: select tuples
• update: update tuples

alter, delete, and insert work on all columns

reference, select, and update can be restricted to particular columns

grant option: user may in turn grant their permission to other users

Permission to see only certain tuples of a table cannot be granted directly. However, you can grant permission to select from a view that is limited to certain tuples through a WHERE clause. For example, the following view shows only those employees whose Grade is less than 3 (the mid-manager grade):

CREATE VIEW Mid_Manager
  AS SELECT * FROM Employees
  WHERE Grade < 3

The Vice_President view, on the other hand, shows all employees whose Grade is less than 5. (Vice-presidents themselves are at grade 5, so they can see all employees below them, but not each other):

CREATE VIEW Vice_President
  AS SELECT * FROM Employees
  WHERE Grade < 5

Thus mid-managers are granted select access to the Mid_Manager view, and vice-presidents are granted access to the Vice_President view - neither are allowed to access the Employees table directly.

Privileges may be revoked by the database owner or table owner.

Most DBMSs allow only for discretionary security control.

Mandatory Access Control

Designed into a secure database schema rather than being part of a DBMS.

Data access has two restrictions:

1. a user is not allowed read access to an object of higher security classification - stops people from seeing things they shouldn't see
2. a user is not allowed to write an object of lower security classification - stops people from lowering the security access of data by copying it to a lower security level. (Could, theoretically, write a object of higher classification level).

Some tuples may appear more than once with different security levels. (In previous example, someone writes data to a higher security level: we don't want existing data overwritten.)

Statistical Database Security

• allow only queries with aggregate operators
• allow only queries where number of tuples returned do not fall below a certain level

This prevents a user from identifying a single tuple by narrowing the selection conditions.