Chapter 2 - Database Security and Authorization
Chapter 2 - Database Security and Authorization
1
Outline
1. Introduction to Database Security Issues
2
1. Introduction to Database Security Issues
4
1. Introduction to Database Security Issues
Database security is a broad area that addresses many issues, including the following:
Legal and ethical issues regarding the right to access certain information.
– Some information should be private and cannot be accessed legally by unauthorized
persons.
Some organizations needs to identify multiple security levels and categorize the
data and users based on these classifications. for example: Top secret, secret,
confidential, and unclassified.
The security policy of the organization with respect to permitting access to various
classifications of data must be enforced. 5
Components of Database Security
1. Authentication:- Verifies the identity of users (e.g., username and password,
biometrics, or multi-factor authentication).
3. Encryption:- Protects data by converting it into a coded form during storage (data-at-
rest) and transmission (data-in-transit).
5. Backup and Recovery:- Ensures data can be restored after corruption, accidental
deletion, or cyberattacks.
Loss of integrity
Only authorized users should be allowed to modify data.
Modification of data includes creation, insertion, modification, changing the status of data, and
deletion.
Integrity is lost if unauthorized changes are made to the data by either intentional or accidental acts.
Loss of availability-if DB is not available for those users/ to which they have a
legal right to uses the data
Authorized users should not be denied access.
Making objects available to a human user or a program to which they have a legitimate
right.
For example, an instructor who wishes to change a grade should be allowed to do so.
Loss of confidentiality
Information should not be disclosed to unauthorized users.
For example, a student should not be allowed to examine other students' grades.
To protect these types of threats against db, four kinds of countermeasures can be implemented
are: Access control, Flow control, and Encryption.
7
Threats to Database Security
Threats to Database Security are potential events, actions, or conditions
that can compromise the confidentiality, integrity, or availability of a
database. They can be intentional (malicious attacks) or unintentional
(errors, system failures).
Common Threats are;
Privilege abuse
Weak authentication
Insider threats
SQL Injection Malicious SQL code inserted to Attacker retrieves customer credit Data theft, unauthorized access,
manipulate database card info via website input data corruption
Privilege Abuse / Insider Threat Authorized users misuse their access Employee modifies confidential Loss of integrity, financial loss, legal
salary data without permission issues
Malware / Ransomware Software that corrupts or locks Ransomware encrypts database files Data unavailability, potential
database and demands payment financial loss
Weak Authentication / Passwords Easy-to-guess or default credentials Admin password “12345” is hacked Unauthorized access, data leakage
Unauthorized Access / Hacking External attackers exploit Hacker gains access to sensitive Confidentiality breach, data theft
vulnerabilities client data
Denial of Service (DoS) Overload database to make it Flood of queries crashes the Database downtime, operational
unavailable database server disruption
Data Theft / Leakage Sensitive data copied or shared Customer personal info sold illegally Confidentiality breach, reputational
without permission damage
Accidental Errors Mistakes by users or admins Admin accidentally deletes customer Data loss, service disruption
records
Hardware / Software Failures System crashes or disk failures Hard drive failure destroys Data unavailability, potential loss of
transaction records business
Unpatched Vulnerabilities Exploitation of known software Exploit targeting old SQL Server Unauthorized access, data
weaknesses vulnerability corruption
9
A DBMS includes a database security and authorization subsystem that is
responsible for ensuring the security of portions of a database against
unauthorized access.
access
12
2.1. 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 has a DBA account in the DBMS (called system or super user account)
* Account creation :- creates a new account and password for a user or a group of users to enable
access to the DBMS.
* Privilege granting:- the DBA grant certain privileges to certain accounts.
* Privilege revocation:- the DBA revoke (cancel) certain privileges that were previously given
to certain account.
* Security level assignment:- Assigning user accounts to the appropriate security classification
13
level
2.2. Access Protection, User Accounts, and Databases 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 if
he/she believes there is a legitimate need to access the database
– The user must log in to the DBMS by entering account id and password
whenever database access is needed.
– The database system must also keep track of all operations on the database
that are applied by a certain user throughout each login session.
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. 14
1.3. Discretionary Access Control Based on Granting and Revoking Privileges
The discretionary access control in a database system is based on the granting and revoking
privileges.
There are two types of Discretionary Privileges;
i. The account level:
At this level, the DBA specifies the particular privileges that each account holds independently
of the relations in the database.
The privileges at this level apply to the capabilities provided to the account itself and can
include the CREATE SCHEMA or CREATE TABLE , DROP TABLE, CREATE VIEW,
DROP VIEW, MODIFY(Inset, update and delete) and SELECT privilege.
These level of privileges that applies to the relation level, whether they are base relations or
virtual (view) relations.
These level of privileges specify for each user, the individual relations on which each type of
command can be applied.
SQL2 commands provide privileges at the relation and attribute level only.
15
Some privileges also refer to individual columns (attributes) of relations
The granting and revoking of privileges follow an authorization model for
discretionary privileges.
This is known as the access matrix model where;
The rows of a matrix M represent 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.
The 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. 16
In SQL, the following types of privileges can be granted on each individual relation R:
• In SQL this gives the account privilege to use the SELECT statement to retrieve tuples
from R.
MODIFY privileges on 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.
REFERENCES privilege on R: This gives the account the capability to reference relation R when
specifying integrity constraints. This 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.3.1. Specifying Privileges Using Views
19
2.3.3. 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
Example 1
Suppose that the DBA creates four accounts
– A1, A2, A3, A4 and wants only A1 to be able to create relations. Then the DBA must
issue the following GRANT command in SQL
GRANT CREATE TAB TO A1;
Example 2
• 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 it has 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:
Suppose that A1 wants to allow A3 to retrieve information from either of the table
(Department or Employee) and also to be able to propagate the SELECT privilege to other
accounts.
A1 can issue the command:
GRANT SELECT ON EMPLOYEE, DEPARTMENT
Example 4
Suppose that A1 decides to revoke the SELECT privilege on the EMPLOYEE relation from A3; A1 can
issue:
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:
WHERE DNO = 5;
After the view is created, A1 can grant SELECT on the view A3 EMPLOYEE to
A3 as follows:
23
Example 6
The limitation is
– They are susceptible to Trojan hours. i.e unauthorized users can trick
an authorized user into disclosing sensitive data.
24
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 traditionally been the main security mechanism for relational database
systems.
– Most commercial DBMSs currently provide mechanisms only for discretionary access
control.
– However, in government, military, and intelligence applications as well as in many
industrial and corporate applications, multilevel security is needed.
In many applications, additional security policy is needed to classifies data and users based
on security classes.
This approach, known as mandatory access control.
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.
25
The commonly used model for multilevel security, known as the Bell-
LaPadula model, which classifies each subject (user, account, program)
and object (relation, tuple, column, view, operation) into one of the
security classifications, T, S, C, or U.
– Clearance (classification) of a subject S as a class(S) and to the
classification of an object O as a 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).
– Star property (or *-property): A subject S is not allowed to write an
object O unless class(S) ≤ class(O).
26
To incorporate multilevel security notions into the relational database model,
it is common to consider attribute values and tuples as data objects.
Hence, each attribute A is associated with a classification attribute C in the
schema, and each attribute value in a tuple is associated with a corresponding
security classification.
In addition, in some models, a tuple classification attribute TC is added to
the relation attributes to provide a classification for each tuple as a whole.
Hence, a multilevel relation schema R with n attributes would be
represented as
– R(A1,C1,A2,C2, …, An,Cn,TC)
o where each Ci represents the classification attribute associated with attribute A i.
27
The value of the TC attribute in each tuple t – which is the highest of all attribute
classification values within t – provides a general classification for the tuple itself,
whereas each Ci provides a finer security classification for each attribute value
within the tuple.
– The apparent key of a multilevel relation is the set of attributes that would
have formed the primary key in a regular(single-level) relation.
A multilevel relation will appear to contain different data to subjects (users)
with different clearance levels.
o In some cases, it is possible to store a single tuple in the relation at a higher
classification level and produce the corresponding tuples at a lower-level
classification through a process known as filtering.
o In other cases, it is necessary to store two or more tuples at different
classification levels with the same value for the apparent key.
28
This leads to the concept of polyinstantiation where several tuples can have
the same apparent key value but have different attribute values for users
at different classification levels.
• Example
29
A user with a security clearance S would see the same relation shown above at (a)
since all row classification are less than or equal to S
However a user with security clearance C would not allowed to see values for
salary of Brown and jobperformance of Smith, since they have higher classification
as shown in (b)
For a user with security clearance U , filtering introduces null values for attributes
values whose security classification is higher than the user’s security clearance as
shown in (c)
A user with security clearance C may request for update on the values of
jobperformance of smith to ‘Excellent’ and the view will allow him to do
so .However the user shouldn't be allowed to overwrite the existing value at the
higher classification level.
Solution: to create ployinstation for smith row at the lower classification level C
as shown in (d)
30
Comparing DAC and MAC
Discretionary Access Control
DAC`s access privileges are granted based on pre-established rules
by administrators.
This model assigns ownership to resources, with administrators deciding
access and levels.
DAC’s security depends on responsible resource owners
Because of its simplicity and flexibility, DAC can pose a security risk to large
organizations, businesses handling sensitive data.
DAC is more straightforward, as resource owners can modify permissions
based on their understanding of their data’s value and security requirement.
DAC is simple to use, and as long as users and roles are listed correctly, it’s
easy to access resources.
Used for small business or organizations. 31
Comparing DAC and MAC
Mandatory Access Control
34
4. Access Control Policies for E-Commerce and the Web
– It is believed that the XML language can play a key role in access
control for e-commerce applications.
36
5. Introduction to Statistical Database Security
38
– Such queries are sometimes called statistical queries.
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.
39
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.
• Solution:
40
6. Introduction to Flow Control
• Flow control regulates the distribution or flow of information among accessible objects.
• A flow between object X and object Y occurs when a program reads values from X and writes
values into Y.
– Flow controls check that information contained in some objects does not flow explicitly
or implicitly into less protected objects.
• A flow policy specifies the channels along which information is allowed to move.
– The simplest flow policy specifies just two classes of information:
– A covert channel allows information to pass from a higher classification level to a lower
classification level through improper means.
41
Covert channels can be classified into two broad categories:
42
6. Encryption and Public Key Infrastructures
Encryption: The process of transforming plain text or data into cipher text that
cannot be read by anyone other than the sender and receiver.
This transformation of plain text to cipher text is accomplished by using a key
or cipher.
A key is any method for transforming plain text to cipher text.
43
6.1. The Data and Advanced Encryption Standards
• The DES algorithm derives its strength from repeated application of these
two techniques for a total of 16 cycles.
– Plaintext (the original form of the message) is encrypted as blocks of
64 bits.
• After questioning the adequacy of DES, the National Institute of Standards
(NIST) introduced the Advanced Encryption Standards (AES).
– This algorithm has a block size of 128 bits and thus takes longer time
to crack.
44
The Data and Advanced Encryption Standards
45
2. AES (Advanced Encryption Standard):- is a symmetric-key block cipher that
encrypts data in 128-bit blocks with key sizes of 128, 192, or 256 bits.
It uses multiple rounds of substitution, permutation,
permutation and mixing operations to
secure data.
AES is highly secure, efficient, and the current standard for protecting sensitive
information in modern systems such as secure communications, cloud storage,
and financial transactions.
RSA is widely used for secure data transmission, digital signatures, and key
exchange, but it is slower than symmetric algorithms and often used in
combination with them.
46
4. Digital Signatures:- is an encryption techniques that use public-key
cryptography to ensure authenticity, integrity, and non-repudiation of
digital messages.
A sender generates a hash of the message and encrypts it with their
private key.
The receiver decrypts the signature with the sender’s public key and
compares the hash to the message.
If they match, the message is verified as authentic and unaltered.
47
6.2 Public Key Encryption
– The use of two keys can have profound consequences in the areas of
confidentiality, key distribution, and authentication.
• 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, but it is referred to as private key rather
48
than a secret key.
A public key encryption scheme, or infrastructure, has six ingredients:
i. Plaintext: This is the data or readable message that is fed into the algorithm as input.
iii. 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.
• The exec transformations performed by the encryption algorithm depend on the
public or private key that is provided as input.
v. Decryption algorithm:
• This algorithm accepts the ciphertext and the matching key and produces the
original plaintext.
49
• 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.
• The essential steps are as follows:
i. Each user generates a pair of keys to be used for the encryption and decryption of
messages.
ii. Each user places one of the two keys in a public register or other accessible file. This is
the public key. The companion key is kept private (private key).
iii. If a sender wishes to send a private message to a receiver, the sender encrypts the
message using the receiver’s public key.
iv. When the receiver receives the message, he or she decrypts it using the receiver’s
private key.
• No other recipient can decrypt the message because only the receiver knows his or
her private key.
50
The RSA Public Key Encryption algorithm
– Private Key must be kept secret by the owner and used to decrypt messages
encrypted with the corresponding public key.
It`s advantage is
– Security: Even if the public key is known, it is extremely hard to deduce the private key.
– Confidentiality: Ensures only the intended recipient can read the message.
51
It`s Applications is;
– Secure email (PGP(Pretty Good Privacy), S/MIME(Secure/Multipurpose Internet
Mail Extensions))
– Secure socket Layer/Transport layer security(SSL/TLS) for secure web
communication
– Digital signatures
52