Part 3: Controlling Data Access and Restricting Privileged Data in Oracle Database
By Troy Kitch on Feb 27, 2014
This is the third post on controlling data access and restricting privileged data in Oracle Database, pulled from the free ebook, Securing Oracle Database 12c: A Technical Primer. Here are the first and second posts. The book highlights new security features found in Oracle Database 12c; however, the majority of the solutions are applicable to earlier Oracle Database releases as well.
Users with Administrative Privileges
Certain users can connect with special administrative privileges, such as SYSDBA and SYSOPER, to allow maintenance operations even when the database is not open. These users can authenticate using a network-based authentication service such as Oracle Internet Directory or based on membership of the connecting user in a particular operating system group.
Proxy Authentication and Authorization
Sometimes administrators need to connect to an application schema to perform maintenance. Sharing the application schema password among several administrators would provide no accountability. Instead, proxy authentication allows the administrators to authenticate with their own credentials first and then proxy to the application schema. In such cases, the audit records show the actual user who performed the maintenance activities. This form of proxy authentication is supported in Oracle Call Interface (OCI), JDBC, and on the SQL*PLUS command line. Here is an example where the user app_dba is allowed to connect to the database and act as hrapp.
ALTER USER hrapp GRANT CONNECT THROUGH app_dba;
Now the user app_dba can connect using his own password and assume the identity of the hrapp user by proxy as follows:
Enter password: <app_dba_password>
Basic Access Control
Every object in the database, such as a table, view, or procedure, is contained within a schema. A schema is a user in the Oracle Database that owns objects. The schema user generally has full access to the objects contained within that schema. Access by other users is determined by object privileges, which allow a user to perform a particular operation on one specific object. Some typical operations for objects are SELECT, INSERT, UPDATE, DELETE, ALTER, and EXECUTE.
The schema user that owns an object has the ability to grant object privileges to other users. In addition, if an object privilege is granted with GRANT OPTION, the recipient of the grant also gains the ability to grant the same privilege to others. The ability to propagate grants in this way is powerful and should be used sparingly.
Here is an example of creating a user with just a few privileges: to create a session and connect to the database, to select from the DEPARTMENTS table, to execute the ADD_DEPARTMENT procedure, and full permissions to read and change data on the ADVENTURES table:
SQL> CREATE USER jsmith IDENTIFIED BY "Raider5!";
SQL> GRANT CREATE SESSION TO jsmith;
SQL> GRANT SELECT ON hr.departments TO jsmith;
SQL> GRANT EXECUTE ON hr.add_department TO jsmith;
SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON hr.adventures TO jsmith;
The dictionary table DBA_TAB_PRIVS shows the object privileges that have been granted. This gives detail about the object including the schema owner and which privileges were granted. This table can be used for reporting privileges and managing the level of permissions.
SQL> SELECT GRANTEE, OWNER, TABLE_NAME, PRIVILEGE FROM DBA_TAB_PRIVS WHERE GRANTEE='JSMITH';
When privileges are no longer needed on an object, they should be revoked.
System Privileges and Roles
Object privileges allow for very fine control over the data that a user can access, but sometimes an administrator may require access to many objects. System privileges allow access to all objects of a particular type; for example, SELECT ANY TABLE allows a user to select from any table in any schema, and EXECUTE ANY PROCEDURE allows execution of any PL/SQL procedure or function. Other system privileges apply to operations that do not involve a specific object, such as the ability to create objects, users, and roles; to change session and system parameters; and to export and import the database. As you can see, these are privileges for the administrator who can perform operations that have an impact across multiple schemas and objects.
Another convenient feature for managing privileges is the ability to group multiple object and system privileges into a role. Roles are especially useful when there is a need to grant a consistent set of privileges to several users. The roles are easier to manage than individual privileges and can be matched up with an application or a job function. Roles can be granted to other roles, allowing a large role like the DBA role for the database administrator to be built up out of smaller components. Like the GRANT option for object privileges, a system privilege or role can be granted with ADMIN OPTION, which allows the recipient to grant the role or privilege to others.
|DBA_TAB_PRIVS||Object privilege grants to roles or users|
|DBA_SYS_PRIVS||System privilege grants to roles or users|
|DBA_ROLE_PRIVS||Role grants to users or other roles|
|DBA_ROLES||All defined roles|
Least Privilege and Separation of Duty
The principle of least privilege denotes the idea that each user of the system should be granted only the minimum set of privileges needed to accomplish their intended tasks or functions. When granting privileges to a user or role, it is preferable to grant specific object privileges that are needed rather than broad system privileges that allow access to all objects in the database. Similarly, it is better to create roles that each contain a few privileges designed to be used for a particular function instead of very powerful roles like the built-in DBA role. Granting several of these smaller roles to a user allows for a close match to the tasks that the user needs to perform without granting extra privileges that are not required.
Closely related to the principle of least privilege is the concept of separation of duty. This is the notion that privileges should be divided among several users instead of a single powerful individual. Dividing administrative privileges in this way improves accountability and makes trusted administrators less likely to abuse their privileges.
To support the principles of least privilege and separation of duty, Oracle Database has long included a SYSOPER administrative privilege, which allows an administrator to perform certain tasks like starting and stopping the database without having the full range of powers conferred by the SYSDBA privilege. Oracle Database 12c adds additional administrative privileges called SYSBACKUP, SYSDG, and SYSKM, to enable database backups, Data Guard administration, and key management, respectively. With these targeted privileges, one or more administrators can perform all of the normal operations to manage a database without needing the all-powerful SYSDBA privilege.
Stay tuned for more. Or, you can read ahead by downloading the complimentary ebook here. Also, let me know if you are enjoying these posts by adding comments below.