Oracle Database Access Control Model

In school, we learn that relational databases use RBAC (Role Based Access Control) model whereas operating systems use DAC (Discretionary Access Control) model. But is it really true about relational databases? Well, it is partially true. Modern databases utilize a combination of RBAC and DAC models. To complicate further, they may provide MAC (Mandatory Access Control) features as well. For example, in Oracle database, OLS (Oracle Label Security) and DV (Database Vault) options are providing MAC features. In this blog post, I am going to describe the access control model used by core Oracle database (optional components will be discussed in a future blog. - why we really need them and what purpose they serve, etc.)

Before we get into the access control model, let's quickly go over some basics.

Privileges and Roles:
- A privilege is a right to run a particular type of SQL statement on the database. For example, a privilege to connect to the database, a privilege to read a table, a privilege to execute a procedure, etc.
- A role is a group privileges and possibly consisting of other roles. When a user is granted a role, the user can exercise all the privileges associated with the role. There are some differences between a directly granted privilege vs the same privilege granted via a role. We are leaving this discussion to a future post. Usually roles are created to manage privileges for a user group or an application.

Let's look at privileges in some more details. There are two types of privileges
1. System privileges
They are quite powerful and granted with due diligence. A system privilege is a right to perform a particular action (e.g. CREATE SESSION system privilege) or an action of any schema object of a particular type (e.g. SELECT ANY TABLE system privilege). Note that there are some exceptions. For example. SELECT ANY TABLE does not gives the rights to access dictionary tables.

2. Schema object privileges (or simply object privileges)
They are much less powerful than system privileges. An object privilege is a right to perform a particular action on a particular schema object (e.g. emp table of scoot). From the point of view of security, it is better to grant object privileges over system privileges as users are granted only the privileges that they need for the job. However, from the management point of view, it could be a nightmare to manage if there thousands of object privileges. In practice, most organizations prefer to use system privileges over object privileges due to ease of management.

With the above background knowledge, let us now go through the RBAC + DAC model that Oracle database employs.

The DAC model gives the owner of an object the ability to grant or revoke access to other subjects (users or roles). For example, user bob has a table foo. Bob can grant select and insert access on table foo to another user alice and grant only select on foo to a role bar. Later, Bob can revoke access on table foo from either alice or the role bar. 

Notice that not every one can grant access to others on a given object. If a user has the privilege to exercise the right on an object (e.g. SELECT on bob.foo table), it does not mean that he/she can grant that privilege to others. Let's take the above example again. Unless, Alice is granted select/insert on bob.foo with GRANT OPTION, Alice cannot grant these privileges on bob.foo to others. 


The RBAC model gives a user access to an object based on its assigned roles in the database system. Usually roles are defined based on job functions. For example, Tom is an employee in the HR department and, hence, assigned the role hr_emp. The system defines that the role hr_emp has access to HR tables in the database. Therefore, Tom can access all HR tables. However, Tim, who is not an employee in the HR department and not assigned the role hr_emp, cannot access HR tables. Similar to GRANT OPTION for privileges, a user can grant a role granted to him/her only if the user is granted that role with ADMIN OPTION. This capability gives the DAC enforcement over RBAC.