DATABASE SECURITY AND

AUTHORIZATION

Chapter
1 -6 Introduction to Database Security Issues
Contents
2

 Security types
 Threats of database
 Security mechanism
 Here we discuss the techniques used for protecting the
database against persons who are not authorized to access
either certain parts of a database or the whole database.
Introduction to Database Security Issues
3

• Authentication means confirming your own identity,

− It is the process of verifying who you are.
− There are three common factors used for authentication: Something
you know (such as a password) Something you have (such as a
smart card) Something you are (such as a fingerprint or other
biometric method)
• Authorization  means granting access to the system.
• In simple terms, It is the process of verifying what you have access to.
Types of Security
4

• Legal and ethical issues- Various legal and ethical issues

regarding the right to access certain information.
• Who has the right to read What information?
• Policy issues - At the governmental, institutional, or corporate
level as to what kinds of information should not be made
publicly available.
• Who should enforce security (government, corporations) ?
• System-related issues- whether a security function should
be handled at the physical hardware, the operating system, or the
DBMS level.
Threats to databases
5

• Loss of integrity: (users should be able to modify things they are not
supposed to.)

E.g., Only students’ can see grades.

• Loss of confidentiality(secrecy): (users should be able to see things they are

not supposed to.)

ƒ E.g., A student can’t see other students’ grades.

• Loss of availability:(data or a system is not available when needed by a

user.)
o Confidentiality, integrity and availability, also known

as the CIA triad, is a model designed to guide policies

for information security within an organization.
Con…
6

 Data integrity in the database is the correctness, consistency

and completeness of data.
 Data integrity is enforced using the following three integrity
constraints:
− Entity Integrity
− Referential Integrity
− Domain Integrity 
Continued..
7

… To protect databases against these types of threats four kinds of

countermeasures can be implemented :
• Access control,
• Inference control,
• Flow control and
• Encryption
… A DBMS typically includes a database security and authorization subsystem
• Two types of database security mechanisms:
Discretionary security mechanisms
Mandatory security mechanisms
Continued..
8

… Access control - handled by creating user accounts and passwords

to control login
… Controlling the access to a statistical database - used to provide
statistical information based on criteria.

… The countermeasures to statistical database security problem - is

called inference control measures.

… Flow control - prevents information from flowing to unauthorized

users.
− Channels that are pathways for information to flow implicitly in ways that
violate the security policy of an organization are called covert channels.
Continued..
9

 A final counter measure is data encryption,

 used to protect sensitive data (such as credit card
numbers) transmitted thro’ communication network.
The data is encoded using some coding algorithm.
Deciphering is required by authorized users to decode
or decrypt algorithms (or keys).
Database Security and the DBA
10

… The database administrator (DBA) -

 central authority for managing a database system.
 responsible for the overall security of the database system

… The DBA has a DBA account in the DBMS - called system or superuser account,
… Following are the major responsibilities of a DBA:
 Account creation
 Privilege granting
 Privilege revocation
 Security level assignment
Access Protection, User Accounts,
and Database Audits
11
… To use Db user needs an account
… The DBA will create a new account number and password
… The user must log in to the DBMS using account number and password
… The database system
 keep track of all operations on the database that are applied by a certain user
 in each login session
 In the system log
… If any tampering with the database is suspected,
 a database audit is performed,
 This consists of
 reviewing the log -
 to examine all accesses and operations applied to the database
 during a certain time period.
… A database log that is used mainly for security purposes is sometimes called an audit trail.
Discretionary Access Control Based on
Granting and Revoking Privileges
12
… The typical method of enforcing discretionary access control in a database system
is based on the granting and revoking privileges.
… Types of Discretionary Privileges
 The account level: At this level, the DBA specifies the particular privileges that each account holds
independently of the relations in the database.
 The relation (or table level): At this level, the DBA can control the privilege to access each individual
relation or view in the database.

… The privileges at the account level apply to the capabilities provided to the account
itself and can include the following:
 CREATE SCHEMA or CREATE TABLE or CREATE VIEW privilege;
 The ALTER privilege
 The DROP privilege;

 The MODIFY privilege

 The SELECT privilege,
Continued..
13

… Relation level:
… The granting and revoking of privileges generally follow an authorization
model for discretionary privileges known as the access matrix model,
 here the rows of a matrix M represents subjects (users, accounts, programs) and
 the columns represent objects (relations, records, columns, views, operations).
 Each position M(i, j) in the matrix represents the types of privileges (read, write,
update) that subject i holds on object j.
… To control the granting and revoking of relation privileges, each relation R
in a database is assigned and owner account (created first)
 The owner of a relation is given all privileges on that relation.
 The owner account holder can pass privileges on any of the owned relation to
other users by granting privileges to their accounts.
14

 In SQL the following types of privileges can be granted on each individual

relation R:
 SELECT (retrieval or read) privilege on R: Gives the account retrieval privilege.
In SQL this gives the account the privilege to use the SELECT statement to
retrieve tuples from R.
 MODIFY privileges on R: Gives the account the capability to modify tuples of R.
 In SQL this privilege is further divided into UPDATE, DELETE, and
INSERT
privileges to apply the corresponding SQL command to R.
 In addition, both the INSERT and UPDATE privileges can specify that only
certain attributes can be updated by the account.
15

 REFERENCES privilege on R: This gives the account the

capability to reference relation R when specifying integrity
constraints.
The privilege can also be restricted to specific attributes of R.
 Notice that to create a view, the account must have SELECT
privilege on all relations involved in the view definition.
Specifying Privileges Using Views
16

… The mechanism of views is an important discretionary

authorization mechanism in its own right.
… Example:

 if the owner A of a relation R wants another account B to

be able to retrieve only some fields of R, then A can create
a view V of R that includes only those attributes and then
grant SELECT on V to B. T
 the same applies to limiting B to retrieving only certain
tuples of R;
 a view V’ can be created by defining the view by means of
a query that selects only those tuples from R that A wants to
allow B to access.
Revoking Privileges
17

• … In some cases it is desirable to grant a privilege to

a user temporarily.
• …For example, the owner of a relation may want to

grant the SELECT privilege to a user for a specific

task and then revoke that privilege once the task is
completed.
• Hence, a mechanism for revoking privileges is needed.

… In SQL, a REVOKE command is included for the

purpose of canceling privileges.
Propagation of Privileges using the
18
GRANT OPTION
… Whenever the owner A of a relation R grants a privilege on
R to another account B, privilege can be given to B with or
without the GRANT OPTION.
… If the GRANT OPTION is given, this means that B can also
grant that privilege on R to other accounts.
… Suppose that B is given the GRANT OPTION by A and that
B then grants the privilege on R to a third account C, also
with GRANT OPTION.
… In this way, privileges on R can propagate to other accounts
without the knowledge of the owner of R.
… If the owner account A now revokes the privilege granted to
B, all the privileges that B propagated based on that
privilege should automatically be revoked by the system.
…
Example(1)
19
• … Suppose that the DBA creates four accounts A1, A2, A3, and A4 and wants only A1
to be able to create base relations; then the DBA must issue the following GRANT
command in SQL:
GRANT CREATETAB TO A1;
…
• …In SQL2 the same effect can be accomplished by having the DBA issue
a CREATE SCHEMA command as follows:
CREATE SCHAMA EXAMPLE AUTHORIZATION A1;
…

… User account A1 can create tables under the schema called EXAMPLE.
• … Suppose that A1 creates the two base relations EMPLOYEE and DEPARTMENT; A1 is then
owner of these two relations and hence all the relation privileges on each of them.
• …Suppose that A1 wants to grant A2 the privilege to insert and delete tuples in both of these
relations, but A1 does not want A2 to be able to propagate these privileges to additional
accounts:

… GRANT INSERT, DELETE ON EMPLOYEE, DEPARTMENT TO A2;

Example(2)
20

 Suppose that A1 wants to allow A3 to retrieve information from either of the two
tables and also to be able to propagate the SELECT privilege to other accounts.
 A1 can issue the command:

GRANT SELECT ON EMPLOYEE, DEPARTMENT

TO A3 WITH GRANT OPTION;

 A3 can grant the SELECT privilege on the EMPLOYEE relation to A4 by
issuing:

GRANT SELECT ON EMPLOYEE TO A4;

 Notice that A4 can’t propagate the SELECT privilege because GRANT OPTION was not given
to A4
Example(3)
21

 Suppose that A1 decides to revoke the SELECT privilege

on the EMPLOYEE relation from A3; A1 can issue:
REVOKE SELECT ON EMPLOYEE FROM A3;

 The DBMS must now automatically revoke the SELECT

privilege on EMPLOYEE from A4, too, because A3
granted that privilege to A4 and A3 does not have the
privilege any more.
Example(4)
22

 Suppose that A1 wants to give back to A3 a limited capability to SELECT from the
EMPLOYEE relation and wants to allow A3 to be able to propagate the privilege.
 The limitation is to retrieve only the NAME, BDATE, and ADDRESS attributes
and only for the tuples with DNO=5.
 A1 then create the view:
CREATE VIEW A3EMPLOYEE AS SELECT NAME, BDATE, ADDRESS FROM
EMPLOYEE WHERE DNO = 5;

 After the view is created, A1 can grant SELECT on the view A3EMPLOYEE to
A3 as follows:
 GRANT SELECT ON A3EMPLOYEE TO A3 WITH GRANT OPTION;
Example(5)
23

 Finally, suppose that A1 wants to allow A4 to update

only the SALARY attribute of EMPLOYEE;
 A1 can issue:

GRANT UPDATE ON EMPLOYEE (SALARY) TO

A4;
 The UPDATE or INSERT privilege can specify particular
attributes that may be updated or inserted in a relation.
 Other privileges (SELECT, DELETE) are not attribute specific.
Mandatory Access Control
24

 Based on system-wide policies that cannot be changed by individual users.

 Each DB object is assigned a security class.

− Bell-LaPadula Model
• Objects (e.g., tables, views, tuples)
• Subjects (e.g., users, user programs)
− Security classes:
− Top secret(TS), secret (S), confidential (C), unclassified (U): TS > S> C > U
• Each object and subject is assigned a class.
• Subject S can read object O only if class(S) >= class(O) (Simple Security
Property)
• ƒ Subject S can write object O only if class(S) <= class(O) (*-Property)
25

Question