Database Security

What is database security?

• Data is a valuable resource that must be strictly controlled and managed, like any corporate resource.
• Part or all of the corporate data may have strategic importance and must be kept secure and confidential.
• Security is not only about the data in the database: breaches can affect other parts of the system (hardware, software, people), which may in turn affect the database.

What security aims to avoid

Database security aims to minimize losses from anticipated events in a cost-effective manner without unduly constraining users.

Threats

Threats and their effects

Scroll sideways on small screens. Connolly & Begg Table 19.1.

Threats summary — by source

A typical multi-user environment

Remote clients reach the database over an insecure network through encryption and a firewall. Inside, the DBMS server’s authorization and access control — SQL security / discretionary access control — decides what each user may do.

Authorization and authentication

The DBA defines authorization rules; every user request passes through the database security system, which checks it against those rules before touching the database.

Main issues in database security

1. Legal and ethical considerations — who has the right to read what information?
2. Policy issues — who should enforce security (government, corporations, departments)?
3. System-level issues — where should security be enforced in the system, and how?

Access control

Decides who should be allowed access to which databases. Typically enforced using system-level accounts with passwords.

Discretionary access control (DAC)

Authorization rules take three concepts into account:

Authorization table

User-based security

Users are individually defined in the DBMS and each object and privilege is specified per user. E.g. user SMITH:

Object-based security

Objects are individually defined and each subject and action is specified per object. E.g. the EMPLOYEES table:

Same information, two views: user-based = one matrix per user; object-based = one matrix per object.

GRANT

SQL provides two main statements for authorization. GRANT gives actions on objects to users:

GRANT action1, action2, ...
ON    object1, object2, ...
TO    subject1, subject2, ...
[WITH GRANT OPTION];

WITH GRANT OPTION allows the grantee to propagate the authorization to other subjects.

Example database — tables: employees, departments, orders, products; users: Smith, Jones, Green, Dba.

GRANT INSERT, DELETE, UPDATE, SELECT ON employees, departments TO Smith;
GRANT INSERT, SELECT ON orders TO Smith;
GRANT SELECT ON products TO Smith;

GRANT SELECT ON employees, departments TO Jones;

GRANT INSERT, DELETE, UPDATE, SELECT
ON    employees, departments, orders, products
TO    Dba
WITH GRANT OPTION;

-- Column-level grants:
GRANT SELECT ON products TO Jones;
GRANT UPDATE ON products (price) TO Jones;   -- Jones may change only the price column

If no GRANT is issued for a user, it is assumed that no authorization is given (e.g. user Green has no access at all).

REVOKE

REVOKE action1, action2, ...
ON     object1, object2, ...
FROM   subject1, subject2, ...;

If Smith leaves the company:

REVOKE INSERT, DELETE, UPDATE, SELECT ON employees, departments FROM Smith;
REVOKE INSERT, SELECT ON orders FROM Smith;
REVOKE SELECT ON products FROM Smith;

-- Many RDBMSs support ALL PRIVILEGES:
REVOKE ALL PRIVILEGES ON employees, departments, orders, products FROM Smith;

Quick review