This post seeks to give a general overview of the roles and privileges functionality for the Oracle RDBMS. It delineates the advantages and limitations of administering privileges through roles. The effects of granting and revoking roles are analyzed and important authorization and security issues are discussed.
The ability to selectively grant the right to manipulate and access information through privileges is integral to assure the security of the database. Privileges have been greatly enhanced historically in Oracle7 to allow the DBA to have more granular control over a users privileges. While v6 had 3 privileges (Connect, Resource, DBA) that could be granted to a user, we now have many more.
In Oracle7 we introduced two distinct types of privileges: system and object. System privileges give a user the right to perform a particular action or to perform a particular action on a particular type of object. This includes creating, dropping and altering tables, views, rollback segments, and procedures. Since system privileges are powerful privileges they are normally granted only to administrative roles. Object privileges allow access and manipulation of a specific object.
For example, selecting, deleting, inserting, and referencing particular tables, views, sequences, and procedures. These are normally granted to end-users so that they can perform specific operations. The distinction between the two types of privileges is very important because different rules and restrictions apply to each. Additionally, many schema objects like clusters, indexes, triggers and database links are controlled using system privileges.
In order to effectively manage these privileges, Oracle7 provided the use of objects called roles. Privileges can be grouped together and granted to the role which is then granted to users or other roles. Both system and object privileges can be granted a role. Anyone with the privilege to grant these privileges can grant them to a role. Privileges will cascade from the parent role to the child role. Roles can thus be used in combination to further control access granted to end users.
For example, the ACCOUNTING_DEPT and FINANCE_DEPT roles can be granted to the MANAGER role which is then granted to the Finance and Accounting Managers to allow them access to each other’s applications. Employees of the Accounting and Finance departments only have access to their own applications through the ACCOUNTING_DEPT and FINANCE_DEPT roles.
Although roles can be granted to other roles, they cannot be granted in a circular fashion. For example, Role A can be granted to Role B, but then Role B cannot, in turn, be granted to Role A.
Roles are not owned by anyone and do not belong to any schema. Rather in a fashion, they are more like schema users themselves since they are naturally administered in data dictionary table USER$ (but visible in DBA_ROLES, not DBA_USERS). Therefore roles must be uniquely named amongst all the usernames and role names on the database. They can be created by using a simple ‘CREATE ROLE’ statement if the user has the ‘CREATE ROLE’ privilege. Since roles are not owned by their creator, the user who created the role can be dropped without any effect on the role.
There are several important advantages to using roles for privilege management. By combining related privileges into a single role, the DBA can streamline the task of granting multiple privileges to multiple users. The DBA can grant or revoke several privileges to a role, then grant that role to multiple users. Thus, the use of roles can dramatically reduce the need to individually administer multiple privileges per user.
Additionally, roles alleviate the need for the DBA to remember the individual privileges required for a new user. If roles are used to reflect a certain job (for example, ACCOUNTING_DEPT role), the DBA does not need to remember what individual grants are needed for the new employee of the Accounting Department. He will simply grant the ACCOUNTING_DEPT role to the new user.
Roles also allow the DBA to dynamically change the user’s privileges as the responsibilities change. For example, if the employees of the Accounting Department now need access to additional tables, the DBA can simply grant the appropriate privileges to the ACCOUNTING_DEPT role. Such a change will affect all users that have been granted this role in one command.
In order to effectively use roles, the DBA must also understand their limitations.
1. In general, a user cannot acquire a DML privilege needed to perform a DDL operation via role. The user must be explicitly granted the necessary object privilege. For example, if user U1 creates a package that references table T in schema U2, the privilege to select table U2.T must be granted directly and not via a role.
2. A user creating a view on another user’s table, cannot receive the privilege to select from the table through a role. It is conceptually important to understand that the privileges assigned to a role can be associated with a user session only and these privileges cannot be inherited by any objects (views, stored procedures) that are owned by a user who happens to have been granted the role. In addition, if the user wants to grant others access to his view then the view creator must have been granted the object privilege on the underlying tables ‘WITH GRANT OPTION‘.
For example, UserA has a table, UserB wants to create a view based on this table. UserB must be explicitly granted select on the table to create a view. If, however, UserB wants to grant his view to UserC, UserA must also grant him select ‘WITH GRANT OPTION’.
3. Similarly, when creating procedures, the user who is creating a procedure must have access to the underlying objects referenced in the body of the procedure. Although these privileges cannot be granted via a role, the right to execute the procedure can. Therefore, a user who is executing a procedure requires only EXECUTE privilege on the procedure and does not require any access to the referenced objects. This reduces the number of privileges that need to be granted to users and also enhances database security. The same applies to a user wanting to reference another’s table. The referenced privilege must be explicitly granted to the user.
Granting Roles and Privileges
In order for roles and privileges to be effective, they must be granted to the users. Any user with ‘GRANT ANY ROLE‘ privilege can grant roles. Any system privilege or role can be granted ‘WITH ADMIN OPTION’ to allow the grantee to in turn grants the privilege or role. The grantee ‘WITH ADMIN OPTION’ can grant or revoke the privilege or role to or from any user or role in the database. The grantee can also alter or drop the role. Since ‘WITH ADMIN OPTION’ is so powerful, extreme caution needs to be taken when using it.
The user who creates a role is automatically granted ‘WITH ADMIN OPTION‘ on that role. Object privileges can be granted by the creator or one who is granted ‘WITH GRANT OPTION’ on the object. The user granted an object privilege also grant ‘WITH GRANT OPTION’.
Grants to a user take effect immediately whereas grants to roles require the role to be re-enabled. Additionally, system privileges and roles cannot be granted with object privileges in the same GRANT statement.
The ‘WITH GRANT OPTION’ is not valid when an object privilege is granted to a role. Furthermore the ‘WITH ADMIN OPTION’ is not valid when granting a role to another role. This means that you can include these option while granting the privilege or the role but the users who will be granted the (grantee) role will not become the ‘administrator’ of the object privilege/role.
1. Granting object privilege to a role:
SQL> GRANT SELECT ON accounts TO analyst;
2. Granting system privilege to a role:
SQL> GRANT CREATE SESSION TO analyst;
3. Granting role AND ability to grant role to a user:
SQL> GRANT analyst TO stiger WITH ADMIN OPTION;
Revoking Roles and Privileges
A user with the right to grant has that same right to revoke. Revoke restriction on privilege is dependent on the type of privilege. For example, any user with the ‘WITH ADMIN OPTION’ on a system privilege can revoke system privileges. However, only the grantor of an object privilege can revoke it. Object execution can be affected, if the creator’s privilege is revoked, but not when the revoke is made to a role. Revoking privilege from a role never causes views or procedures to fail because execution uses only privileges directly granted to the owner of the view or the procedure.
Depending on the type of privilege, there may or may not be cascading effects if a privilege is revoked. There are no cascading effects when revoking a system privilege related to DDL operations regardless of whether the privilege was granted with or without the ‘WITH ADMIN OPTION’.
For example, if userA has granted the ‘CREATE TABLE’ privilege to userB who then creates a table, a revoke of userA’s privilege, doesn’t drop any of his objects, and userB can still create tables. However, cascading effects will be observed when revoking a system privilege related to a DML operation. If SELECT ANY TABLE is revoked from a user, then all procedures contained in the users schema relying on this privilege will fail until the privilege is reauthorized.
Revoking an object privilege can have several cascading effects that must be investigated before issuing the REVOKE. Object execution can be affected if the creator’s privilege is revoked.
For example, a procedure that requires the creator to have SELECT on a table,will fail if this access is then revoked. Similarly, views created on that table will now fail. When revoking the REFERENCES privilege, the revoke statement must include the ‘CASCADE CONSTRAINTS‘ option, so that constraints the user no longer has privileges on are dropped.
For example, userA has reference privilege to userB’s table. He in turn creates a constraint on his table based on this privilege. The revoker must use ‘CASCADE CONSTRAINTS’ which will then drop userA’s constraint. Also, the users privilege received through the use of ‘WITH GRANT OPTION’ are revoked if the grantors object privilege is revoked. Assume that userA has been granted SELECT on a table ‘WITH GRANT OPTION’ and then grants SELECT on that table to user B. If userA’s privilege is revoked, then UserB can no longer select from the table.
A good rule of thumb is that DML revokes will cascade, whereas DDL revokes do not. So, if user B had created a view based on the table, userB’s view no longer is valid when userA loses the select privilege. Finally, if a privilege related to a DML operation is revoked from PUBLIC, all procedures in the database relying on this privilege (including functions and packages) must be reauthorized before they can be used again.
A major goal of the DBA is to grant each user only the privileges necessary to perform his/her job. At certain times, the user may need a privilege to perform a task, that at other times isn’t necessary. Privileges granted directly to a user are always available. However, even though a role is granted to a user, the privileges associated with that role are disabled until the user enables the role. The DBA can control how many roles can be enabled at one time by setting the limit through the init.ora parameter MAX_ENABLED_ROLES. (Note: Roles received via role are included in this number).
For additional security, when a role is created, it can require that a password be specified when the role is enabled. This feature allows the DBA to ensure that certain work is done only within the scope of an application.
For example, a DBA may not want a user to be able to insert into a table from within SQL*Plus where little control over the data exists. Therefore, he can enable a role when the application is first executed, to allow the user to manipulate data within the restrictions of the application (i.e, triggers, etc). Then upon exiting of the application, his role is disabled, leaving him with no privileges. The user can not enable the role within SQL*Plus, because he does not know the password. Furthermore, if desired, the operating system account can be used to manage the authorization of roles. The advantage lies in that all privilege management can be externalized so that the security features of the operating system can be used to validate the users. If a DBA relies on this extra security, he must be aware of a few restrictions. Specifying a role as a default role bypasses any authentication by password or operating system checks. If a user wants to use the ‘ALL’ or ‘ALL EXCEPT’ when enabling roles, these roles cannot require authorization.
We have seen how roles and privileges allow the DBA to administer security on the database. The basic functions of roles and their use in privilege management are outlined along with some advantages and restrictions. In order to administer security, the dba must know all the functionality and restrictions of privileges and roles, so that he can avoid any unexplained privilege problems that may result.