home

alter user s_nq_sched quota unlimited on users;

Notes from : http://www.psoug.org/reference/roles.html

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
dba_tab_privs

 

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 jbiden;

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;

 

 

 

 

Role Name Description
DBA Example Database Administrator role. Should not be used
PUBLIC  
CONNECT Contains the create session privilege (only)
SELECT_CATALOG_ROLE Provides SELECT privilege on objects in the data dictionary. Also provides the HS_ADMIN_ROLE privilege

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.
CTXAPP Enables developers create Oracle Text indexes and index preferences, and to use PL/SQL packages.
DATAPUMP_EXP_FULL_DATABASE Undocumented
DATAPUMP_IMP_FULL_DATABASE Undocumented
DELETE_CATALOG_ROLE Allow users to delete records from the system audit table (AUD$)
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).

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

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
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.
AQ_ADMINISTRATOR_ROLE Privilege to administer Advanced Queuing 
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.