Chapter Two

Database Security and Authorization

Tadele M.
1
1 Introduction to Database Security
 Legal and ethical issues regarding the right to access
certain information. Some information may need to be
kept private and cannot be accessed by unauthorized
people.
 Policy issues: At the governmental, institutional and
corporate level as to what kinds of information should not
be made publicly available, e.g. personal medical records.
 System-related issues: Such as the system level at which
various security functions should be enforced. For
example, Hardware, OS or DBMS level.

2
Introduction to Database Security Issues (cont…)
 The need to identify multiple security levels and to
categorize the data and users based on these
classifications: e.g. top secret, secret, confidential, and
unclassified.
 Threats to databases
 Loss of integrity

 Loss of availability

 Loss of confidentiality

 To protect databases against these types of threats, four

kinds of countermeasures can be implemented:
 Access control, Inference control, Flow control and

Encryption

3
Introduction (cont…)
 A DBMS typically includes a database security and
authorization subsystem that is responsible for ensuring the
security portions of a database against unauthorized access.
 Two types of database security mechanisms:
 Discretionary security mechanisms

 Mandatory security mechanisms

 The security mechanism of a DBMS must include provisions

for restricting access to the database as a whole
 This function is called access control and is handled by

creating user accounts and passwords to control login

process by the DBMS.

4
Introduction (cont…)
 The security problem associated with databases is that of
controlling the access to a statistical database, which is
used to provide statistical information or summaries of
values based on various criteria.
 Statistical database security problem should be handled
using inference control measures
 Another security is that of flow control, which prevents
information from flowing in such a way that it reaches
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.

5
Introduction (cont…)
 Another security issue is data encryption, which is used
to protect sensitive data (such as credit card numbers)
that is being transmitted via some type of communication
network
 Using encryption method, the data is encoded using
some encoding algorithm.
 Unauthorized user who access encoded data will have

difficulty deciphering it, but authorized users are given

decoding or decrypting algorithms (or keys) to decipher
data

6
1.2 Database Security and the DBA
 The database administrator (DBA) is the central authority
for managing a database system.
 The DBA’s responsibilities include

 Granting privileges to users who need to use the

system
 Classifying users and data in accordance with the

policy of the organization

 The DBA is responsible for the overall security of the
database system.

7
1.2 Database Security and the DBA (cont…)

 The DBA has a DBA account in the DBMS

 Sometimes these are called a system or superuser
account
 These accounts provide powerful capabilities such
as:
[Link] creation
[Link] granting
[Link] revocation
[Link] level assignment
 Action 1 is access control, whereas 2 and 3 are
discretionary and 4 is used to control mandatory
authorization

8
1.3 Access Protection, User Accounts, and
Database Audits

 Whenever a person or group of persons need to access a

database system, the individual or group must first apply
for a user account
 The DBA will then create a new account id and

password for the user.

 The user must log in to the DBMS by entering account id
and password whenever database access is needed

9
1.3 Access Protection, User Accounts, and
Database Audits (cont…)

 The database system must also keep track of all

operations on the database that are applied by a certain
user throughout each login session
 To keep a record of all updates applied to the database

and of the particular user who applied each update, we

can modify system log, which includes an entry for
each operation applied to the database that may be
required for recovery from a transaction failure or
system crash

10
1.3 Access Protection, User Accounts, and
Database Audits (cont…)

 If any tampering with the database is suspected, a

database audit is performed
 A database audit 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.

11
Discretionary Access Control

 This is the typical method of enforcing access control in a

database based on the granting and revoking privileges.
2.1 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 level (or table level):

 At this level, the DBA can control the privilege to access

each individual relation or view in the database.

12
Discretionary Privileges (cont…)
 The Privileges at the account level apply to the
capabilities provided to the account itself and can include
 the CREATE SCHEMA or CREATE TABLE privilege,
to create a schema or base relation;
 the CREATE VIEW privilege;

 the ALTER privilege, to apply schema changes such

as adding or removing attributes from relations;
 the DROP privilege, to delete relations or views;

 the MODIFY privilege, to insert, delete, or update

tuples;
 and the SELECT privilege, to retrieve information from
the database.

13
Types of Discretionary Privileges (cont…)
 The second level of privileges applies to the relation level
 This includes privileges on base relations and virtual (view)
relations.
 The granting and revoking of privileges generally follow an
authorization model for discretionary privileges known as the access
matrix model where
 The rows of a matrix M represents subjects (users, accounts,
programs)
 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.

14
Types of Discretionary Privileges (cont..)
 To control the granting and revoking of relation privileges,
each relation R in a database is assigned an owner
account, which is typically the account that was used
when the relation was created in the first place.
 The owner of a relation is given all privileges on that
relation.
 DBA can assign an owner to a whole schema by
creating the schema and associating the appropriate
authorization identifier with that schema, using the
CREATE SCHEMA command.
 The owner account holder can pass privileges on any
of the owned relation to other users by granting
privileges to their accounts.

15
Types of Discretionary Privileges (cont…)
 In SQL, the following types of privileges can be granted on each
individual relation R:
 SELECT (retrieval or read) privilege on R:

 Gives a retrieval privilege to the account

 This gives the account holder the privilege to use the SELECT

statement to retrieve tuples from R.

 MODIFY privileges on R:

 This gives the account the capability to modify tuples of R.

 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

16
Types of Discretionary Privileges (cont…)
 The following types of privileges can be granted on each
individual relation R :
 REFERENCES privilege on R:

 This gives the account holder the right 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

17
2.2 Specifying Privileges Using Views

 The mechanism of views is an important discretionary

authorization mechanism. For 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

18
2.3 Revoking Privileges
 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.

19
2.4 Propagation of Privileges using the 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.

20
2.5 An Example
 Suppose that the DBA creates four accounts
 A1, A2, A3, A4

 and wants only A1 to be able to create base relations. Then, the DBA
must issue the following GRANT command in SQL
GRANT CREATE TABLE TO A1;
 The same effect can be accomplished by having the DBA issue a
CREATE SCHEMA command as follows:
CREATE SCHAMA schema1 AUTHORIZATION A1;
 User account A1 can create tables under the schema called
schema1.

21
An Example (cont…)
 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;

22
2.5 An Example (cont…)
 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

23
2.5 An Example (cont…)
 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 so that A3 does not have the
privilege any more.

24
2.5 Example (cont…)
 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;

25
An Example (cont…)
 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;

26
3 Mandatory Access Control and Role-Based Access
Control for Multilevel Security

 The discretionary access control techniques of granting

and revoking privileges on relations has been the main
security mechanism for relational database systems.
 This is an all-or-nothing method:
 A user either has or does not have a certain privilege.

 In many applications, additional security policy is

needed that classifies data and users based on security
classes.
 This approach as mandatory access control, would
typically be combined with the discretionary access
control mechanisms.

27
3 Mandatory Access Control and Role-Based Access
Control for Multilevel Security (cont…)
 Typical security classes are top secret (TS), secret (S),
confidential (C), and unclassified (U), where TS is the
highest level and U the lowest: TS ≥ S ≥ C ≥ U
 The commonly used model for multilevel security, classifies
each subject (user, account, program) and object (relation,
tuple, column, view, operation) into one of the security
classifications, TS, S, C, or U:
 Clearance (classification) of a subject S as class(S) and

to the classification of an object O as class(O).

 Two restrictions are enforced on data access based on the
subject/object classifications:
 Simple security property: A subject S is not allowed

read access to an object O unless class(S) ≥ class(O)

 *-property (star property): A subject S is not allowed

to write an object O unless class(S) <= class(O).

28
Mandatory Access Control and Role-Based Access
Control for Multilevel Security (cont…)

 E.g. a user with TS clearance may make a copy of an

object with classification TS and then write it back as a
new object with classification U, thus making it visible
throughout the system

29
3.1 Comparing Discretionary Access Control
and Mandatory Access Control

 Discretionary Access Control (DAC) policies are

characterized by a high degree of flexibility, which makes
them suitable for a large variety of application domains.
 The main drawback of DAC models is their

vulnerability to malicious attacks, such as Trojan

horses embedded in application programs

30
3.1 Comparing Discretionary Access Control
and Mandatory Access Control (cont…)

 By contrast, mandatory policies ensure a high degree of

protection in a way, they prevent any illegal flow of
information.
 Mandatory policies have the drawback of being too rigid
and they are only applicable in limited environments.
 In many practical situations, discretionary policies are
preferred because they offer a better trade-off between
security and applicability.

31
3.2 Role-Based Access Control
 Role-based access control (RBAC) has emerged
rapidly in the 1990s as a proven technology for managing
and enforcing security in large-scale enterprise wide
systems.
 Its basic notion is that permissions are associated with
roles, and users are assigned to appropriate roles.
 Roles can be created using the CREATE ROLE
commands.
 The GRANT and REVOKE commands discussed

under DAC can then be used to assign and revoke

privileges from roles

32
3.2 Role-Based Access Control (cont…)
 RBAC appears to be a viable alternative to discretionary
and mandatory access controls; it ensures that only
authorized users are given access to certain data or
resources.
 Many DBMSs have allowed the concept of roles, where
privileges can be assigned to roles.
 Role hierarchy in RBAC is a natural way of organizing
roles to reflect the organization’s lines of authority and
responsibility.

33
3.2 Role-Based Access Control (cont…)
 Using RBAC model is highly desirable goal for addressing
the key security requirements of Web-based applications.
 In contrast, discretionary access control (DAC) and
mandatory access control (MAC) models lack
capabilities needed to support the security requirements
emerging enterprises and Web-based applications.

34
4 Introduction to Statistical
Database Security

 Statistical databases are used mainly to produce

statistics on various populations.
 The database may contain confidential data on
individuals, which should be protected from user access.
 Users are permitted to retrieve statistical information on
the populations, such as averages, sums, counts,
maximums, minimums, and standard deviations.
 A population is a set of tuples of a relation (table) that
satisfy some selection condition.
 Statistical queries involve applying statistical functions
to a population of tuples.

35
Statistical database Security (cont…)
 For example, we may want to retrieve the number of
individuals in a population or the average income in the
population.
 However, statistical users are not allowed to retrieve
individual data, such as the income of a specific
person.
 Statistical database security techniques must prohibit the
retrieval of individual data.
 This can be achieved by prohibiting queries that retrieve
attribute values and by allowing only queries that involve
statistical aggregate functions such as COUNT, SUM,
MIN, MAX, AVERAGE, and STANDARD DEVIATION.
 Such queries are sometimes called statistical
queries.

36
Statistical database Security (cont…)
 It is DBMS’s responsibility to ensure confidentiality of
information about individuals, while still providing useful
statistical summaries of data about those individuals to
users.
 Provision of privacy protection of users in a statistical
database is paramount.
 In some cases it is possible to infer the values of
individual tuples from a sequence statistical queries.
 This is particularly true when the conditions result in a

population consisting of a small number of tuples.

37
5. Encryption and Public Key Infrastructures

 Encryption is a means of maintaining secure data in an

insecure environment.
 Encryption consists of applying an encryption
algorithm to data using some pre specified encryption
key.
 The resulting data has to be decrypted using a
decryption key to recover the original data.

38
Public Key Encryption
 The two keys used for public key encryption are referred
to as the public key and the private key.
 the private key is kept secret.

 A public key encryption scheme or infrastructure, has six

ingredients:
 Plaintext: This is the data or readable message that is
fed into the algorithm as input.
 Encryption algorithm: The encryption algorithm
performs various transformations on the plaintext.
 Public and private keys: These are pair of keys that
have been selected so that if one is used for
encryption, the other is used for decryption.

39
Public Key Encryption (cont…)
 A public key encryption scheme, or infrastructure, has six
ingredients (cont…):
 Ciphertext:

 This is the scrambled message produced as

output. It depends on the plaintext and the key.

 For a given message, two different keys will

produce two different ciphertexts.

 Decryption algorithm:

 This algorithm accepts the ciphertext and the

matching key and produces the original plaintext.

40
Public Key Encryption (cont…)
 Public key is made for public and private key is known
only by owner.
 A general-purpose public key cryptographic algorithm
relies on
 one key for encryption and

 a different but related key for decryption.

41