Subject: Roles and Privileges
Last Revision Date: 11 September 1994
----------------------------------------------------------------------
ADMINISTERING ROLES AND PRIVILEGES IN ORACLE7
PRIVILEGES:
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 in Oracle7 to allow the DBA to have
more granular control over users' privileges. While v6 had 3 privileges
(Connect, Resource, DBA) that could be granted to a user, we now have over
80. In Oracle7 we have 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.
ROLES:
In order to effectively manage these privileges, Oracle7 provides 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 to roles. Anyone with
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 others 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 Role B
cannot in turn be granted to Role A.
Roles are not owned by anyone and do not belong to any schema. They can be
created by using a simple 'CREATE ROLE' statement once the user has
the 'CREATE ROLE' privilege. Roles must be uniquely named amongst all the
usernames and role names on the database. Since roles are not owned by their
creator, the user who created the role can be dropped without any effect
on the role.
ROLE ADVANTAGES:
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 users 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 in one command. NOTE: the use of roles has no effect on
explicitly granted privileges.
ROLE LIMITATIONS:
In order to effectively use roles, the DBA must also understand their
limitations. 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. a user creating a view on another user's table,
cannot receive the privilege to select from the table through a 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'.
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 reference 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 grant 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 granting this privilege. 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 reenabled. Additionally, system privileges and roles cannot
be granted with object privileges in the same GRANT statement. Finally,
object privileges may not be granted 'WITH GRANT OPTION' to a role since
the RDBMS does not allow object privileges to be propagated through roles.
The 'WITH ADMIN OPTION' is not valid when granting a role to another role.
Examples: Granting object privilege to a role:
GRANT SELECT ON accounts TO analyst;
Granting system privilege to a role:
GRANT CREATE SESSION TO analyst;
Granting role AND ability to grant role to a user:
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 a 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 creators 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.
NOTE: The 'WITH ADMIN OPTION' or 'WITH GRANT OPTION' can not be selectively
revoked. The entire privilege must be revoked then regranted without
these options.
SECURITY:
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). The DBA should be conservative with this number, since each
enabled role uses 4bytes of memory in the users' PGA.
There are two ways to enable a role:
o Roles can be automatically enabled at login time by specifying
them as the users DEFAULT ROLES. Each user may have none or more default
roles. These roles must be directly granted to the user. If ALL is
specified for the default roles, then any newly granted roles will
automatically be enabled for the next session.
o A user can also use the 'SET ROLE' command to enable a role.
Multiple roles can be enabled in one statement including the use of the 'ALL'
or 'ALL EXCEPT' options. Enabled roles not included in the latest
'SET ROLE' are automatically disabled when the user reissues the 'SET ROLE'
command. (Note: enabling the parent role, also enables the roles within).
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 a 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 externalised 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.
CONCLUSION:
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.