managing user privileges and roles

8/16/2019 Managing User Privileges and Roles

1/42

Managing User Privileges and RolesThis chapter explains how to use privileges and roles to control access to schema objects and to control the ability to executesystem operations. The following topics are discussed:

• Understanding User Privileges and Roles

• Managing User Roles

• ranting User Privileges and Roles

• Revo!ing User Privileges and Roles

• ranting to and Revo!ing from the User roup PU"#$%

• &hen 'o rants and Revo!es Ta!e (ffect)

• ranting Roles Using the *perating +ystem or ,etwor!

• -iewing Privilege and Role $nformation

See Also:

o %hapter /0 1Managing Users and Resources1 for information about controlling access to a

database

o %hapter 20 1(stablishing +ecurity Policies1 for suggested general database security policies

http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#14881http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#14979http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#15311http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#15404http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#15505http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#15521http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#15555http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#15665http://docs.oracle.com/cd/B10501_01/server.920/a96521/users.htm#14699http://docs.oracle.com/cd/B10501_01/server.920/a96521/secure.htm#684http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#14979http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#15311http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#15404http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#15505http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#15521http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#15555http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#15665http://docs.oracle.com/cd/B10501_01/server.920/a96521/users.htm#14699http://docs.oracle.com/cd/B10501_01/server.920/a96521/secure.htm#684http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#14881


2/42

Understanding User Privileges and Roles

3 user privilege is a right to execute a particular type of +4# statement0 or a right to access another user5s object. The typesof privileges are defined by *racle.

Roles0 on the other hand0 are created by users 6usually administrators7 and are used to group together privileges or otherroles. They are a means of facilitating the granting of multiple privileges or roles to users.

This section describes *racle user privileges0 and contains the following topics:

• +ystem Privileges

• *bject Privileges

• User Roles

See Also:

Oracle9i Database Concepts for additional information about privileges and roles

System Privileges

There are over 899 distinct system privileges. (ach system privilege allows a user to perform a particular database operation

or class of database operations.

Caution:

+ystem privileges can be very powerful0 and should be granted only when necessary to roles and trusted users of

http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#14895http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#21327http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#21065http://docs.oracle.com/cd/B10501_01/server.920/a96524/c24privs.htm#CNCPT024http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#14895http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#21327http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#21065http://docs.oracle.com/cd/B10501_01/server.920/a96524/c24privs.htm#CNCPT024


3/42

the database.

See Also:

Oracle9i SQL Reference. for the complete list of system privileges and their descriptions

Restricting System Privileges

"ecause system privileges are so powerful0 *racle recommends that you configure your database to prevent regular 6non

'"37 users exercising ANY system privileges 6such as UPDATE ANY TABLE7 on the data dictionary. $n order to secure the datadictionary0 ensure that the O7_DICTIONARY_ACCESSIBILITY initiali;ation parameter is set to FALSE. This feature is called the

dictionary protection mechanism.

Note:

The O7_DICTIONARY_ACCESSIBILITY initiali;ation parameter controls restrictions on system privileges when

you upgrade from *racle< to *racle=i and higher releases. $f the parameter is set to TRUE0 access to objects in

the SYS schema is allowed 6*racle< behavior7. $f this parameter is set to FALSE0 system privileges that allow

access to objects in 1any schema1 do not allow access to objects in SYS schema. The default

for O7_DICTIONARY_ACCESSIBILITY is FALSE.

&hen this parameter is not set to FALSE0 the ANY privilege applies to the data dictionary0 and a malicious user

with ANY privilege could access or alter data dictionary tables.

+ee the Oracle9i Database Reference for more information onthe O7_DICTIONARY_ACCESSIBILITY initiali;ation parameter and Oracle9i Database Migration to understand

its usage.

http://docs.oracle.com/cd/B10501_01/server.920/a96540/statements_912a.htm#SQLRF01603http://docs.oracle.com/cd/B10501_01/server.920/a96536/ch1128.htm#REFRN10133http://docs.oracle.com/cd/B10501_01/server.920/a96536/ch1128.htm#REFRN10133http://docs.oracle.com/cd/B10501_01/server.920/a96530/migcompa.htm#MIGNG009http://docs.oracle.com/cd/B10501_01/server.920/a96540/statements_912a.htm#SQLRF01603http://docs.oracle.com/cd/B10501_01/server.920/a96536/ch1128.htm#REFRN10133http://docs.oracle.com/cd/B10501_01/server.920/a96530/migcompa.htm#MIGNG009


4/42


5/42

This system privilege allows ?uery access to any object in the SYS schema0 including tables created in that schema. $t

must be granted individually to each user re?uiring the privilege. $t is not included in GRANT ALL PRIVILEGES0 nor can it

be granted through a role.

Caution:

@ou should grant these roles and the SELECT ANY DICTIONARY system privilege with extreme care0 since the

integrity of your system can be compromised by their misuse.

Object Privileges

(ach type of object has different privileges associated with it.

@ou can specify ALL APRIVILEGESB to grant or revo!e all available object privileges for an object. ALL is not a privilegeC rather0

it is a shortcut0 or a way of granting or revo!ing all object privileges with one word in GRANT and REVOKE statements. ,otethat if all object privileges are granted using the ALL shortcut0 individual privileges can still be revo!ed.

#i!ewise0 all individually granted privileges can be revo!ed by specifying ALL. Dowever0 if you REVOKE ALL0 and revo!ing

causes integrity constraints to be deleted 6because they depend on a REFERENCES privilege that you are revo!ing70 you mustinclude the CASCADE CONSTRAINTS option in the REVOKE statement.

See Also:

Oracle9i SQL Reference. for the complete list of object privileges

User Roles

http://docs.oracle.com/cd/B10501_01/server.920/a96540/statements_912a.htm#SQLRF01603http://docs.oracle.com/cd/B10501_01/server.920/a96540/statements_912a.htm#SQLRF01603


6/42

3 role groups several privileges and roles0 so that they can be granted to and revo!ed from users simultaneously. 3 role must be enabled for a user before it can be used by the user.

*racle provides some predefined roles to help in database administration. These roles0 listed in Table E80 are automatically

defined for *racle databases when you run the standard scripts that are part of database creation. @ou can grant privilegesand roles to0 and revo!e privileges and roles from0 these predefined roles in the same way as you do with any role youdefine.

Table 25-1 Predefined Roles (Page 1 of 2)

Role Name

Created y

!Scri"t# $escri"tion

CONNECT SQL.BSQ $ncludes the following system privileges: ALTER SESSION0 CREATE CLUSTER0 CREATE

DATABASE LINK0 CREATE SEQUENCE0 CREATE SESSION0 CREATE SYNONYM0 CREATE

TABLE0CREATE VIEW

RESOURCE SQL.BSQ $ncludes the following system privileges: CREATE CLUSTER0 CREATE INDEXTYPE0 CREATE

OPERATOR0 CREATE PROCEDURE0 CREATE SEQUENCE0 CREATE TABLE0 CREATE

TRIGGER0CREATE TYPE

DBA SQL.BSQ 3ll system privileges WITH ADMIN OPTION

Note: The previous three roles are provided to maintain compatibility with previous versions of *racle and may not be created

automatically in future versions of *racle. *racle %orporation recommends that you design your own roles for database security0 rather

http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#21164http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#21164


7/42

Role NameCreated y!Scri"t# $escri"tion

than relying on these roles.

EXP_FULL_DATABASE CATEXP.SQL Provides the privileges re?uired to perform full and incremental database exports.

$ncludes: SELECT ANY TABLE0 BACKUP ANY TABLE0 EXECUTE ANY PROCEDURE0 EXECUTE ANY

TYPE0 ADMINISTER RESOURCE MANAGER0 and INSERT0 DELETE0 and UPDATE on the

tables SYS.INCVID0 SYS.INCFIL0 and SYS.INCEXP. 3lso the following

roles:EXECUTE_CATALOG_ROLE and SELECT_CATALOG_ROLE.

IMP_FULL_DATABASE CATEXP.SQL Provides the privileges re?uired to perform full database imports. $ncludes an extensive list of

system privileges 6use view DBA_SYS_PRIVS to view privileges7 and the following

roles: EXECUTE_CATALOG_ROLE and SELECT_CATALOG_ROLE.

DELETE_CATALOG_ROLE SQL.BSQ Provides DELETE privilege on the system audit table 6AUD$7

EXECUTE_CATALOG_ROLE SQL.BSQ Provides EXECUTE privilege on objects in the data dictionary. 3lso0 HS_ADMIN_ROLE.

SELECT_CATALOG_ROLE SQL.BSQ Provides SELECT privilege on objects in the data dictionary. 3lso0 HS_ADMIN_ROLE.

RECOVERY_CATALOG_OWNE CATALOG.SQL Provides privileges for owner of the recovery catalog. $ncludes: CREATE SESSION0 ALTER


8/42

Role NameCreated y!Scri"t# $escri"tion

R SESSION0 CREATE SYNONYM0 CREATE VIEW0 CREATE DATABASE LINK0 CREATE TABLE0 CREATE

CLUSTER0 CREATE SEQUENCE0 CREATE TRIGGER0 and CREATE PROCEDURE

HS_ADMIN_ROLE CATHS.SQL Used to protect access to the D+ 6Deterogeneous +ervices7 data dictionary tables

6grants SELECT7 and pac!ages 6grants EXECUTE7. $t is granted

toSELECT_CATALOG_ROLE and EXECUTE_CATALOG_ROLE such that users with generic data

dictionary access also can access the D+ data dictionary.

AQ_USER_ROLE CATQUEUE.SQL *bsoleted0 but !ept mainly for release =.9 compatibility. Provides execute privilege

on DBMS_AQ and DBMS_AQIN.

AQ_ADMINISTRATOR_ROLE CATQUEUE.SQL Provides privileges to administer 3dvance 4ueuing. $ncludes ENQUEUE ANY QUEUE0 DEQUEUE

ANY QUEUE0 and MANAGE ANY QUEUE0 SELECT privileges on 34 tables andEXECUTE privileges

on 34 pac!ages.

SNMPAGENT CATSNMP.SQL This role is used by (nterprise ManagerF$ntelligent 3gent. $ncludes ANALYZE ANY and

grants SELECT on various views.

$f you install other options or products0 other predefined roles may be created.


9/42

Managing User Roles

This section describes aspects of managing roles0 and contains the following topics:

• %reating a Role

• +pecifying the Type of Role 3uthori;ation

• 'ropping Roles

Creating a Role

@ou can create a role using the CREATE ROLE statement0 but you must have the CREATE ROLE system privilege to do so.

Typically0 only security administrators have this system privilege.

Note:

$mmediately after creation0 a role has no privileges associated with it. To associate privileges with a new role0

you must grant privileges or other roles to the new role.

@ou must give each role you create a uni?ue name among existing usernames and role names of the database. Roles are notcontained in the schema of any user. $n a database that uses a multibyte character set0 *racle recommends that each role

name contain at least one singlebyte character. $f a role name contains only multibyte characters0 the encrypted rolenameFpassword combination is considerably less secure.

The following statement creates the !"# role0 which is authori;ed by the database using the password %&"'("''&)!:

http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#15134http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#15180http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#15292http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#15134http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#15180http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#15292


10/42

CREATE ROLE !"# IDENTIFIED BY %&"'("''&)!*

The IDENTIFIED BY clause specifies how the user must be authori;ed before the role can be enabled for use by a specific user

to which it has been granted. $f this clause is not specified0 or NOT IDENTIFIED is specified0 then no authori;ation is re?uired

when the role is enabled. Roles can be specified to be authori;ed by:

• The database using a password

• 3n application using a specified pac!age

•

(xternally by the operating system0 networ!0 or other external source

• lobally by an enterprise directory service

These authori;ations are discussed in following sections.

#ater0 you can set or change the authori;ation method for a role using the ALTER ROLE statement. The following statement

alters the !"# role to specify that the user must have been authori;ed by an external source before enabling the role:

ALTER ROLE !"# IDENTIFIED EXTERNALLY*

To alter the authori;ation method for a role0 you must have the ALTER ANY ROLE system privilege or have been granted the

role with the ADMIN OPTION.

See Also:

Oracle9i SQL Reference for syntax0 restrictions0 and authori;ation information about the +4# statements usedto manage roles and privileges

http://docs.oracle.com/cd/B10501_01/server.920/a96540/toc.htmhttp://docs.oracle.com/cd/B10501_01/server.920/a96540/toc.htm


11/42

S"eci%ying the &y"e o% Role Authori'ation

The methods of authori;ing roles are presented in this section. 3 role must be enabled for you to use it.

See Also:

1&hen 'o rants and Revo!es Ta!e (ffect)1 for a discussion about enabling roles

Role Authori'ation by the $atabase

The use of a role authori;ed by the database can be protected by an associated password. $f you are granted a role protected by a password0 you can enable or disable the role by supplying the proper password for the role in a SET ROLE statement.

Dowever0 if the role is made a default role and enabled at connect time0 the user is not re?uired to enter a password.

The following statement creates a role +)'),"#. &hen it is enabled0 the password +o#"-o# must be supplied.

CREATE ROLE +)'),"# IDENTIFIED BY +o#"-o#*

Note:

$n a database that uses a multibyte character set0 passwords for roles must include only singlebyte characters.

Multibyte characters are not accepted in passwords. +ee the Oracle9i SQL Reference for information aboutspecifying valid passwords.

Role Authori'ation by an A""lication

The INDENTIFIED USING package_name clause lets you create an application role0 which is a role that can be enabled only by

applications using an authori;ed pac!age. 3pplication developers do not need to secure a role by embedding passwords

http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#15521http://docs.oracle.com/cd/B10501_01/server.920/a96540/toc.htmhttp://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#15521http://docs.oracle.com/cd/B10501_01/server.920/a96540/toc.htm


12/42

inside applications. $nstead0 they can create an application role and specify which P#F+4# pac!age is authori;ed to enablethe role.

The following example indicates that the role )+&'_#o!" is an application role and the role can only be enabled by any

module defined inside the P#F+4# pac!age /#.)+&'.

CREATE ROLE )+&'_#o!" IDENTIFIED USING /#.)+&'*

&hen enabling the user5s default roles at login as specified in the user5s profile0 no chec!ing is performed for applicationroles.

Role Authori'ation by an ()ternal Source

The following statement creates a role named )(0_#" and re?uires that the user be authori;ed by an external source before

it can be enabled:

CREATE ROLE )(0_#" IDENTIFIED EXTERNALLY*

Role Authori'ation by the O"erating System

Role authentication through the operating system is useful only when the operating system is able to dynamically lin!operating system privileges with applications. &hen a user starts an application0 the operating system grants an operatingsystem privilege to the user. The granted operating system privilege corresponds to the role associated with the application.3t this point0 the application can enable the application role. &hen the application is terminated0 the previously granted

operating system privilege is revo!ed from the user5s operating system account.

$f a role is authori;ed by the operating system0 you must configure information for each user at the operating system level.This operation is operating system dependent.


13/42

$f roles are granted by the operating system0 you do not need to have the operating system authori;e them alsoC this isredundant.

See Also:

1ranting Roles Using the *perating +ystem or ,etwor!1 for more information about roles granted by theoperating system

Role Authori'ation and Net*or+ Clients

$f users connect to the database over *racle ,et0 by default their roles cannot be authenticated by the operating system. This

includes connections through a shared server configuration0 as this connection re?uires *racle ,et. This restriction is thedefault because a remote user could impersonate another operating system user over a networ! connection.

$f you are not concerned with this security ris! and want to use operating system role authentication for networ! clients0 setthe initiali;ation parameter REMOTE_OS_ROLES in the database5s initiali;ation parameter file to TRUE. The change will ta!e effect

the next time you start the instance and mount the database. The parameter is FALSE by default.

Role Authori'ation by an (nter"rise $irectory Service

3 role can be defined as a global role0 whereby a 6global7 user can only be authori;ed to use the role by an enterprisedirectory service. @ou define the global role locally in the database by granting privileges and roles to it0 but you cannotgrant the global role itself to any user or other role in the database. &hen a global user attempts to connect to the database0the enterprise directory is ?ueried to obtain any global roles associated with the user.

The following statement creates a global role:

CREATE ROLE 012"#3&0o# IDENTIFIED GLOBALLY*



14/42

lobal roles are one component of enterprise user management. 3 global role only applies to one database0 but it can begranted to an enterprise role defined in the enterprise directory. 3n enterprise role is a directory structure which contains

global roles on multiple databases0 and which can be granted to enterprise users.

3 general discussion of global authentication and authori;ation of users0 and its role in enterprise user management0 was presented earlier in 1lobal 3uthentication and 3uthori;ation1.

See Also:

Oracle Advanced Security Administrators !uide and Oracle "nternet Directory Administrators !uide for

information about enterprise user management and how to implement it

$ro""ing Roles

$n some cases0 it may be appropriate to drop a role from the database. The security domains of all users and roles granted adropped role are immediately changed to reflect the absence of the dropped role5s privileges. 3ll indirectly granted roles ofthe dropped role are also removed from affected security domains. 'ropping a role automatically removes the role from all

users5 default role lists.

"ecause the creation of objects is not dependent on the privileges received through a role0 tables and other objects are not

dropped when a role is dropped.

@ou can drop a role using the +4# statement DROP ROLE. To drop a role0 you must have the DROP ANY ROLE system privilege or

have been granted the role with the ADMIN OPTION.

The following statement drops the role CLERK:

DROP ROLE !"#*

http://docs.oracle.com/cd/B10501_01/server.920/a96521/users.htm#17355http://docs.oracle.com/cd/B10501_01/network.920/a96573/toc.htmhttp://docs.oracle.com/cd/B10501_01/network.920/a96574/toc.htmhttp://docs.oracle.com/cd/B10501_01/server.920/a96521/users.htm#17355http://docs.oracle.com/cd/B10501_01/network.920/a96573/toc.htmhttp://docs.oracle.com/cd/B10501_01/network.920/a96574/toc.htm


15/42

,ranting User Privileges and Roles

This section describes the granting of privileges and roles0 and contains the following topics:

• ranting +ystem Privileges and Roles

• ranting *bject Privileges

• ranting Privileges on %olumns

$t is also possible to grant roles to a user connected through a middle tier or proxy. This is discussed in 1Proxy 3uthentication

and 3uthori;ation1.

,ranting System Privileges and Roles

@ou can grant system privileges and roles to other users and roles using the GRANT statement. The following privileges are

re?uired:

• To grant a system privilege0 you must have been granted the system privilege with the ADMIN OPTION or have been

granted the GRANT ANY PRIVILEGE system privilege.

• To grant a role0 you must have been granted the role with the ADMIN OPTION or have been granted the GRANT ANY

ROLE system privilege.

Note:

@ou cannot grant a roll that is IDENTIFIED GLOBALLY to anything. The granting 6and revo!ing7 of global roles

is controlled entirely by the enterprise directory service.

http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#15326http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#20924http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#21776http://docs.oracle.com/cd/B10501_01/server.920/a96521/users.htm#17433http://docs.oracle.com/cd/B10501_01/server.920/a96521/users.htm#17433http://docs.oracle.com/cd/B10501_01/server.920/a96521/users.htm#17433http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#15326http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#20924http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#21776http://docs.oracle.com/cd/B10501_01/server.920/a96521/users.htm#17433http://docs.oracle.com/cd/B10501_01/server.920/a96521/users.htm#17433


16/42

The following statement grants the system privilege CREATE SESSION and the )(0_2)4 role to the user 5-)#:

GRANT CREATE SESSION6 )(0_2)4 TO 5-)#*

Note:

*bject privileges cannot be granted along with system privileges and roles in the same GRANT statement.

,ranting the A$M-N OP&-ON

3 user or role that is granted a privilege or role specifying the WITH ADMIN OPTION clause has several expanded capabilities:

• The grantee can grant or revo!e the system privilege or role to or from any user or other role in the database. Users

cannot revo!e a role from themselves.

• The grantee can further grant the system privilege or role with the ADMIN OPTION.

• The grantee of a role can alter or drop the role.

$n the following statement0 the security administrator grants the '"-_%) role to +&/)"!:

GRANT '"-_%) TO +&/)"! WITH ADMIN OPTION*

The user +&/)"! cannot only use all of the privileges implicit in the '"-_%) role0 but can grant0 revo!e0 or drop

the '"-_%) role as deemed necessary. "ecause of these powerful capabilities0 exercise caution when granting system

privileges or roles with the ADMIN OPTION. +uch privileges are usually reserved for a security administrator and rarely granted

to other administrators or users of the system.


17/42

&hen a user creates a role0 the role is automatically granted to the creator with the ADMIN OPTION

Creating a Ne* User *ith the ,RAN& Statement

*racle allows you to create a new user with the GRANT statement. $f you specify a password using the IDENTIFIED BY clause0and the usernameFpassword does not exist in the database0 a new user with that username and password is created. The

following example creates 00+&(/ as a new user while granting 00+&(/ the CONNECT system privilege:

GRANT CONNECT TO 00+&(/ IDENTIFIED BY 289#:*

See Also:

1%reating Users1

,ranting Object Privileges

@ou also use the GRANT statement to grant object privileges to roles and users. To grant an object privilege0 you must fulfillone of the following conditions:

• @ou own the object specified.

• @ou possess the GRANT ANY OB;ECT PRIVILEGE system privilege that enables you to grant and revo!e privileges on behalf of the object owner.

• The WITH GRANT OPTION clause was specified when you were granted the object privilege by its owner.

Note:

+ystem privileges and roles cannot be granted along with object privileges in the same GRANT statement.

http://docs.oracle.com/cd/B10501_01/server.920/a96521/users.htm#15245http://docs.oracle.com/cd/B10501_01/server.920/a96521/users.htm#15245


18/42

The following statement grants the SELECT0 INSERT0 and DELETE object privileges for all columns of the "+2 table to theusers 5


19/42

,ranting Object Privileges on ehal% o% the Object O*ner

The GRANT ANY OB;ECT PRIVILEGE system privilege allows users to grant and revo!e any object privilege on behalf of theobject owner. This provides a convenient means for database and application administrators to grant access to objects in anyschema without re?uiring that they connect to the schema. This eliminates the need to maintain login credentials for schemaowners so that they can grant access to objects0 and it reduces the number of connections re?uired during configuration.

This system privilege is part of the *racle supplied DBA role and is thus granted 6with the ADMIN OPTION7 to any user

connecting AS SYSDBA 6user SYS7. 3s with other system privileges0 the GRANT ANY OB;ECT PRIVILEGE system privilege can only

be granted by a user who possesses the ADMIN OPTION.

&hen you exercise the GRANT ANY OB;ECT PRIVILEGE system privilege to grant an object privilege to a user0 if you already possess the object privilege with the GRANT OPTION0 then the grant is performed in the usual way. $n this case0 you become the

grantor of the grant. $f you do not possess the object privilege0 then the object owner is shown as the grantor0 even though

you0 with the R3,T 3,@ *"G(%TPR$-$#(( system privilege0 actually performed the grant.

Note:

The audit record generated by the R3,T statement will always show the real user who performed the grant.

>or example0 consider the following. User ))+0 possesses the GRANT ANY OB;ECT PRIVILEGE system privilege. De does not possess any other grant privileges. De issues the following statement:

GRANT SELECT ON /#."+2!o4""0 TO %!)" WITH GRANT OPTION*

$f you examine the DBA_TAB_PRIVS view0 you will see that /# is shown as being the grantor of the privilege:


20/42

SQL= SELECT GRANTEE6 OWNER6 GRANTOR6 PRIVILEGE6 GRANTABLE 9= FROM DBA_TAB_PRIVS:= WHERE TABLE_NAME > ?EMPLOYEES? )' OWNER > ?HR?*

GRANTEE OWNER GRANTOR PRIVILEGE GRANTABLE@@@@@@@@ @@@@@ @@@@@@@ @@@@@@@@@@@ @@@@@@@@@@BLAKE HR HR SELECT YES

,ow assume that %!)" also has the GRANT ANY OB;ECT PRIVILEGE system. De0 issues the following statement:

GRANT SELECT ON /#."+2!o4""0 TO !)#*

$n this case0 when you again ?uery the DBA_TAB_PRIVS view0 you see that %!)" is shown as being the grantor of the privilege:

GRANTEE OWNER GRANTOR PRIVILEGE GRANTABLE@@@@@@@@ @@@@@ @@@@@@@@ @@@@@@@@ @@@@@@@@@@BLAKE HR HR SELECT YESCLARK HR BLAKE SELECT NO

This is because %!)" already possesses the SELECT privilege on /#."+2!o4""0 with the GRANT OPTION.

See Also:

1Revo!ing *bject Privileges on "ehalf of the *bject *wner1

,ranting Privileges on Columns

@ou can grant INSERT0 UPDATE0 or REFERENCES privileges on individual columns in a table.

Caution:



21/42

"efore granting a columnspecific INSERT privilege0 determine if the table contains any columns on which NOT

NULL constraints are defined. ranting selective insert capability without including the NOT NULL columns prevents the user from inserting any rows into the table. To avoid this situation0 ma!e sure that each NOT

NULLcolumn is either insertable or has a nonNULL default value. *therwise0 the grantee will not be able to insert

rows into the table and will receive an error.

The following statement grants INSERT privilege on the )(_'o column of the )o1'(0 table to 0o((:

GRANT INSERT )(_'o ON )o1'(0 TO 0o((*

$n another example0 object privilege for the "')+" and 5o% columns of the "+2 table are granter to the users 5


22/42

3ny user with the ADMIN OPTION for a system privilege or role can revo!e the privilege or role from any other database user

or role. The revo!er does not have to be the user that originally granted the privilege or role. Users with GRANT ANY ROLE can

revo!e any role.

The following statement revo!es the CREATE TABLE system privilege and the )(0_#" role from (0+&(/:

REVOKE CREATE TABLE6 )(0_#" FROM (0+&(/*

Note:

The ADMIN OPTION for a system privilege or role cannot be selectively revo!ed. The privilege or role must be

revo!ed and then the privilege or role regranted without the ADMIN OPTION.

Revo+ing Object Privileges

The REVOKE statement is used to revo!e object privileges. To revo!e an object privilege0 you must fulfill one of the following

conditions:

• @ou previously granted the object privilege to the user or role.

• @ou possess the GRANT ANY OB;ECT PRIVILEGE system privilege that enables you to grant and revo!e privileges on

behalf of the object owner.

@ou can only revo!e the privileges that you0 the grantor0 directly authori;ed0 not the grants made by other users to whom you

granted the R3,T *PT$*,. Dowever0 there is a cascading effect. The object privilege grants propagated using the GRANT

OPTION are revo!ed if a grantor5s object privilege is revo!ed.

3ssuming you are the original grantor0 the following statement revo!es the SELECT and INSERT privileges on the "+2 table

from the users5


23/42

REVOKE SELECT6 &'0"#( ON "+2 FROM 5


24/42

BLAKE HR HR SELECT YESCLARK HR BLAKE SELECT NOCLARK HR HR SELECT NO

User %!)" now issues the following REVOKE statement:

REVOKE SELECT ON /#."+2!o4""0 FROM !)#*

*nly the object privilege for !)# granted by %!)" is removed. The grant by the object owner0 /#0 remains.

GRANTEE OWNER GRANTOR PRIVILEGE GRANTABLE@@@@@@@@ @@@@@ @@@@@@@ @@@@@@@@@@@ @@@@@@@@@@BLAKE HR HR SELECT YESCLARK HR HR SELECT NO

$f %!)" issues the REVOKE statement again0 this time the effect will be to remove the object privilege granted by /#.

See Also:

1ranting *bject Privileges on "ehalf of the *bject *wner1

Revo+ing Column.Selective Object Privileges

3lthough users can grant columnselective INSERT0 UPDATE0 and REFERENCES privileges for tables and views0 they cannot

selectively revo!e column specific privileges with a similar REVOKE statement. $nstead0 the grantor must first revo!e the

object privilege for all columns of a table or view0 and then selectively regrant the columnspecific privileges that shouldremain.



25/42

>or example0 assume that role /1+)'_#"0o1#"0 has been granted the UPDATE privilege on the "2('o and ')+" columns of

the table "2(. To revo!e the UPDATE privilege on just the "2('o column0 issue the following two statements:

REVOKE UPDATE ON "2( FROM /1+)'_#"0o1#"0*GRANT UPDATE ')+" ON "2( TO /1+)'_#"0o1#"0*

The REVOKE statement revo!es UPDATE privilege on all columns of the "2( table from the role /1+)'_#"0o1#"0.

The GRANT statement regrants UPDATE privilege on the ')+" column to the role/1+)'_#"0o1#"0.

Revo+ing the R(/(R(NC(S Object Privilege

$f the grantee of the REFERENCES object privilege has used the privilege to create a foreign !ey constraint 6that currentlyexists70 the grantor can revo!e the privilege only by specifying the CASCADE CONSTRAINTS option in the REVOKE statement:

REVOKE REFERENCES ON "2( FROM 5-)# CASCADE CONSTRAINTS*

3ny foreign !ey constraints currently defined that use the revo!ed REFERENCES privilege are dropped when the CASCADE

CONSTRAINTS clause is specified.

Cascading (%%ects o% Revo+ing Privileges

'epending on the type of privilege0 there may be cascading effects when a privilege is revo!ed.

System Privileges

There are no cascading effects when revo!ing a system privilege related to ''# operations0 regardless of whether the privilege was granted with or without the ADMIN OPTION. >or example0 assume the following:

8 The security administrator grants the CREATE TABLE system privilege to 5


26/42

8 User 5


27/42

• The object privilege grants propagated using the GRANT OPTION are revo!ed if a grantor5s object privilege is revo!ed.

>or example0 assume that 10"# is granted the SELECT object privilege with theGRANT OPTION0 and grants

the SELECT privilege on "+2 to 10"#9. +ubse?uently0 the SELECT privilege is revo!ed from 10"#. This REVOKE iscascaded to 10"#9 as well. 3ny objects that depended on10"#5s and 10"#95s revo!ed SELECT privilege can also be

affected0 as described in previous bullet items.

*bject definitions that re?uire the ALTER and INDEX DDL object privileges are not affected if the ALTER or INDEX object

privilege is revo!ed. >or example0 if the INDEX privilege is revo!ed from a user that created an index on someone else5s table0the index continues to exist after the privilege is revo!ed.

,ranting to and Revo+ing %rom the User ,rou" PU0-C

Privileges and roles can also be granted to and revo!ed from the user group PUBLIC. "ecause PUBLIC is accessible to every

database user0 all privileges and roles granted to PUBLIC are accessible to every database user.

+ecurity administrators and database users should grant a privilege or role to PUBLIC only if every database user re?uires the

privilege or role. This recommendation reinforces the general rule that at any given time0 each database user should only

have the privileges re?uired to accomplish the group5s current tas!s successfully.

Revo!ing a privilege from PUBLIC can cause significant cascading effects. $f any privilege related to a 'M# operation is

revo!ed from PUBLIC 6for example0 SELECT ANY TABLE6 UPDATE ON "+270 all procedures in the database0 including functionsand pac!ages0 must be reaut#ori$ed before they can be used again. Therefore0 exercise caution when granting and revo!ing'M#related privileges toPUBLIC.

See Also:

1Managing *bject 'ependencies1 for more information about object dependencies

http://docs.oracle.com/cd/B10501_01/server.920/a96521/general.htm#10428http://docs.oracle.com/cd/B10501_01/server.920/a96521/general.htm#10428http://docs.oracle.com/cd/B10501_01/server.920/a96521/general.htm#10428


28/42

1hen $o ,rants and Revo+es &a+e (%%ect2

'epending on what is granted or revo!ed0 a grant or revo!e ta!es effect at different times:

• 3ll grantsFrevo!es of system and object privileges to anything 6users0 roles0 and PUBLIC7 are immediately observed.

• 3ll grantsFrevo!es of roles to anything 6users0 other roles0 PUBLIC7 are only observed when a current user session

issues a SET ROLE statement to reenable the role after the grantFrevo!e0 or when a new user session is created after the

grantFrevo!e.

@ou can see which roles are currently enabled by examining the SESSION_ROLES data dictionary view.

&he S(& RO0( Statement

'uring the session0 the user or an application can use the SET ROLE statement any number of times to change the roles

currently enabled for the session. @ou 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 initiali;ation parameter MAX_ENABLED_ROLES.

This example enables the role !"#0 which you have already been granted0 and specifies the password.

SET ROLE !"# IDENTIFIED BY %&"'("''&)!*

@ou can disable all roles with the following statement:

SET ROLE NONE*

S"eci%ying $e%ault Roles

&hen a user logs on0 *racle enables all privileges granted explicitly to the user and all privileges in the user5s default roles.


29/42

3 user5s 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 database0 without re?uiring the user to specify the roles5

passwords. The user must have already been directly granted the roles with a GRANT statement. @ou cannot specify as adefault role any role managed by an external service including a directory service 6external roles or global roles7.

The following example establishes default roles for user 5)'":

ALTER USER 5)'" DEFAULT ROLE 2)4!"#6 2"((4)0/*

@ou cannot set a user5s default roles in the CREATE USER statement. &hen you first create a user0 the user5s default role settingis ALL0 which causes all roles subse?uently granted to the user to be default roles. Use the ALTER USER statement to limit the

user5s default roles.

Caution:

&hen you create a role 6other than a user role70 it is granted to you implicitly and added as a default role. @ou

receive an error at login if you have more thanMAX_ENABLED_ROLES. @ou can avoid this error by altering the

user5s default roles to be less than MAX_ENABLED_ROLES. Thus0 you should change the DEFAULT ROLEsettings

of SYS and SYSTEM before creating user roles.

Restricting the Number o% Roles that a User Can (nable

3 user can enable as many roles as specified by the initiali;ation parameter MAX_ENABLED_ROLES. 3ll 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. Digher values permit each user session to have more concurrently enabled roles.Dowever0 the larger the value for this parameter0 the more memory space is re?uired on behalf of each user sessionC this is


30/42

because the P3 si;e is affected for each user session0 and re?uires four bytes for each role. 'etermine the highest numberof roles that will be concurrently enabled by any one user and use this value for the MAX_ENABLED_ROLES parameter.

,ranting Roles Using the O"erating System or Net*or+

This section describes aspects of granting roles through your operating system or networ!0 and contains the following topics:

• Using *perating +ystem Role $dentification

• Using *perating +ystem Role Management

• ranting and Revo!ing Roles &hen *+HR*#(+ITRU(

• (nabling and 'isabling Roles &hen *+HR*#(+ITRU(

• Using ,etwor! %onnections with *perating +ystem Role Management

$nstead of a security administrator explicitly granting and revo!ing database roles to and from usersusing GRANT and REVOKE statements0 the operating system that operates *racle can grant roles to users at connect time. Roles

can be administered using the operating system and passed to *racle when a user creates a session. 3s part of this

mechanism0 each user5s default roles and the roles granted to a user with the ADMIN OPTION can be identified. (ven if theoperating system is used to authori;e users for roles0 all roles must be created in the database and privileges assigned to the

role with GRANT statements.

Roles can also be granted through a networ! service.

The advantage of using the operating system to identify a user5s database roles is that privilege management for an *racledatabase can be externali;ed. The security facilities offered by the operating system control a user5s privileges. This option

may offer advantages of centrali;ing security for a number of system activities0 such as the following situation:

http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#15599http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#15634http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#15637http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#15646http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#15656http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#15599http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#15634http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#15637http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#15646http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#15656


31/42

• M-+ *racle administrators want R3%> groups to identify a database user5s roles.

• U,$J *racle administrators want U,$J groups to identify a database user5s roles.

• -M+ *racle administrators want to use rights identifiers to identify a database user5s roles.

The main disadvantage of using the operating system to identify a user5s database roles is that privilege management canonly be performed at the role level. $ndividual privileges cannot be granted using the operating system0 but can still begranted inside the database using GRANT statements.

3 secondary disadvantage of using this feature is that by default users cannot connect to the database through the shared

server0 or any other networ! connection0 if the operating system is managing roles. Dowever0 you can change this defaultCsee 1Using ,etwor! %onnections with *perating +ystem Role Management1.

Note:

The features described in this section are available only on some operating systems. +ee your operating systemspecific *racle documentation to determine if you can use these features.

Using O"erating System Role -denti%ication

To operate a database so that it uses the operating system to identify each user5s database roles when a session is created0 setthe initiali;ation parameter OS_ROLES to TRUE 6and restart the instance0 if it is currently running7. &hen a user attempts to

create a session with the database0 *racle initiali;es the user5s security domain using the database roles identified by the

operating system.



32/42

To identify database roles for a user0 each *racle user5s operating system account must have operating system identifiers6these may be called groups0 rights identifiers0 or other similar names7 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 withthe ADMIN OPTION. ,o matter which operating system is used0 the role specification at the operating system level follows the

format:

o#)_ID _ROLE _)

where:

• ID has a definition that varies on different operating systems. >or example0 on -M+0 ID is the instance identifier of the

databaseC on M-+0 it is the machine typeC on U,$J0 it is the system ID.

Note:

ID is case sensitive to match your ORACLE_SID. ROLE is not case sensitive.

• ROLE is the name of the database role.

• is an optional character that indicates this role is to be a default role of the database user.

• ) is 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.

Note:


33/42

$f either the or ) characters are specified0 they must be preceded by an underscore.

>or example0 an operating system account might have the following roles identified in its profile:

o#)_PAYROLL_ROLEo#)_PAYROLL_ROLE9_)o#)_PAYROLL_ROLE:_o#)_PAYROLL_ROLE_)

&hen the corresponding user connects to the 2)4#o!! instance of *racle0 #o!": and #o!" are defaults0while #o!"9 and #o!" are available with the ADMIN OPTION.

Using O"erating System Role Management

&hen you use operating system managed roles0 it is important to note that database roles are being granted to an operatingsystem user. 3ny database user to which the *+ user is able to connect will have the authori;ed database roles enabled. >orthis reason0 you should consider defining all *racle users as IDENTIFIED EXTERNALLY if you are using OS_ROLES > TRUE0 so

that the database accounts are tied to the *+ account that was granted privileges.

,ranting and Revo+ing Roles 1hen OS3RO0(S4&RU(

$f OS_ROLES is set to TRUE0 the operating system completely manages the grants and revo!es of roles to users. 3ny previous

grants of roles to users using GRANT statements do not applyC however0 they are still listed in the data dictionary. *nly the role

grants made at the operating system level to users apply. Users can still grant privileges to roles and users.

Note:


34/42

$f the operating system grants a role to a user with the ADMIN OPTION0 the user can grant the role only to other

roles.

(nabling and $isabling Roles 1hen OS3RO0(S4&RU(

$f OS_ROLES is set to TRUE0 any role granted by the operating system can be dynamically enabled using the SET ROLE statement.This still applies0 even if the role was defined to re?uire a password or operating system authori;ation. Dowever0 any role

not identified in a user5s operating system account cannot be specified in a SET ROLE statement0 even if a role has beengranted using a GRANT statement when OS_ROLES > FALSE. 6$f you specify such a role0 *racle ignores it.7

&hen OS_ROLES > TRUE0 a user can enable as many roles as specified by the initiali;ation parameter MAX_ENABLED_ROLES.

Using Net*or+ Connections *ith O"erating System Role Management

$f you choose to have the operating system to manage roles0 by default users cannot connect to the database through theshared server. This restriction is the default because a remote user could impersonate another operating system user over a

nonsecure connection.

$f you are not concerned with this security ris! and want to use operating system role management with the shared server0 orany other networ! connection0 set the initiali;ation parameter REMOTE_OS_ROLESin the database5s initiali;ation parameter file

to TRUE. The change will ta!e effect the next time you start the instance and mount the database. The default setting of this

parameter is FALSE.

5ie*ing Privilege and Role -n%ormation

To access information about grants of privileges and roles0 you can ?uery the following data dictionary views:


35/42

5ie* $escri"tion

DBA_COL_PRIVS

ALL_COL_PRIVS

USER_COL_PRIVS

'"3 view describes all column object grants in the database. 3## view describes all column object grants forwhich the current user or PU"#$% is the object owner0 grantor0 or grantee. U+(R view describes column object

grants for which the current user is the object owner0 grantor0 or grantee.

ALL_COL_PRIVS_MADE

USER_COL_PRIVS_MAD

E

3## view lists column object grants for which the current user is object owner or grantor. U+(R view describes

column object grants for which the current user is the grantor.

ALL_COL_PRIVS_RECD

USER_COL_PRIVS_RECD

3## view describes column object grants for which the current user or PU"#$% is the grantee. U+(R view

describes column object grants for which the current user is the grantee.

DBA_TAB_PRIVS

ALL_TAB_PRIVS

USER_TAB_PRIVS

'"3 view lists all grants on all objects in the database. 3## view lists the grants on objects where the user or

PU"#$% is the grantee. U+(R view lists grants on all objects where the current user is the grantee.


36/42

5ie* $escri"tion

ALL_TAB_PRIVS_MADE

USER_TAB_PRIVS_MADE

3## view lists the all object grants made by the current user or made on the objects owned by the current user.U+(R view lists grants on all objects owned by the current user.

ALL_TAB_PRIVS_RECD

USER_TAB_PRIVS_RECD

3## view lists object grants for which the user or PU"#$% is the grantee. U+(R view lists object grants forwhich the current user is the grantee.

DBA_ROLES This view lists all roles that exist in the database.

DBA_ROLE_PRIVS

USER_ROLE_PRIVS

'"3 view lists roles granted to users and roles. U+(R view lists roles granted to the current user.

DBA_SYS_PRIVS

USER_SYS_PRIVS

'"3 view lists system privileges granted to users and roles. U+(R view lists system privileges granted to the

current user.

ROLE_ROLE_PRIVS This view describes roles granted to other roles. $nformation is provided only about roles to which the user has

access.


37/42

5ie* $escri"tion

ROLE_SYS_PRIVS This view contains information about system privileges granted to roles. $nformation is provided only about rolesto which the user has access.

ROLE_TAB_PRIVS This view contains information about object privileges granted to roles. $nformation is provided only about roles

to which the user has access.

SESSION_PRIVS This view lists the privileges that are currently enabled for the user.

SESSION_ROLES This view lists the roles that are currently enabled to the user.

+ome examples of using these views follow. >or these examples0 assume the following statements have been issued:

CREATE ROLE 0"1#&(4_)+&' IDENTIFIED BY /o'/o*

GRANT CREATE PROFILE6 ALTER PROFILE6 DROP PROFILE6 CREATE ROLE6 DROP ANY ROLE6 GRANT ANY ROLE6 AUDIT ANY6 AUDIT SYSTEM6 CREATE USER6 BECOME USER6 ALTER USER6 DROP USER TO 0"1#&(4_)+&' WITH ADMIN OPTION*

GRANT SELECT6 DELETE ON SYS.AUD$ TO 0"1#&(4_)+&'*

GRANT 0"1#&(4_)+&'6 CREATE SESSION TO 0-&!!&)+0*

GRANT 0"1#&(4_)+&' TO 040("+_)+&'&0(#)(o#*

GRANT CREATE SESSION TO 5-)#*


38/42

GRANT SELECT6 DELETE ON "+2 TO 5-)#*

GRANT INSERT "')+"6 5o% ON "+2 TO 0-&!!&)+06 5-)#*See Also:

Oracle9i Database Reference for a detailed description of these data dictionary views

0isting All System Privilege ,rants

The following ?uery returns all system privilege grants made to roles and users:

SELECT FROM DBA_SYS_PRIVS*

GRANTEE PRIVILEGE ADM@@@@@@@@@@@@@@ @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ @@@SECURITY_ADMIN ALTER PROFILE YESSECURITY_ADMIN ALTER USER YESSECURITY_ADMIN AUDIT ANY YESSECURITY_ADMIN AUDIT SYSTEM YESSECURITY_ADMIN BECOME USER YESSECURITY_ADMIN CREATE PROFILE YESSECURITY_ADMIN CREATE ROLE YESSECURITY_ADMIN CREATE USER YES

SECURITY_ADMIN DROP ANY ROLE YESSECURITY_ADMIN DROP PROFILE YESSECURITY_ADMIN DROP USER YESSECURITY_ADMIN GRANT ANY ROLE YESSWILLIAMS CREATE SESSION NO;WARD CREATE SESSION NO

0isting All Role ,rants

The following ?uery returns all the roles granted to users and other roles:

http://docs.oracle.com/cd/B10501_01/server.920/a96536/toc.htmhttp://docs.oracle.com/cd/B10501_01/server.920/a96536/toc.htm


39/42

SELECT FROM DBA_ROLE_PRIVS*

GRANTEE GRANTED_ROLE ADM

@@@@@@@@@@@@@@@@@@ @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ @@@SWILLIAMS SECURITY_ADMIN NO

0isting Object Privileges ,ranted to a User

The following ?uery returns all object privileges 6not including columnspecific privileges7 granted to the specified user:

SELECT TABLE_NAME6 PRIVILEGE6 GRANTABLE FROM DBA_TAB_PRIVS WHERE GRANTEE > ?;WARD?*

TABLE_NAME PRIVILEGE GRANTABLE

@@@@@@@@@@@ @@@@@@@@@@@@ @@@@@@@@@@EMP SELECT NOEMP DELETE NO

To list all the columnspecific privileges that have been granted0 use the following ?uery:

SELECT GRANTEE6 TABLE_NAME6 COLUMN_NAME6 PRIVILEGE FROM DBA_COL_PRIVS*

GRANTEE TABLE_NAME COLUMN_NAME PRIVILEGE

@@@@@@@@@@@ @@@@@@@@@@@@ @@@@@@@@@@@@@ @@@@@@@@@@@@@@SWILLIAMS EMP ENAME INSERTSWILLIAMS EMP ;OB INSERT;WARD EMP NAME INSERT;WARD EMP ;OB INSERT

0isting the Current Privilege $omain o% Your Session

The following ?uery lists all roles currently enabled for the issuer:

SELECT FROM SESSION_ROLES*


40/42

$f 0-&!!&)+0 has enabled the 0"1#&(4_)+&' role and issues this ?uery0 *racle returns the following information:

ROLE@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@SECURITY_ADMIN

The following ?uery lists all system privileges currently available in the issuer5s security domain0 both from explicit privilege grants and from enabled roles:

SELECT FROM SESSION_PRIVS*

$f 0-&!!&)+0 has the 0"1#&(4_)+&' role enabled and issues this ?uery0 *racle returns the following results:

PRIVILEGE@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@AUDIT SYSTEMCREATE SESSIONCREATE USERBECOME USERALTER USERDROP USER

CREATE ROLEDROP ANY ROLEGRANT ANY ROLEAUDIT ANYCREATE PROFILEALTER PROFILEDROP PROFILE

$f the 0"1#&(4_)+&' role is disabled for 0-&!!&)+00 the first ?uery would have returned no rows0 while the second ?uerywould only return a row for the CREATE SESSION privilege grant.


41/42

0isting Roles o% the $atabase

The DBA_ROLES data dictionary view can be used to list all roles of a database and the authentication used for each role. >orexample0 the following ?uery lists all the roles in the database:

SELECT FROM DBA_ROLES*

ROLE PASSWORD@@@@@@@@@@@@@@@@ @@@@@@@@CONNECT NORESOURCE NODBA NOSECURITY_ADMIN YES

0isting -n%ormation About the Privilege $omains o% Roles

The ROLE_ROLE_PRIVS0 ROLE_SYS_PRIVS0 and ROLE_TAB_PRIVS data dictionary views contain information on the privilege

domains of roles.

>or example0 the following ?uery lists all the roles granted to the 040("+_)+&' role:

SELECT GRANTED_ROLE6 ADMIN_OPTION FROM ROLE_ROLE_PRIVS WHERE ROLE > ?SYSTEM_ADMIN?*

GRANTED_ROLE ADM@@@@@@@@@@@@@@@@ @@@@SECURITY_ADMIN NO

The following ?uery lists all the system privileges granted to the 0"1#&(4_)+&' role:

SELECT FROM ROLE_SYS_PRIVS WHERE ROLE > ?SECURITY_ADMIN?*

ROLE PRIVILEGE ADM


42/42

@@@@@@@@@@@@@@@@@@@@@@@ @@@@@@@@@@@@@@@@@@@@@@@@@@@@@ @@@SECURITY_ADMIN ALTER PROFILE YESSECURITY_ADMIN ALTER USER YES

SECURITY_ADMIN AUDIT ANY YESSECURITY_ADMIN AUDIT SYSTEM YESSECURITY_ADMIN BECOME USER YESSECURITY_ADMIN CREATE PROFILE YESSECURITY_ADMIN CREATE ROLE YESSECURITY_ADMIN CREATE USER YESSECURITY_ADMIN DROP ANY ROLE YESSECURITY_ADMIN DROP PROFILE YESSECURITY_ADMIN DROP USER YESSECURITY_ADMIN GRANT ANY ROLE YES

The following ?uery lists all the object privileges granted to the 0"1#&(4_)+&' role:

SELECT TABLE_NAME6 PRIVILEGE FROM ROLE_TAB_PRIVS WHERE ROLE > ?SECURITY_ADMIN?*

TABLE_NAME PRIVILEGE@@@@@@@@@@@@@@@@@@@@@@@@@@@ @@@@@@@@@@@@@@@@AUD$ DELETEAUD$ SELECT

managing user privileges and roles

Documents