 |
|
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:
CREATE ROLE
role
[NOT IDENTIFIED|IDENTIFIED BY password]
[IDENTIFIED EXTERNALLY|USING schema.package|GLOBALLY]
For example:
CREATE ROLE
appusers
NOT IDENTIFIED;
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:
* OSOPER
* OSDBA
Oracle may create the following roles when you
create the database:
CONNECT
RESOURCE
DBA
DELETE_CATALOG_ROLE (Oracle8)
EXECUTE_CATALOG_ROLE (Oracle8)
SELECT_CATALOG_ROLE (Oracle8)
AQ_USER_ROLE (Oracle8)
AQ_ADMINISTRATION_ROLE (Oracle8)
SNMPAGENT (Oracle8)
RECOVERY_CATALOG_OWNER (Oracle8)
HS_ADMIN_ROLE (Oracle8)
When you execute
the catalog.sql script, the following two roles are created:
*
EXP_FULL_DATABASE
*
IMP_FULL_DATABASE
In the following subsection, I explain these
special roles and how they are used for database maintenance.
OSOPER and OSDBA
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:
STARTUP
SHUTDOWN
ALTER DATABASE OPEN/MOUNT
ALTER DATABASE BACKUP CONTROLFILE
ALTER TABLESPACE BEGIN/END BACKUP
ARCHIVE LOG
RECOVER
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. |
|
