Database Security: Chapter 24 - Section 24.1, 24.2 & 24.3

Database Security
Database security deals with access control to data of a

database.
There are various access control mechanism.

• Discretionary access control (DAC) &
• Mandatory access control (MAC)
• Role-based access control
DAC mechanism grants & revokes privileges to database

users to control access to a database system.
Chapter 24 – Section 24.1, 24.2 & 24.3
1
Discretionary access control
Account level privileges are
privileges that are independent of relations are account level
privileges.
Some examples of account level privileges are

• privilege to create schema or table,
• change the schema,
• drop a table, view, constraint, column of table
• privilege to create view,
• modify privilege (insert, update, delete) &
• retrieval privilege (select privilege)
2
Relation level privilege
Relation level privilege (privilege on relation / view)

• User access to a relation or one or more attributes of a
relation or to a view
• select privilege on a relation,
• modify (insert, update, delete) privilege on a relation,
• modify (insert, update) certain attributes of a relation,
• reference privilege on a relation,
may be restricted to specific attributes of the relation
3
DBA creates account
user1, user2, user3, user4
SQL> create user <user_name> identified by

<password>;
SQL> grant create session to user1;
Assume that user1 has been given necessary privileges.
user1 logs in
SQL> grant select, insert, update, delete on employee,
to user2;
user1 gives rights to user2 to access & modify
his/her employee table.
4
grant & revoke
user1 logs in
SQL> grant select on employee to user2 with grant option;
Gives retrieval right to user2 on employee table
user2 logs in
SQL> grant select on employee to user3;
user1 logs in
SQL> revoke select on employee from user2;
5
Privileges may also be specified through
views
Create a view & then grant privilege on the view to a user.
user1 logs in
create view empview1 as select ssn, fname, lname, address
from employee;
grant select on empview1 to user3;
create view empview2 as select * from employee where

dno = 5;
grant select on empview2 to user4;
6
Revoke privilege
Use revoke command of SQL to cancel privilege.
SQL> revoke <privilege> on <table_name> from

<user_name>
SQL> revoke select on empview1 from user3;
SQL> revoke select on empview2 from user4;
7
Propagating privilege using grant option
user1 grants select privilege on employee table to

user2 with grant option
user1 logs in
SQL> grant select on employee to user2 with grant option
SQL> grant select on employee to user3 with grant option
user2 logs in
SQL> grant select on employee to user4;
8
Granting relation level privilege
user1 logs in
SQL> grant insert, update on employee, department to

user3
SQL> grant update on employee(salary) to user4;
Update & insert privileges may be attribute specific but
select & delete cannot be attribute specific (use view)
9
Authorization model in DAC
Access matrix model
Subject (user, account, program)
Object (relations, tuples, attributes, views, operations)
Elements m(i, j) = type of privilege the subject i has on

the object j
10
An access matrix
User\Relation Customer Order Product Employee
Sales President Read Read Read Read

Write
Sales Manager Read Read Read Read
Write Write
Sales Read Read Read Read

Representative Write Write
Stock Manager Read Read

Write
11
Mandatory access control
Classifies data (objects) & users (subject) based on security

classes.
Typical security classes are

TS (top-secret), (Oracle calls it HIGHLY_SENSITIVE)
S (secret), (SENSITIVE)
C (confidential) &
U (unclassified)
TS ≥ S ≥ C ≥ U
12
Bell-LaPadula model
(A multi-level security model)
Classifies subject (user, account, program) &

object (relations, tuples, attributes, views, operations)
into TS, S, C & U
Restriction in Bell-LaPadula model dictates access to a data

item by a subject.
13
Restriction in Bell-LaPadula model
Simple security property

– Reading:
class (Sub) ≥ class (Obj)
Star property – Writing
class (Sub) ≤ class (Obj)
TS ≥ S ≥ C ≥ U
14
Multi level relation schema
R(A1, C1, A2, C2, …, An, Cn, TC)
Apparent key –
The set of attributes that constitute primary key of a regular
relation is called apparent key.
15
Appearance of multi level relation schema
Appearance of EMPLOYEE relation to a user with clearance

level C
16
Appearance of multi level relation schema
Appearance of EMPLOYEE relation to a user with

clearance level U
17
Polyinstantiation
Polyinstantiation is defined as having several tuples with

the same apparent key value but with different attribute
values for users at different clearance levels.
18

Database Security: Chapter 24 - Section 24.1, 24.2 & 24.3

Uploaded by

Copyright:

Available Formats

Database Security: Chapter 24 - Section 24.1, 24.2 & 24.3

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Database Security: Chapter 24 - Section 24.1, 24.2 & 24.3

Uploaded by

Copyright:

Available Formats

Database Security

Database security deals with access control to data of a

There are various access control mechanism.

DAC mechanism grants & revokes privileges to database

Some examples of account level privileges are

Relation level privilege (privilege on relation / view)

SQL> create user <user_name> identified by

Assume that user1 has been given necessary privileges.

grant select on empview1 to user3;

create view empview2 as select * from employee where

grant select on empview2 to user4;

Use revoke command of SQL to cancel privilege.

SQL> revoke <privilege> on <table_name> from

SQL> revoke select on empview2 from user4;

user1 grants select privilege on employee table to

SQL> grant select on employee to user3 with grant option

SQL> grant insert, update on employee, department to

Update & insert privileges may be attribute specific but

select & delete cannot be attribute specific (use view)

Access matrix model

Subject (user, account, program)

Object (relations, tuples, attributes, views, operations)

Elements m(i, j) = type of privilege the subject i has on

Sales President Read Read Read Read

Sales Read Read Read Read

Stock Manager Read Read

Classifies data (objects) & users (subject) based on security

Typical security classes are

Classifies subject (user, account, program) &

Restriction in Bell-LaPadula model dictates access to a data

Simple security property

class (Sub) ≥ class (Obj)

Star property – Writing

class (Sub) ≤ class (Obj)

R(A1, C1, A2, C2, …, An, Cn, TC)

Appearance of EMPLOYEE relation to a user with clearance

Appearance of EMPLOYEE relation to a user with

Polyinstantiation is defined as having several tuples with

You might also like