|Oracle9i Database Administrator's Guide
Release 1 (9.0.1)
Part Number A90117-01
This chapter explains how to use privileges and roles to control access to schema objects and to control the ability to execute system operations. The following topics are discussed:
This section describes Oracle user privileges, and contains the following topics:
A user privilege is a right to execute a particular type of SQL statement, or a right to access another user's object. Oracle also provides shortcuts for grouping privileges that are commonly granted or revoked together.
There are over 100 distinct system privileges. Each system privilege allows a user to perform a particular database operation or class of database operations.
For the complete list, including descriptions, of system privileges, see the Oracle9i SQL Reference.
Because system privileges are so powerful, Oracle recommends that you configure your database to prevent regular (non-DBA) users exercising
ANY system privileges (such as
UPDATE ANY TABLE) on the data dictionary. In order to secure the data dictionary, ensure that the
O7_DICTIONARY_ACCESSIBILITY initialization parameter is set to
FALSE. This feature is called the dictionary protection mechanism.
If you enable dictionary protection (
FALSE), access to objects in the
SYS schema (dictionary objects) is restricted to users with the
SYS schema. These users are
SYS and those who connect as
SYSDBA. System privileges providing access to objects in other schemas do not give other users access to objects in the
SYS schema. For example, the
SELECT ANY TABLE privilege allows users to access views and tables in other schemas, but does not enable them to select dictionary objects (base tables of dynamic performance views, views, packages, and synonyms). These users can, however, be granted explicit object privileges to access objects in the
Users with explicit object privileges or with administrative privileges (
SYSDBA) can access objects in the
SYS schema. Another means of allowing access to objects in the
SYS schema is by granting users any of the following roles:
This role can be granted to users to allow
SELECT privileges on all data dictionary views.
This role can be granted to users to allow
EXECUTE privileges for packages and procedures in the data dictionary.
This role can be granted to users to allow them to delete records from the system audit table (
Additionally, the following system privilege can be granted to users who require access to tables created in the
SELECT ANY DICTIONARY
This system privilege allows query access to any object in the
SYS schema, including tables created in that schema. It must be granted individually to each user requiring the privilege. It is not included in
GRANT ALL PRIVILEGES, nor can it be granted through a role.
Each type of object has different privileges associated with it. For a detailed list of objects and associated privileges, see the Oracle9i SQL Reference.
You can specify
PRIVILEGES] to grant or revoke all available object privileges for an object.
ALL is not a privilege; rather, it is a shortcut, or a way of granting or revoking all object privileges with one word in
REVOKE statements. Note that if all object privileges are granted using the
ALL shortcut, individual privileges can still be revoked.
Likewise, all individually granted privileges can be revoked by specifying
ALL. However, if you
REVOKE ALL, and revoking causes integrity constraints to be deleted (because they depend on a
REFERENCES privilege that you are revoking), you must include the
CASCADE CONSTRAINTS option in the
A role groups several privileges and roles, so that they can be granted to and revoked from users simultaneously. A role must be enabled for a user before it can be used by the user.
This section describes aspects of managing roles, and contains the following topics:
The roles listed in Table 25-1 are automatically defined for Oracle databases when you run the standard scripts that are part of database creation. You can grant privileges and roles to, and revoke privileges and roles from, these predefined roles, much the way you do with any role you define.
|Role Name||Created By (Script)||Description|
Includes the following system privileges:
Includes the following system privileges:
All system privileges
Note: The previous three roles are provided to maintain compatibility with previous versions of Oracle and may not be created automatically in future versions of Oracle. Oracle Corporation recommends that you design your own roles for database security, rather than relying on these roles.
Provides the privileges required to perform full and incremental database exports. Includes:
Provides the privileges required to perform full database imports. Includes an extensive list of system privileges (use view
Provides privileges for owner of the recovery catalog. Includes:
Used to protect access to the HS (Heterogeneous Services) data dictionary tables (grants
Obsoleted, but kept mainly for release 8.0 compatibility. Provides execute privilege on
Provides privileges to administer Advance Queuing. Includes
This role is used by Enterprise Manager/Intelligent Agent. Includes
If you install other options or products, other predefined roles may be created.
You can create a role using the
CREATE ROLE statement, but you must have the
CREATE ROLE system privilege to do so. Typically, only security administrators have this system privilege.
You must give each role you create a unique name among existing usernames and role names of the database. Roles are not contained in the schema of any user. In a database that uses a multibyte character set, Oracle recommends that each role name contain at least one single-byte character. If a role name contains only multibyte characters, the encrypted role name/password combination is considerably less secure.
The following statement creates the
clerk role, which is authorized by the database using the password
IDENTIFIED BY clause specifies how the user must be authorized before the role can be enabled for use by a specific user to which it has been granted. If this clause is not specified, or
NOT IDENTIFIED is specified, then no authorization is required when the role is enabled. Roles can be specified to be authorized by:
These authorizations are discussed in following sections.
Later, you can set or change the authorization method for a role using the
ALTER ROLE statement. The following statement alters the
clerk role to specify that the user must have been authorized by an external source before enabling the role:
To alter the authorization method for a role, you must have the
ALTER ANY ROLE system privilege or have been granted the role with the
Oracle9i SQL Reference for syntax, restrictions, and authorization information about the SQL statements used to manage roles and privileges
The methods of authorizing roles are presented in this section. A role must be enabled for you to use it.
"When Do Grants and Revokes Take Effect?" for a discussion about enabling roles
The use of a role authorized by the database can be protected by an associated password. If you are granted a role protected by a password, you can enable or disable the role by supplying the proper password for the role in a
SET ROLE statement. However, if the role is made a default role and enabled at connect time, the user is not required to enter a password.
The following statement creates a role
manager. When it is enabled, the password
morework must be supplied.
In a database that uses a multibyte character set, passwords for roles must include only singlebyte characters. Multibyte characters are not accepted in passwords. See the Oracle9i SQL Reference for information about specifying valid passwords.
package_name clause lets you create an application role, which is a role that can be enabled only by applications using an authorized package. Application developers do not need to secure a role by embedding passwords inside applications. Instead, they can create an application role and specify which PL/SQL package is authorized to enable the role.
The following example indicates that the role
admin_role is an application role and the role can only be enabled by any module defined inside the PL/SQL package
When enabling the user's default roles at login as specified in the user's profile, no checking is performed for application roles.
The following statement creates a role named
accts_rec and requires that the user be authorized by an external source before it can be enabled:
Role authentication through the operating system is useful only when the operating system is able to dynamically link operating system privileges with applications. When a user starts an application, the operating system grants an operating system privilege to the user. The granted operating system privilege corresponds to the role associated with the application. At this point, the application can enable the application role. When the application is terminated, the previously granted operating system privilege is revoked from the user's operating system account.
If a role is authorized by the operating system, you must configure information for each user at the operating system level. This operation is operating system dependent.
If roles are granted by the operating system, you do not need to have the operating system authorize them also; this is redundant.
"Granting Roles Using the Operating System or Network" for more information about roles granted by the operating system
If users connect to the database over Oracle Net, by default their roles cannot be authenticated by the operating system. This includes connections through a shared server configuration, as this connection requires Oracle Net. This restriction is the default because a remote user could impersonate another operating system user over a network connection.
If you are not concerned with this security risk and want to use operating system role authentication for network clients, set the initialization parameter
REMOTE_OS_ROLES in the database's initialization parameter file to
TRUE. The change will take effect the next time you start the instance and mount the database. The parameter is
FALSE by default.
A role can be defined as a global role, whereby a (global) user can only be authorized to use the role by an enterprise directory service. You define the global role locally in the database by granting privileges and roles to it, but you cannot grant the global role itself to any user or other role in the database. When a global user attempts to connect to the database, the enterprise directory is queried to obtain any global roles associated with the user.
The following statement creates a global role:
Global roles are one component of enterprise user management. A global role only applies to one database, but it can be granted to an enterprise role defined in the enterprise directory. An enterprise role is a directory structure which contains global roles on multiple databases, and which can be granted to enterprise users.
A general discussion of global authentication and authorization of users, and its role in enterprise user management, was presented earlier in "Global Authentication and Authorization".
In some cases, it may be appropriate to drop a role from the database. The security domains of all users and roles granted a dropped role are immediately changed to reflect the absence of the dropped role's privileges. All indirectly granted roles of the dropped role are also removed from affected security domains. Dropping a role automatically removes the role from all users' default role lists.
Because the creation of objects is not dependent on the privileges received through a role, tables and other objects are not dropped when a role is dropped.
You can drop a role using the SQL statement
DROP ROLE. To drop a role, you must have the
DROP ANY ROLE system privilege or have been granted the role with the
The following statement drops the role
This section describes aspects of granting privileges and roles, and contains the following topics:
It is also possible to grant roles to a user connected through a middle tier or proxy. This was discussed in "Proxy Authentication and Authorization".
You can grant system privileges and roles to other roles and users using the SQL statement
GRANT. To grant a system privilege or role, you must have the
ADMIN OPTION for all system privileges and roles being granted. Also, any user with the
GRANT ANY ROLE system privilege can grant any role in a database.
The following statement grants the system privilege
CREATE SESSION and the
accts_pay role to the user
When a user creates a role, the role is automatically granted to the creator with the
ADMIN OPTION. A grantee with the
ADMIN option has several expanded capabilities:
In the following statement, the security administrator grants the
new_dba role to
michael can not only use all of the privileges implicit in the
new_dba role, but can grant, revoke, or drop the
new_dba role as deemed necessary. Because of these powerful capabilities, exercise caution when granting system privileges or roles with the
ADMIN OPTION. Such privileges are usually reserved for a security administrator and rarely granted to other administrators or users of the system.
You also use the
GRANT statement to grant object privileges to roles and users. To grant an object privilege, you must fulfill one of the following conditions:
The following statement grants the
DELETE object privileges for all columns of the
emp table to the users
To grant the
INSERT object privilege for only the
job columns of the
emp table to the users
tsmith, issue the following statement:
To grant all object privileges on the
salary view to the user
jfee, use the
ALL keyword, as shown in the following example:
The user whose schema contains an object is automatically granted all associated object privileges with the
GRANT OPTION. This special privilege allows the grantee several expanded privileges:
GRANT OPTION, or to any role in the database.
GRANT OPTION is not valid when granting an object privilege to a role. Oracle prevents the propagation of object privileges through roles so that grantees of a role cannot propagate object privileges received by means of roles.
You can grant
REFERENCES privileges on individual columns in a table.
Before granting a column-specific
INSERT privilege on the
acct_no column of the
accounts table to
This section describes aspects of revoking user privileges and roles, and contains the following topics:
You can revoke system privileges and roles using the SQL statement
Any user with the
ADMIN OPTION for a system privilege or role can revoke the privilege or role from any other database user or role. The revoker does not have to be the user that originally granted the privilege or role. Also, users with
GRANT ANY ROLE can revoke any role.
The following statement revokes the
CREATE TABLE system privilege and the
accts_rec role from
REVOKE statement is used to revoke object privileges. To revoke an object privilege, the revoker must be the original grantor of the object privilege being revoked.
For example, assuming you are the original grantor, to revoke the
INSERT privileges on the
emp table from the users
tsmith, you would issue the following statement:
The following statement revokes all privileges (which were originally granted to the role
human_resource) from the table
This statement above would only revoke the privileges that the grantor authorized, not the grants made by other users. The
Although users can grant column-selective
REFERENCES privileges for tables and views, they cannot selectively revoke column specific privileges with a similar
REVOKE statement. Instead, the grantor must first revoke the object privilege for all columns of a table or view, and then selectively re-grant the column-specific privileges that should remain.
For example, assume that role
human_resources has been granted the
UPDATE privilege on the
dname columns of the table
dept. To revoke the
UPDATE privilege on just the
deptno column, issue the following two statements:
REVOKE statement revokes
UPDATE privilege on all columns of the
dept table from the role
GRANT statement re-grants
UPDATE privilege on the
dname column to the role
If the grantee of the
REFERENCES object privilege has used the privilege to create a foreign key constraint (that currently exists), the grantor can revoke the privilege only by specifying the
CASCADE CONSTRAINTS option in the
Any foreign key constraints currently defined that use the revoked
REFERENCES privilege are dropped when the
CASCADE CONSTRAINTS clause is specified.
Depending on the type of privilege, there may be cascading effects when 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
ADMIN OPTION. For example, assume the following:
CREATE TABLEsystem privilege to
jfeecreates a table.
CREATE TABLEsystem privilege to
tsmithcreates a table.
CREATE TABLEsystem privilege from
jfee's table continues to exist.
tsmithstill has the table and the
CREATE TABLEsystem privilege.
Cascading effects can 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 cascading effects that should be investigated before issuing a
testprocedure includes a SQL statement that queries data from the
emptable. If the
SELECTprivilege on the
emptable is revoked from the owner of the
testprocedure, the procedure can no longer be executed successfully.
REFERENCESprivilege for a table is revoked from a user, any foreign key integrity constraints defined by the user that require the dropped
REFERENCESprivilege are automatically dropped. For example, assume that the user
jwardis granted the
REFERENCESprivilege for the
deptnocolumn of the
depttable and creates a foreign key on the
deptnocolumn in the
emptable that references the
deptnocolumn. If the
referencesprivilege on the
deptnocolumn of the
depttable is revoked, the foreign key constraint on the
deptnocolumn of the
emptable is dropped in the same operation.
GRANT OPTIONare revoked if a grantor's object privilege is revoked. For example, assume that
user1is granted the
SELECTobject privilege with the
GRANT OPTION, and grants the
user2. Subsequently, the
SELECTprivilege is revoked from
REVOKEis cascaded to
user2as well. Any objects that depended on
SELECTprivilege can also be affected, as described in previous bullet items.
Object definitions that require the
INDEX DDL object privileges are not affected if the
INDEX object privilege is revoked. For example, if the
INDEX privilege is revoked from a user that created an index on someone else's table, the index continues to exist after the privilege is revoked.
Privileges and roles can also be granted to and revoked from the user group
PUBLIC is accessible to every database user, all privileges and roles granted to
PUBLIC are accessible to every database user.
Security administrators and database users should grant a privilege or role to
PUBLIC only if every database user requires the privilege or role. This recommendation reinforces the general rule that at any given time, each database user should only have the privileges required to accomplish the group's current tasks successfully.
Revoking a privilege from
PUBLIC can cause significant cascading effects. If any privilege related to a DML operation is revoked from
PUBLIC (for example,
SELECT ANY TABLE, UPDATE ON emp), all procedures in the database, including functions and packages, must be reauthorized before they can be used again. Therefore, exercise caution when granting and revoking DML-related privileges to
"Managing Object Dependencies" for more information about object dependencies
Depending on what is granted or revoked, a grant or revoke takes effect at different times:
PUBLIC) are immediately observed.
PUBLIC) are only observed when a current user session issues a
SET ROLEstatement to re-enable the role after the grant/revoke, or when a new user session is created after the grant/revoke.
You can see which roles are currently enabled by examining the
SESSION_ROLES data dictionary view.
During the session, the user or an application can use the
SET ROLE statement any number of times to change the roles currently enabled for the session. You must already have been granted the roles that you name in the
SET ROLE statement.The number of roles that can be concurrently enabled is limited by the initialization parameter
This example enables the role
clerk, which you have already been granted, and specifies the password.
You can disable all roles with the following statement:
When a user logs on, Oracle enables all privileges granted explicitly to the user and all privileges in the user's default roles.
A user's list of default roles can be set and altered using the
ALTER USER statement. The
ALTER USER statement allows you to specify roles that are to be enabled when a user connects to the database, without requiring the user to specify the roles' passwords. The user must have already been directly granted the roles with a
GRANT statement. You cannot specify as a default role any role managed by an external service including a directory service (external roles or global roles).
The following example establishes default roles for user
You cannot set a user's default roles in the
CREATE USER statement. When you first create a user, the user's default role setting is
ALL, which causes all roles subsequently granted to the user to be default roles. Use the
ALTER USER statement to limit the user's default roles.
When you create a role (other than a user role), it is granted to you implicitly and added as a default role. You receive an error at login if you have more than
A user can enable as many roles as specified by the initialization parameter
MAX_ENABLED_ROLES. All indirectly granted roles enabled as a result of enabling a primary role are included in this count. The database administrator can alter this limitation by modifying the value for this parameter. Higher values permit each user session to have more concurrently enabled roles. However, the larger the value for this parameter, the more memory space is required on behalf of each user session; this is because the PGA size is affected for each user session, and requires four bytes for each role. Determine the highest number of roles that will be concurrently enabled by any one user and use this value for the
This section describes aspects of granting roles through your operating system or network, and contains the following topics:
Instead of a security administrator explicitly granting and revoking database roles to and from users using
REVOKE statements, the operating system that operates Oracle can grant roles to users at connect time. Roles can be administered using the operating system and passed to Oracle when a user creates a session. As part of this mechanism, each user's default roles and the roles granted to a user with the
ADMIN OPTION can be identified. Even if the operating system is used to authorize users for roles, all roles must be created in the database and privileges assigned to the role with
Roles can also be granted through a network service.
The advantage of using the operating system to identify a user's database roles is that privilege management for an Oracle database can be externalized. The security facilities offered by the operating system control a user's privileges. This option may offer advantages of centralizing security for a number of system activities, such as the following situation:
The main disadvantage of using the operating system to identify a user's database roles is that privilege management can only be performed at the role level. Individual privileges cannot be granted using the operating system, but can still be granted inside the database using
A secondary disadvantage of using this feature is that by default users cannot connect to the database through the shared server, or any other network connection, if the operating system is managing roles. However, you can change this default; see "Using Network Connections with Operating System Role Management".
To operate a database so that it uses the operating system to identify each user's database roles when a session is created, set the initialization parameter
TRUE (and restart the instance, if it is currently running). When a user attempts to create a session with the database, Oracle initializes the user's security domain using the database roles identified by the operating system.
To identify database roles for a user, each Oracle user's operating system account must have operating system identifiers (these may be called groups, rights identifiers, or other similar names) that indicate which database roles are to be available for the user. Role specification can also indicate which roles are the default roles of a user and which roles are available with the
ADMIN OPTION. No matter which operating system is used, the role specification at the operating system level follows the format:
IDhas a definition that varies on different operating systems. For example, on VMS,
IDis the instance identifier of the database; on MVS, it is the machine type; on UNIX, it is the system
ROLEis the name of the database role.
dis an optional character that indicates this role is to be a default role of the database user.
ais an optional character that indicates this role is to be granted to the user with the
ADMIN OPTION. This allows the user to grant the role to other roles only. Roles cannot be granted to users if the operating system is used to manage roles.
For example, an operating system account might have the following roles identified in its profile:
When the corresponding user connects to the
payroll instance of Oracle,
role4 are defaults, while
role4 are available with the
When you use operating system managed roles, it is important to note that database roles are being granted to an operating system user. Any database user to which the OS user is able to connect will have the authorized database roles enabled. For this reason, you should consider defining all Oracle users as
IDENTIFIED EXTERNALLY if you are using
OS_ROLES = TRUE, so that the database accounts are tied to the OS account that was granted privileges.
OS_ROLES is set to
TRUE, the operating system completely manages the grants and revokes of roles to users. Any previous grants of roles to users using
GRANT statements do not apply; however, they are still listed in the data dictionary. Only the role grants made at the operating system level to users apply. Users can still grant privileges to roles and users.
OS_ROLES is set to
TRUE, any role granted by the operating system can be dynamically enabled using the
SET ROLE statement. This still applies, even if the role was defined to require a password or operating system authorization. However, any role not identified in a user's operating system account cannot be specified in a
SET ROLE statement, even if a role has been granted using a
GRANT statement when
OS_ROLES = FALSE. (If you specify such a role, Oracle ignores it.)
OS_ROLES = TRUE, a user can enable as many roles as specified by the initialization parameter
If you choose to have the operating system to manage roles, by default users cannot connect to the database through the shared server. This restriction is the default because a remote user could impersonate another operating system user over a non-secure connection.
If you are not concerned with this security risk and want to use operating system role management with the shared server, or any other network connection, set the initialization parameter
REMOTE_OS_ROLES in the database's initialization parameter file to
TRUE. The change will take effect the next time you start the instance and mount the database. The default setting of this parameter is
To access information about grants of privileges and roles, you can query the following data dictionary views:
DBA view describes all column object grants in the database. ALL view describes all column object grants for which the current user or PUBLIC is the object owner, grantor, or grantee. USER view describes column object grants for which the current user is the object owner, grantor, or grantee.
ALL view lists column object grants for which the current user is object owner or grantor. USER view describes column object grants for which the current user is the grantor.
ALL view describes column object grants for which the current user or PUBLIC is the grantee. USER view describes column object grants for which the current user is the grantee.
DBA view lists all grants on all objects in the database. ALL view lists the grants on objects where the user or PUBLIC is the grantee. USER view lists grants on all objects where the current user is the grantee.
ALL view lists the all object grants made by the current user or made on the objects owned by the current user. USER view lists grants on all objects owned by the current user.
ALL view lists object grants for which the user or PUBLIC is the grantee. USER view lists object grants for which the current user is the grantee.
This view lists all roles that exist in the database.
DBA view lists roles granted to users and roles. USER view lists roles granted to the current user.
DBA view lists system privileges granted to users and roles. USER view lists system privileges granted to the current user.
This view describes roles granted to other roles. Information is provided only about roles to which the user has access.
This view contains information about system privileges granted to roles. Information is provided only about roles to which the user has access.
This view contains information about object privileges granted to roles. Information is provided only about roles to which the user has access.
This view lists the privileges that are currently enabled for the user.
This view lists the roles that are currently enabled to the user.
Some examples of using these views follow. For these examples, assume the following statements have been issued:
CREATE ROLE security_admin IDENTIFIED BY honcho; GRANT CREATE PROFILE, ALTER PROFILE, DROP PROFILE, CREATE ROLE, DROP ANY ROLE, GRANT ANY ROLE, AUDIT ANY, AUDIT SYSTEM, CREATE USER, BECOME USER, ALTER USER, DROP USER TO security_admin WITH ADMIN OPTION; GRANT SELECT, DELETE ON SYS.AUD$ TO security_admin; GRANT security_admin, CREATE SESSION TO swilliams; GRANT security_admin TO system_administrator; GRANT CREATE SESSION TO jward; GRANT SELECT, DELETE ON emp TO jward; GRANT INSERT (ename, job) ON emp TO swilliams, jward;
Oracle9i Database Reference for a detailed description of these data dictionary views
The following query returns all system privilege grants made to roles and users:
SELECT * FROM DBA_SYS_PRIVS; GRANTEE PRIVILEGE ADM -------------- --------------------------------- --- SECURITY_ADMIN ALTER PROFILE YES SECURITY_ADMIN ALTER USER YES SECURITY_ADMIN AUDIT ANY YES SECURITY_ADMIN AUDIT SYSTEM YES SECURITY_ADMIN BECOME USER YES SECURITY_ADMIN CREATE PROFILE YES SECURITY_ADMIN CREATE ROLE YES SECURITY_ADMIN CREATE USER YES SECURITY_ADMIN DROP ANY ROLE YES SECURITY_ADMIN DROP PROFILE YES SECURITY_ADMIN DROP USER YES SECURITY_ADMIN GRANT ANY ROLE YES SWILLIAMS CREATE SESSION NO JWARD CREATE SESSION NO
The following query returns all the roles granted to users and other roles:
SELECT * FROM DBA_ROLE_PRIVS; GRANTEE GRANTED_ROLE ADM ------------------ ------------------------------------ --- SWILLIAMS SECURITY_ADMIN NO
The following query returns all object privileges (not including column-specific privileges) granted to the specified user:
SELECT TABLE_NAME, PRIVILEGE, GRANTABLE FROM DBA_TAB_PRIVS WHERE GRANTEE = 'JWARD'; TABLE_NAME PRIVILEGE GRANTABLE ----------- ------------ ---------- EMP SELECT NO EMP DELETE NO
To list all the column-specific privileges that have been granted, use the following query:
SELECT GRANTEE, TABLE_NAME, COLUMN_NAME, PRIVILEGE FROM DBA_COL_PRIVS; GRANTEE TABLE_NAME COLUMN_NAME PRIVILEGE ----------- ------------ ------------- -------------- SWILLIAMS EMP ENAME INSERT SWILLIAMS EMP JOB INSERT JWARD EMP NAME INSERT JWARD EMP JOB INSERT
The following query lists all roles currently enabled for the issuer:
swilliams has enabled the
security_admin role and issues this query, Oracle returns the following information:
The following query lists all system privileges currently available in the issuer's security domain, both from explicit privilege grants and from enabled roles:
swilliams has the
security_admin role enabled and issues this query, Oracle returns the following results:
PRIVILEGE ---------------------------------------- AUDIT SYSTEM CREATE SESSION CREATE USER BECOME USER ALTER USER DROP USER CREATE ROLE DROP ANY ROLE GRANT ANY ROLE AUDIT ANY CREATE PROFILE ALTER PROFILE DROP PROFILE
security_admin role is disabled for
swilliams, the first query would have returned no rows, while the second query would only return a row for the
CREATE SESSION privilege grant.
DBA_ROLES data dictionary view can be used to list all roles of a database and the authentication used for each role. For example, the following query lists all the roles in the database:
SELECT * FROM DBA_ROLES; ROLE PASSWORD ---------------- -------- CONNECT NO RESOURCE NO DBA NO SECURITY_ADMIN YES
ROLE_TAB_PRIVS data dictionary views contain information on the privilege domains of roles.
For example, the following query lists all the roles granted to the
SELECT GRANTED_ROLE, ADMIN_OPTION FROM ROLE_ROLE_PRIVS WHERE ROLE = 'SYSTEM_ADMIN'; GRANTED_ROLE ADM ---------------- ---- SECURITY_ADMIN NO
The following query lists all the system privileges granted to the
SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE = 'SECURITY_ADMIN'; ROLE PRIVILEGE ADM ----------------------- ----------------------------- --- SECURITY_ADMIN ALTER PROFILE YES SECURITY_ADMIN ALTER USER YES SECURITY_ADMIN AUDIT ANY YES SECURITY_ADMIN AUDIT SYSTEM YES SECURITY_ADMIN BECOME USER YES SECURITY_ADMIN CREATE PROFILE YES SECURITY_ADMIN CREATE ROLE YES SECURITY_ADMIN CREATE USER YES SECURITY_ADMIN DROP ANY ROLE YES SECURITY_ADMIN DROP PROFILE YES SECURITY_ADMIN DROP USER YES SECURITY_ADMIN GRANT ANY ROLE YES
The following query lists all the object privileges granted to the