Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




Use of Roles

Oracle Tips by Burleson

Roles are collections of system, object, and row grants. Using roles allows the DBA to collect all related grants for a specific application function (or role, if you wish) under one object that can then be easily granted to your users with a single command. Using roles has several benefits:

* Reduces the number of grants, thereby making it easier to manage security.

* Dynamically changes the privileges for many users with a single grant/revoke.

* Can be selectively enabled/disabled, depending upon the application.

* Roles can be used for most system and object privileges. Privileges granted through a role cannot be used for creating a stored object (views, packages, triggers, Java, procedures, and functions). You need to grant privileges directly to the user for this.

Creating Roles

To implement a role, you first create the role and then grant system and object privileges to that role. When you create the role, there are three password options available:

* No authentication

* Operating system authentication

* Password authentication

You can set operating system authentication when the role is created or by using the database initialization parameters OS_ROLES=TRUE and REMOTE_OS_ROLES=TRUE. Note, however, if you are using the multithreaded server (MTS) option, you cannot use operating system authentication for roles.

In order to create a role, you must have the CREATE ROLE system privilege. You can create roles with Server Manager or at the command line in SQL*Plus. The command-line syntax for creating a role is:


For example:

CREATE ROLE appusers

To alter a role, you must have the ALTER ANY ROLE system privilege or have been granted the role with the WITH ADMIN OPTION. The creator of any role automatically has the WITH ADMIN OPTION for that role. Note that this command only creates the role that acts as a tag for later storage of a grants list; it has no implicit grants. The privileges and grants to a role are made identically to the method used to grant to users. Roles may also be granted to other roles in the form of a role hierarchy.

Grants to Roles

To grant a role to a user, you must either be the creator of that role or have the GRANT ANY ROLE privilege. You can grant roles to users with Oracle Enterprise Manager, at the command line in Server Manager (svrmgrl) in versions prior to 9i, or with SQL*Plus. Grants to roles will not take effect for a user if that user is currently logged in to the database with that role. Only when the user exits or sets another role do the changes take effect. Once roles have been granted to a user, they can be enabled and disabled.

The command-line syntax for granting privileges to a role is basically the same as the syntax for granting privileges to a user, as shown here:

GRANT role TO user; 

For example:

GRANT appuser TO scott;

The following syntax is for granting system privileges to roles:

GRANT system_priv_list TO role 

For example:

GRANT create session, create table TO appuser;

The following syntax is for granting object privileges to roles:

GRANT object_priv_list|ALL PRIVILEGES [(column_list)]
ON [schema.]object TO role 

       For example:

GRANT  select, insert, update
ON  scott.emp
TO  appuser;

System privileges can be granted to roles, with the exception of the UNLIMITED TABLESPACE system privilege. Grants on objects can be passed to other users or to roles if the grantee has been given the WITH GRANT OPTION. However, you cannot assign a privilege that includes the WITH GRANT OPTION to a role. The INDEX and REFERENCES privileges cannot be granted to a role; they may be granted only to a user. You can grant that role to a user or to another role. However, you cannot grant a role to itself.

Setting Roles

When a user is created, the default for active roles is set to ALL, which means that all the roles granted to a user are active. The DBA can change the default with an ALTER USER command. A user can enable multiple roles at one time and use the SET ROLE command to switch between roles or activate all roles with the command SET ROLE ALL. The SET ROLE ALL command will not work if any of the roles assigned to that user requires either a password or operating system authentication.

The command-line syntax for setting roles is:

SET ROLE role_list|ALL|NONE [EXCEPT role_list]
[IDENTIFIED BY password]             

Users can look at the SESSION_ROLES view to find the roles that are currently enabled for them. Users can look at SESSION_PRIVS view to see the privileges available to their session.

If you determine that all control of roles will be at the operating system level, you can set the database initialization parameter OS_ROLES=TRUE. Again, all roles must still be created first in the database. And though any grants you previously made using the database command line or Server Manager are still listed in the data dictionary, they cannot be used and are not in effect. If the use of roles is determined at the operating system level, the multithreaded server option cannot be used.

You can use the MAX_ENABLED_ROLES parameter in the database initialization file to set the number of roles that you will allow any user to have enabled at one time.

Special Roles

Oracle creates the following two roles when you install the Oracle executables:



Oracle may create the following roles when you create the database:







   AQ_USER_ROLE (Oracle8)


   SNMPAGENT (Oracle8)


   HS_ADMIN_ROLE (Oracle8)

When you execute the catalog.sql script, the following two roles are created:



In the following subsection, I explain these special roles and how they are used for database maintenance.


The OSOPER and OSDBA roles are created at the operating system level when Oracle is installed; they cannot be granted. The OSOPER and OSDBA roles are needed to perform database operations when the database is not mounted and, therefore, the data dictionary is not accessible. It is the OSOPER and OSDBA roles that are used when you CONNECT INTERNAL to the database using Server Manager. The database roles correspond to the operating system level role (or group) assignments of SYSDBA and SYSOPER.

The OSOPER role can perform the following:








       The OSDBA role also has the OSOPER role. In addition, the OSDBA role has the WITH ADMIN OPTION to allow it to grant system privileges to other users. This is the role that is used to create the database and to use for time-based recovery processes. Both the OSOPER and OSDBA roles include the RESTRICTED SESSION system privilege.

       If you intend to allow remote users to CONNECT INTERNAL, you need to set the REMOTE_LOGIN_PASSWORDFILE option in your database parameter file to either EXCLUSIVE or SHARED.  The user will then connect in Server Manager with the AS SYSDBA or AS SYSOPER clause at the end of the CONNECT command (CONNECT SYS AS SYSDBA). The privileges assigned to SYSDBA correspond to those for OSDBA. The privileges assigned to SYSOPER correspond to OSOPER. The operating system verifies the password provided using an external operating system file. This external file is generated using the ORAPWD utility. When the password for the INTERNAL or SYS accounts is changed with the ALTER USER command, the changes are mapped to the operating system password file. The V$PWFILE_USERS view lists users with the SYSDBA and SYSOPER privileges


See Code Depot for Full Scripts

This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter