eneral
defrole$ user$
dba_roles session_roles
dba_role_pri user_application_r
vs oles
Data Dictionary role_role_pr
Objects Related to user_role_privs
Roles ivs
role_sys_pri
v$pwfile_users
vs
role_tab_pri
vs
System Privileges alter any create drop any grant any
Related To Roles role role role role
Role Name Description
Installation roles AQ_ADMINISTRATOR_ROL Privilege to administer Advanced
E Queuing
AQ_USER_ROLE Deprecated
AUTHENTICATEDUSER DBUriServlet Security
Contains the create session privilege
CONNECT
(only)
Provides user privileges to manage the
CSW_USR_ROLE Catalog Services for the Web (CSW)
component of Oracle Spatial.
Enables developers create Oracle Text
CTXAPP indexes and index preferences, and to
use PL/SQL packages.
Provides privileges to manage Common
Warehouse Metadata (CWM), which is a
CWM_USER
repository standard used by Oracle data
warehousing and decision support.
The
DATAPUMP_EXP_FULL_DATABASE
role affects only Export operations. It
allows users running these operations to
do the following:
Perform the operation outside of
the scope of their schema
Monitor jobs that were initiated
by another user
Export objects (for example,
DATAPUMP_EXP_FULL_DA
TABLESPACE definitions) that
TABASE
unprivileged users cannot
reference
Although the SYS schema does not
have the
DATAPUMP_EXP_FULL_DATABASE
role assigned to it, all security checks
performed by Data Pump that require the
DATAPUMP_EXP_FULL_DATABASE
role will also grant access to the SYS
schema.
This role affects only Import and
SQL_FILE operations. It allows users
running these operations to do the
following:
Perform the operation outside of
the scope of their schema
Monitor jobs that were initiated
by another user
Import objects (for example,
DATAPUMP_IMP_FULL_DA
DIRECTORY definitions) that
TABASE
unprivileged users cannot create
Although the SYS schema does not
have the
DATAPUMP_IMP_FULL_DATABASE
role assigned to it, all security checks
performed by Data Pump that require the
DATAPUMP_IMP_FULL_DATABASE
role will also grant access to the SYS
schema.
Example Database Administrator role.
DBA
Should not be used
Allow users to delete records from the
DELETE_CATALOG_ROLE
system audit table (AUD$)
Related to the Java API and Data Miner.
In Release 1, a separate role called
DMUSER_ROLE has to be created
(using the script dm/admin/odmcrt.sql),
DMUSER_ROLE
and every user of the ODM Java API or
Data Miner must be granted privileges
on this role. This is no longer a
requirement in Release 2.
DM_CATALOG_ROLE Undocumented
Provides privileges to connect to EJBs
EJBCLIENT
from a Java stored procedure.
Allow users EXECUTE privileges for
EXECUTE_CATALOG_ROL
packages and procedures in the data
E
dictionary
Provides the privileges required to
EXP_FULL_DATABASE 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.
To update the dictionary system
GATHER_SYSTEM_STATIS statistics a user must have DBA
TICS 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).
Provides privileges for DBAs who need
HS_ADMIN_ROLE 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.
Provides the privileges required to
IMP_FULL_DATABASE 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.
Grants permissions to run the Java
JAVADEBUGPRIV
debugger
JAVAIDPRIV Deprecated
Grants permissions for Java
JAVASYSPRIV administrators including updating JVM-
protected packages
Grants permissions for Java users such
JAVAUSERPRIV
as examining properties
Java administration privileges including
JAVA_ADMIN
permission to modify PolicyTable.
Provides privileges to deploy ncomp
DLLs into thejavavm/admin directory
using the ncomp and deploynsutilities.
JAVA_DEPLOY
Without this role,
the javavm/deploy andjavavm/admi
n directories cannot be accessible.
Provides permissions to start and
maintain a JMX agent in a session. The
procedure dbms_java.start_jmx_agent
JMXSERVER
starts the agent in a specific session that
generally remains active for the duration
of the session.
LOGSTDBY_ADMINISTRAT A prototype role created by default with
OR 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.
Provides adminstrative privileges to
MGMT_USER perform various activities with Oracle
Enterprise Manager.
Required to run the Segment Advisor
OEM_ADVISOR
manually with Enterprise Manager.
Provides privileges needed by the
Management Agent component of
OEM_MONITOR
Oracle Enterprise Manager to monitor
and manage the database.
Provides privileges to perform OLAP API
OLAPI_TRACE_USER tracing. Contact Oracle Support for more
information.
To create dimensional objects in any
OLAP_DBA
schema
OLAP_USER Create dimensional objects
OLAP_XS_ADMIN Administer OLAP data security
After installing Oracle Multimedia
DICOM, the ORDADMIN role is created,
with the database system privileges
required for administration of the DICOM
ORDADMIN data model repository.
The ORDADMIN role must be assigned
to the administrator of the DICOM data
model repository.
Privileges granted to PUBLIC are
OWB$CLIENT
available to all sessions.
Provides privileges from the database
level for any registered Oracle
Warehouse Builder user to query the
Warehouse Builder public views, such as
ALL_IV_PROJECTS. A Warehouse
OWB_DESIGNCENTER_VIE
Builder administrator can use the
W
ACCESS_PUBLICVIEW_BROWSER
system privilege from the Warehouse
Builder security level to control an
Warehouse Builder user's access to
those public views.
With Oracle Warehouse builder enables
a remote Oracle WorkFlow instance to
OWB_USER
connect to the services provided by the
Control Center.
Traditionally required to use
PLUSTRACE AUTOTRACE but in 11gR1 it seems to
function without this role being required.
PUBLIC -
Provides privileges for owner of the
RECOVERY_CATALOG_OW recovery catalog. Includes: CREATE
NER SESSION, ALTER SESSION, CREATE
SYNONYM, CREATE VIEW, CREATE
DATABASE LINK, CREATE TABLE,
CREATE CLUSTER, CREATE
SEQUENCE, CREATE TRIGGER, and
CREATE PROCEDURE
Provides the following system privileges:
RESOURCE 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.
Allows the grantee to execute the
procedures of the DBMS_SCHEDULER
SCHEDULER_ADMIN package. It includes all of the job
scheduler system privileges and is
included in the DBA role.
Provides SELECT privilege on objects in
SELECT_CATALOG_ROLE the data dictionary. Also provides the
HS_ADMIN_ROLE privilege.
Privileges granted the Catalog Services
for the Web (CSW) account used by the
Oracle Spatial CSW cache manager to
SPATIAL_CSW_ADMIN load all record type metadata, and
record instances from the database into
the main memory for the record types
that are cached.
Privileges granted the Web Feature
Service (WFS) account used by the
Oracle Spatial WFS cache manager to
SPATIAL_WFS_ADMIN load all feature type metadata, and
feature instances from the database into
main memory for the feature types that
are cached.
Privileges granted a Web Feature
WFS_USR_ROLE
Service (WFS) user
Privileges that must be granted to
WKUSER database users hosting new Oracle
Ultra Search instances.
Contains all Workspace Manager
privileges with the grant option. By
WM_ADMIN_ROLE default, the database administrator (DBA
role) is granted the WM_ADMIN_ROLE
role.
Allows the grantee to register an XML
schema globally, as opposed to
registering it for use or access only by its
XDBADMIN
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.
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
XDB_WEBSERVICES
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.
Allows the grantee to access Oracle
Database Web services over HTTP.
However, it does not provide the user
XDB_WEBSERVICES_OVE access to objects in the database that
R_HTTP are public. To allow public access, you
need to grant the user the
XDB_WEBSERVICES_WITH_PUBLIC
role.
Allows the grantee access to public
XDB_WEBSERVICES_WITH
objects through Oracle Database Web
_PUBLIC
services.
SELECT name USER_NAMES
FROM user$
Roles are treated like WHERE type# = 1;
users in the data
dictionary SELECT name ROLE_NAMES
FROM user$
WHERE type# = 0;
Controlling The max_enabled_roles = <integer>
Number Of Roles
With An init.ora max_enabled_roles = 100
Parameter
Roles can contain system privileges
Roles can contain object privileges
Roles can contain roles
Object privileges granted through
NOTE: roles do not work within
procedures, functions, and
packages. Those permissions must be
granted explicitly to the user.
Creating Roles
CREATE ROLE <role_name>;
Create Role
CREATE ROLE read_only;
Create Password CREATE ROLE <role_name> IDENTIFIED BY
Protected Role <password>;
CREATE ROLE dba IDENTIFIED
BY "S0^Sorry";
Assigning Privileges And Roles To Roles
Assign Privilege To A GRANT <privilege_name> TO <role_name>;
Role GRANT create session TO read_only
GRANT <role_name> TO <role_name>;
CREATE ROLE ap_clerk;
GRANT read_only TO ap_clerk;
Create A Role GRANT select ON general_ledger TO ap_cl
Heirarchy erk;
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;
GRANT <roles and privileges> TO
<role_name>;
CREATE ROLE ap_manager IDENTIFIED BY
appwd;
Add Another Layer To
The Heirarchy 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
GRANT <roles_name> TO <user_name>;
GRANT read_only TO jbiden;
Assigning Roles To GRANT ap_clerk TO jstough;
Users GRANT ap_clerk TO ckeizer;
GRANT ap_clerk TO rallen;
GRANT ap_manager TO escott;
Revoking Privileges From Roles
REVOKE <privilege_name> FROM <role_name
>;
Revoke Privilege
REVOKE select any
table FROM ap_manager;
Revoking Roles
Revoke a role from a REVOKE <role_name> FROM <user_name>;
user REVOKE ap_manager FROM escott;
REVOKE ALL ON <table_name>
FROM <schema_name>
Revoke A Role And CASCADE CONSTRAINTS;
Drop Any Invalidated
Constraints REVOKE ALL ON invoices
FROM abc
CASCADE CONSTRAINTS;
Activating & Decactivating Roles
SET ROLE <role_name>;
Activating A Role
SET ROLE ap_clerk;
SET ROLE <role_name> IDENTIFIED BY
Activating A Password <role_password>;
Protected Role SET ROLE ap_manager IDENTIFIED
BY appwd;
Activating All Roles SET ROLE all;
Activating All Roles SET ROLE all EXCEPT <role_name>;
Except One 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
DROP ROLE <role_name>;
Dropping A Role
DROP ROLE manager_role;
PLUSTRACE Role
This role must be created by SYS and
grants SELECT on the following v_$
views:
Creating And V_$SESSTAT
Assigning The V_$STATNAME
PLUSTRACE Role
Used By
V_$MYSTAT
AUTOTRACE
SQL> @c:\oracle\product\ora10\sqlplus\
admin\plustrce.sql
GRANT plustrace TO uwclass;
Role Related Queries
All Roles Available In SELECT name
The Database
FROM user$
WHERE type# = 0;
Roles Granted To A SELECT *
User FROM user_role_privs;
Privileges Granted To SELECT *
A Role FROM role_sys_privs;
SELECT DISTINCT privilege
System Privileges
FROM dba_sys_privs;
CREATE OR REPLACE PROCEDURE GRANT_SELEC
T AS
CURSOR ut_cur IS
SELECT table_name
FROM user_tables;
RetVal NUMBER;
sCursor INT;
sqlstr VARCHAR2(250);
BEGIN
Grant SELECT On All FOR ut_rec IN user_tabs_cur;
Tables In A Schema 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 SELECT grantee, granted_role
Schemas FROM dba_role_privs;
Tables And Columns SELECT *
That Can Be Modified
by a User FROM all_updatable_columns;
DBA_TAB_PRIVS (DBA Table Privileges)
DBA_TAB_PRIVS de
scribes the object
Column Datatype NULL Description
grants for which the GRANTEE VARCHAR2(30) NOT Name of the user to whom
current user is the NULL
access was granted
object owner, grantor,
or grantee.
OWNER VARCHAR2(30) NOT Owner of the object
NULL
TABLE_NAME VARCHAR2(30) NOT Name of the object. The
NULL
object can be any object,
including tables, packages,
indexes, sequences, and so
on.
GRANTOR VARCHAR2(30) NOT Name of the user who
NULL
performed the grant
PRIVILEGE VARCHAR2(40) NOT Privilege on the object
NULL
GRANTABLE VARCHAR2(3) Indicates whether the
privilege was granted with
the GRANT OPTION(YES) or
not (NO)
HIERARCHY VARCHAR2(3) Indicates whether the
privilege was granted with
the HIERARCHY
OPTION (YES) or not (NO)
USER_TAB_PRIVS (USER Table Privileges)
USER_TAB_PRIVS describes the object grants for which the current user is the object owner,
grantor, or grantee. Its columns are the same as those in DBA_TAB_PRIVS.
