Oracle Roles (转载)

Oracle Roles

Version 10.2

General

Data Dictionary Objects Related to Roles

defrole$	user$
dba_roles	session_roles
dba_role_privs	user_application_roles
role_role_privs	user_role_privs
role_sys_privs	v$pwfile_users
role_tab_privs

System Privileges Related To Roles

alter any role

create role

drop any role

grant any role

Installation roles

Role Name	Description
AQ_ADMINISTRATOR_ROLE	Privilege to administer Advanced Queuing
AQ_USER_ROLE	Deprecated
AUTHENTICATEDUSER	DBUriServlet Security
CONNECT	Contains the create session privilege (only)
CSW_USR_ROLE	Not documented
CTXAPP	Enables developers create Oracle Text indexes and index preferences, and to use PL/SQL packages.
CWM_USER	Undocumented
DATAPUMP_EXP_FULL_DATABASE	Undocumented
DATAPUMP_IMP_FULL_DATABASE	Undocumented
DBA	Example Database Administrator role. Should not be used
DELETE_CATALOG_ROLE	Allow users to delete records from the system audit table (AUD$)
DMUSER_ROLE	Undocumented
DM_CATALOG_ROLE	Undocumented
EJBCLIENT	Undocumented
EXECUTE_CATALOG_ROLE	Allow users EXECUTE privileges for packages and procedures in the data dictionary
EXP_FULL_DATABASE	Provides the privileges required to perform full and incremental database exports, and includes: SELECT ANY TABLE, BACKUP ANY TABLE, EXECUTE ANY PROCEDURE, EXECUTE ANY TYPE, ADMINISTER RESOURCE MANAGER, and INSERT, DELETE, and UPDATE on the tables SYS.INCVID, SYS.INCFIL, and SYS.INCEXP. Also the following roles: EXECUTE_CATALOG_ROLE and SELECT_CATALOG_ROLE.
GATHER_SYSTEM_STATISTICS	To update the dictionary system statistics a user must have DBA privileges or the GATHER_SYSTEM_STATISTICS role.
GLOBAL_AQ_USER_ROLE	Required to register through LDAP using JDBC connection parameters as this requires the ability to write access to the connection factory entries in the LDAP server (which requires the LDAP user to be either the database itself or be granted GLOBAL_AQ_USER_ROLE).
HS_ADMIN_ROLE	Provides privileges for DBAs who need to use the DBA role using Oracle Database Heterogeneous Services to access appropriate tables in the data dictionary. Used to protect access to the Heterogeneous Services (HS) data dictionary tables (grants SELECT) and packages (grants EXECUTE). It is granted to SELECT_CATALOG_ROLE and EXECUTE_CATALOG_ROLE such that users with generic data dictionary access also can access the HS data dictionary.
IMP_FULL_DATABASE	Provides the privileges required to perform full database imports. Includes an extensive list of system privileges (use view DBA_SYS_PRIVS to view privileges) and the following roles: EXECUTE_CATALOG_ROLE and SELECT_CATALOG_ROLE. This role is provided for convenience in using the export and import utilities.
JAVADEBUGPRIV	Grants permissions to run the Java debugger
JAVAIDPRIV	Undocumented
JAVASYSPRIV	Grants permissions for Java administrators including updating JVM-protected packages
JAVAUSERPRIV	Grants permissions for Java users such as examining properties
JAVA_ADMIN	Java administration privileges including permission to modify PolicyTable.
JAVA_DEPLOY	Undocumented
JMXSERVER	Provides permissions to start and maintain a JMX agent in a session. The procedure dbms_java.start_jmx_agent starts the agent in a specific session that generally remains active for the duration of the session.
LOGSTDBY_ADMINISTRATOR	A prototype role created by default with RESOURCE, and EXECUTE on DBMS_LOGSTDBY privileges. It is advisable to not use this role but rather to craft your own specific to your needs. Read Oracle's comments, in red with respect to RESOURCE. They apply here too.
MGMT_USER	Undocumented
OEM_ADVISOR	Required to run the Segment Advisor manually with Enterprise Manager.
OEM_MONITOR	Undocumented
OLAPI_TRACE_USER	Undocumented
OLAP_DBA	To create dimensional objects in any schema
OLAP_USER	Create dimensional objects
OLAP_XS_ADMIN	Administer OLAP data security
ORDADMIN	After installing Oracle Multimedia DICOM, the ORDADMIN role is created, with the database system privileges required for administration of the DICOM data model repository. The ORDADMIN role must be assigned to the administrator of the DICOM data model repository.
OWB$CLIENT	Privileges granted to PUBLIC are available to all sessions.
OWB_DESIGNCENTER_VIEW	Undocumented
OWB_USER	With Oracle Warehouse builder enables a remote Oracle WorkFlow instance to connect to the services provided by the Control Center.
PLUSTRACE	Traditionally required to use AUTOTRACE but in 11gR1 it seems to function without this role being required.
PUBLIC	-
RECOVERY_CATALOG_OWNER	Provides privileges for owner of the recovery catalog. Includes: CREATE SESSION, ALTER SESSION, CREATE SYNONYM, CREATE VIEW, CREATE DATABASE LINK, CREATE TABLE, CREATE CLUSTER, CREATE SEQUENCE, CREATE TRIGGER, and CREATE PROCEDURE
RESOURCE	Provides the following system privileges: CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, CREATE TYPE. This role is provided for compatibility with previous releases of Oracle Database. You can determine the privileges encompassed by this role by querying the DBA_SYS_PRIVS data dictionary view. Note: Oracle recommends that you design your own roles for database security rather than relying on this role. This role may not be created automatically by future releases of Oracle Database.
SCHEDULER_ADMIN	Allows the grantee to execute the procedures of the DBMS_SCHEDULER package. It includes all of the job scheduler system privileges and is included in the DBA role.
SELECT_CATALOG_ROLE	Provides SELECT privilege on objects in the data dictionary. Also provides the HS_ADMIN_ROLE privilege.
SPATIAL_CSW_ADMIN	Privileges granted the Catalog Services for the Web (CSW) account used by the Oracle Spatial CSW cache manager to load all record type metadata, and record instances from the database into the main memory for the record types that are cached.
SPATIAL_WFS_ADMIN	Privileges granted the Web Feature Service (WFS) account used by the Oracle Spatial WFS cache manager to load all feature type metadata, and feature instances from the database into main memory for the feature types that are cached.
WFS_USR_ROLE	Privileges granted a Web Feature Service (WFS) user
WKUSER	Privileges that must be granted to database users hosting new Oracle Ultra Search instances.
WM_ADMIN_ROLE	Contains all Workspace Manager privileges with the grant option. By default, the database administrator (DBA role) is granted the WM_ADMIN_ROLE role.
XDBADMIN	Allows the grantee to register an XML schema globally, as opposed to registering it for use or access only by its owner. It also lets the grantee bypass access control list (ACL) checks when accessing Oracle XML DB Repository.
XDB_SET_INVOKER	Allows the grantee to define invoker's rights handlers and to create or update the resource configuration for XML repository triggers. By default, Oracle Database grants this role to the DBA role but not to the XDBADMIN role.
XDB_WEBSERVICES	Allows the grantee to access Oracle Database Web services over HTTPS. However, it does not provide the user access to objects in the database that are public. To allow public access, you need to grant the user the XDB_WEBSERVICES_WITH_PUBLIC role. For a user to use these Web services, SYS must enable the Web service servlets.
XDB_WEBSERVICES_OVER_HTTP	Allows the grantee to access Oracle Database Web services over HTTP. However, it does not provide the user access to objects in the database that are public. To allow public access, you need to grant the user the XDB_WEBSERVICES_WITH_PUBLIC role.
XDB_WEBSERVICES_WITH_PUBLIC	Allows the grantee access to public objects through Oracle Database Web services.

Roles are treated like users in the data dictionary

SELECT name USER_NAMES
FROM user$
WHERE type# = 1;

SELECT name ROLE_NAMES
FROM user$
WHERE type# = 0;

Controlling The Number Of Roles With An init.ora Parameter

max_enabled_roles = <integer>

max_enabled_roles = 100

NOTE:

• Roles can contain system privileges
• Roles can contain object privileges
• Roles can contain roles
• Object privileges granted through roles do not work within procedures, functions, and packages. Those permissions must be granted explicitly to the user.

Creating Roles

Create Role

CREATE ROLE <role_name>;

CREATE ROLE read_only;

Create Password Protected Role

CREATE ROLE <role_name> IDENTIFIED BY <password>;

CREATE ROLE dba IDENTIFIED BY "S0^Sorry";

Assigning Privileges And Roles To Roles

Assign Privilege To A Role

GRANT <privilege_name> TO <role_name>;

GRANT create session TO read_only

Create A Role Heirarchy

GRANT <role_name> TO <role_name>;

CREATE ROLE ap_clerk;

GRANT read_only TO ap_clerk;
GRANT select ON general_ledger TO ap_clerk;
GRANT insert ON ap_master TO ap_clerk;
GRANT update ON ap_master TO ap_clerk;
GRANT insert ON ap_detail TO ap_clerk;
GRANT update ON ap_detail TO ap_clerk;

Add Another Layer To The Heirarchy

GRANT <roles and privileges> TO <role_name>;

CREATE ROLE ap_manager IDENTIFIED BY appwd;

GRANT ap_clerk TO ap_manager;
GRANT delete ON ap_master TO ap_manager;
GRANT delete ON ap_detail TO ap_manager;
GRANT select any table TO ap_manager;

Assigning Roles

Assigning Roles To Users

GRANT <roles_name> TO <user_name>;

GRANT read_only TO jcline;

GRANT ap_clerk TO jstough;
GRANT ap_clerk TO ckeizer;
GRANT ap_clerk TO rallen;

GRANT ap_manager TO escott;

Revoking Privileges From Roles

Revoke Privilege

REVOKE <privilege_name> FROM <role_name>;

REVOKE select any table FROM ap_manager;

Revoking Roles

Revoke a role from a user

REVOKE <role_name> FROM <user_name>;

REVOKE ap_manager FROM escott;

Revoke A Role And Drop Any Invalidated Constraints

REVOKE ALL ON <table_name>
FROM <schema_name>
CASCADE CONSTRAINTS;

REVOKE ALL ON invoices
FROM abc
CASCADE CONSTRAINTS;

Activating & Decactivating Roles

Activating A Role

SET ROLE <role_name>;

SET ROLE ap_clerk;

Activating A Password Protected Role

SET ROLE <role_name> IDENTIFIED BY <role_password>;

SET ROLE ap_manager IDENTIFIED BY appwd;

Activating All Roles

SET ROLE all;

Activating All Roles Except One

SET ROLE all EXCEPT <role_name>;

SET ROLE all EXCEPT ap_manager;

Deactivating A Role

Can not be done on an individual basis

Deactivating All Roles

SET ROLE none;

Drop Role

Dropping A Role

DROP ROLE <role_name>;

DROP ROLE manager_role;

PLUSTRACE Role

Creating And Assigning The PLUSTRACE Role Used By AUTOTRACE

This role must be created by SYS and grants SELECT on the following v_$ views:

• V_$SESSTAT
• V_$STATNAME
• V_$MYSTAT

SQL> @c:\oracle\product\ora10\sqlplus\admin\plustrce.sql

GRANT plustrace TO uwclass;

Role Related Queries

All Roles Available In The Database

SELECT name
FROM user$
WHERE type# = 0;

Roles Granted To A User

SELECT *
FROM user_role_privs;

Privileges Granted To A Role

SELECT *
FROM role_sys_privs;

System Privileges

SELECT DISTINCT privilege
FROM dba_sys_privs;

Grant SELECT On All Tables In A Schema

CREATE OR REPLACE PROCEDURE GRANT_SELECT AS

CURSOR ut_cur IS
SELECT table_name
FROM user_tables;

RetVal NUMBER;
sCursor INT;
sqlstr VARCHAR2(250);

BEGIN
FOR ut_rec IN user_tabs_cur;
LOOP
sqlstr := 'GRANT SELECT ON '||ut_rec.table_name
|| ' TO jwc7675';
sCursor := dbms_sql.open_cursor;
dbms_sql.parse(sCursor,sqlstr, dbms_sql.native);

RetVal := dbms_sql.execute(sCursor);
dbms_sql.close_cursor(sCursor);
END LOOP;
END grant_select;

Roles Granted To Schemas

SELECT grantee, granted_role
FROM dba_role_privs;

Tables And Columns That Can Be Modified by a User

SELECT *
FROM all_updatable_columns;

Other Related Topics

Autotrace

Consumer Groups

Object Privileges

Profiles

System Privileges

Users

Contact Us � Legal Notices and Terms of Use � Privacy Statement