OracleAppsDBAKK1: Master Note For Privileges And Roles (Doc ID 1347470.1)

this Document

APPLIES TO:

Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.

PURPOSE

This Master Note is intended to provide an index and references to the most frequently used My Oracle Support Notes with respect to Database Privileges and Roles. This Master Note is subdivided into categories to allow for easy access and reference to notes that are applicable to your area of interest.

SCOPE

This document is meant for use as a guide by those who are configuring or managing/troubleshooting the Oracle Privileges and Roles.

DETAILS

Oracle Database Privileges and Roles Concepts and Overview

A privilege is the right to execute a particular type of SQL statement. It can allow a user to access database objects or execute stored programs that are owned by another user or to perform system level actions.

There are two types of privileges:

system privileges
schema object privileges

System Privileges

The system privileges are not related to a certain object. They control the ability of a user to perform system level actions such as connecting to the database(creating a session), creating a table, altering a user, etc. or to run a certain type of SQL statement on any schema(select any table, create any procedure).

All the system privileges are listed in the SYSTEM_PRIVILEGE_MAP table:

SQL> select name from SYSTEM_PRIVILEGE_MAP;

NAME
----------------------------------------
CREATE EXTERNAL JOB
CHANGE NOTIFICATION
READ ANY FILE GROUP
MANAGE ANY FILE GROUP
MANAGE FILE GROUP
CREATE ANY SQL PROFILE
ADMINISTER ANY SQL TUNING SET
ADMINISTER SQL TUNING SET
...
ALTER SYSTEM

Object Privileges

The object privileges control the access to a certain objects. For different object types there are different privileges( for a procedure we do have an execute object privilege but do not have a select privilege).

Roles

A role is a named group of privileges. It facilitates the management of individual privileges. The users who are granted a role inherit all the privileges(and roles) that have been granted to that role. The roles can be secured so that they are enabled only if the user supplies a password or executes a certain PL/SQL procedure.

Read more in Note 13615.1 - Roles and Privileges Administration and Restrictions

Oracle Database Privileges and Roles Configuration and Administration

Managing system privileges

The system privileges can be granted to any user or role with the following command:

grant <privilege name> to <username or role> [WITH ADMIN OPTION];

They can be revoked with the following command:

revoke <privilege name> from <username or role>;

A user who is granted the "GRANT ANY PRIVILEGE" privilege(SYSDBA has this privilege by default) can grant or revoke any system privilege. Furthermore a user who is granted a certain system privilege WITH ADMIN OPTION can grant/revoke it to/from other users or roles. The WITH ADMIN OPTION used while granting a system privilege will make that user the "administrator" of the privilege.

Note: All the users who are granted the system privilege by the privilege administrator(a user who is granted the privilege WITH ADMIN OPTION) will keep it when the privilege is revoked from the privilege administrator.

Note: Starting with 11g the GRANT ANY PRIVILEGE privilege is granted to some default roles like IMP_FULL_DATABASE. Make sure that this role is granted with caution.

The system privileges are very powerful and as such they must be granted with caution. Furthermore since GRANT ANY PRIVILEGE and WITH ADMIN OPTION allow a user to grant system privileges they must be guarded well.

All the system privileges granted to a certain user can be viewed in DBA_SYS_PRIVS:

SQL> select grantee, privilege, admin_option from dba_sys_privs where grantee='<USER>';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
<USER>                          CREATE JOB                               NO
<USER>                          SELECT ANY DICTIONARY                    NO
<USER>                          CREATE ANY PROCEDURE                     YES
<USER>                          CREATE PROCEDURE                         NO
<USER>                          UNLIMITED TABLESPACE                     NO
<USER>                          ALTER SESSION                            NO
<USER>                          CREATE TABLE                             NO
<USER>                          CREATE SESSION                           NO

8 rows selected.

Managing the object privileges

The object privileges can be granted to any user or role with the following command:

grant <privilege name> on <schema>.<object_name> to <username or role> [WITH GRANT OPTION];

They can be revoked with the following command:

revoke <privilege name> on <schema>.<object_name> from <username or role>;

An object privilege can be granted by the object owner, by a user who was granted that object privilege WITH GRANT OPTION or by a user who is granted the "GRANT ANY OBJECT PRIVILEGE" privilege.

Note: When a user who is granted an object privilege WITH GRANT OPTION loses the privilege, all the users who were granted this privilege by this user will lose the privilege as well.

All the object privileges granted to a certain user can be viewed in DBA_TAB_PRIVS:

SQL> select owner, table_name, privilege from dba_tab_privs where grantee='<USER>';

OWNER         TABLE_NAME          PRIVILEGE
-----------   ----------          -----------------
SYS           <TABLE>              FLASHBACK
SYS           <TABLE>              DEBUG
SYS           <TABLE>              QUERY REWRITE
SYS           <TABLE>              ON COMMIT REFRESH
SYS           <TABLE>              REFERENCES
SYS           <TABLE>              UPDATE
SYS           <TABLE>              SELECT
SYS           <TABLE>              INSERT
SYS           <TABLE>              INDEX
SYS           <TABLE>              DELETE
SYS           <TABLE>              ALTER
...

The following note provides a script that will generate the commands needed to recreate all the object privileges granted on the database:

Note 1020176.6 - SCRIPT: Script to Generate object privilege GRANTS

Managing Roles

To create a role, use the CREATE ROLE command:

create role <role name> [IDENTIFIED BY <password>/USING <package>/EXTERNALLY/GLOBALLY ];

To grant privileges to a role use the GRANT command:

grant <object/system privilege> to <role name>;

To revoke privileges from a role run the following statement:

revoke <privilege> from <role name>;

A role can be granted to a user or to another role with the following command:

grant <role> to <username or role> ;

Note : The privileges assigned to a role can be associated with a user session only and cannot be inherited by any objects (views, stored procedures) that are owned by a user who is granted the role. You cannot use the privileges granted via a role while creating a stored PL/SQL object or a view. For this you have to be granted the object privileges directly. Furthermore the object privileges granted through roles cannot be used by the scheduler jobs.

It is important to note that the privileges acquired via roles can be exercised ONLY when running a procedure with invoker's rights but cannot be used when running a procedure with definer's rights. Furthermore one can ONLY run the procedure using the privileges acquired through roles but cannot compile them using those privileges.

For further details see the following notes:

Note 168168.1 - Getting ORA-942 or ORA-1031 and PLS-201 or ORA-28111 in PL/SQL, works in SQL*Plus
Note 11740.1 - Role Restrictions

Roles can be enabled or disabled selectively in a session. To enable a role explicitly in a session you can run one of the following commands:

set role <role name>;

set role all;

set role all except <list of roles>;

The roles can be enabled automatically in a session if they are declared to be the default roles of a user:

alter user <username> default role <role list>;

Not all the roles can be made the default roles of a user. See the following note for details:

Note 745407.1 - What Roles Can Be Set as Default for a User?

Note : In any Oracle version up to 10.2.0.3 + 11.1.0.6 if a role is made a default role of a user it is automatically enabled in the user's session. This is true also for the roles protected by a password and for the secure application roles. This behaviour has been changed starting with 10.2.0.4/10.2.0.5 and 11.1.0.7. After upgrading to 10.2.0.4/ 11.1.0.7(or to a newer version) it is possible that you will get errors like ORA-1031, ORA-942 because your application was using the privileges acquired via these secured roles. If you cannot enable these roles explicitly then remove their protection ( ALTER ROLE <role name> NOT IDENTIFIED; )

When a new session is initiated Oracle will attempt to enable all the default roles of the account if their number will be less than ( (value of the MAX_ENABLED_ROLES parameter) - 2 ). The default value of MAX_ENABLED_ROLES is 150. If the account is granted (directly or recursively) more than 148 roles then the session creation will fail with ORA-28031.

See the following notes which present in detail this subject:

Note 727902.1 - ORA-28031 max enabled roles exceeded during import of Transportable Tablespace Set
Note 778785.1 - What to Check When Dealing With Ora-28031: Maximum Of 148 Enabled Roles Exceeded?
Note 780749.1 - ORA-28031 / ORA-01925: maximum of 148 enabled roles exceeded

Oracle Database Privileges and Roles HOWTOs

System privileges

Note 1545816.1 - Security Checklist: 10 Basic Steps to Make Your Database Secure from Attacks
Note 109891.1 - A User connected AS SYSOPER can only Perform STARTUP and SHUTDOWN
Note 180019.1 - Which System Privileges are required for a User to Perform Backup Operator Tasks
Note 153510.1 - Use SELECT ANY DICTIONARY or SELECT_CATALOG_ROLE or O7_DICTIONARY_ACCESSIBILITY?
Note 1357623.1 - How To Revoke Lock Privilege From A User That Has Select Any Table Privilege
Note 204699.1 - How to revoke ALTER SESSION Privilege
Note 247093.1 - Be Cautious when Revoking Privileges Granted to PUBLIC
Note 266536.1 - What are the SELECT ANY TRANSACTION / FLASHBACK ANY TABLE Privileges ?
Note 312066.1 - Which Dictionary View Store The Privileges Of Object Directory
Note 365418.1 - Prevent Truncate of a Table in Your Own Schema
Note 1025296.6 -HOW TO CREATE A USER THROUGH A STORED PL/SQL PROCEDURE?

Object privileges

Note 107843.1 - Grant Object Privileges on Another Schema Object to Other Users as SYSTEM or SYS
Note 162489.1 - Invokers rights procedure executed by definers rights procedures
Note 130425.1 - How to Know if a Stored Procedure is Defined as AUTHID CURRENT_USER ?
Note 215331.1 - How to Know if a User Has Grants to Execute a Function or a Procedure
Note 104355.1 - How to GRANT privileges on another user's objects as DBA without GRANT option
Note 156303.1 - How to exclude a user from PUBLIC scope
Note 197611.1 - How to avoid a user from dropping his own objects
Note 271288.1 - Granting a SELECT privilege on a view not owned by you results in revoking the SELECT privilege.
Note 414423.1 - Which Privilege to Grant To DESCRIBE Table Schema Definition?

Roles

Note 13615.1     - Roles and Privileges Administration and Restrictions
Note 11740.1     - Role Restrictions
Note 1079975.6 - Enabling, Disabling, and Granting Default Roles
Note 317258.1   - Predefined Roles Evolution from 8i to 10g R2: CONNECT role Change in 10gR2
Note 234551.1   - PUBLIC : Is it a User, a Role, a User Group, a Privilege ?
Note 556692.1   - Users Being Granted The CONNECT Privilege Via Another Role Cannot Connect
Note 39333.1     - Identifying PC Clients in V$SESSION
Note 174138.1   - How to Transfer all Roles and Grants to Another Database
Note 77666.1     - WIN: Granting Database Roles
Note 1011899.6 - Roles and Creating Stored Objects / Views
Note 1022776.6 - How to Make Trace Files Created by Oracle Readable by All Users ?
Note 1068753.6 - How To Isolate a Table To Run Update Without Losing Granted Roles
Note 1071358.6 - What is the OUTLN User?
Note 235690.1   - How To Create A User With '.' (dot) In Name
Note 112523.1   - How to see which Roles are Active within a Session
Note 106698.1   - WINNT: Assigning External Operating System Roles to NT Global Groups
Note 114673.1   - RESOURCE Role in DBA_SYS_PRIVS does not Include UNLIMITED TABLESPACE Privilege
Note 69483.1   - Changing Role within Stored Procedures using dbms_session.set_role
Note 180028.1   - Set up a Secure Access to Application Data within a Database: DBAs, Schemas and Users
Note 203318.1   - How to create a user and grant privileges in a single GRANT statement
Note 207560.1   - Can the 9i Sample Schemas Be Safely Removed?
Note 124121.1   - How to Disable a SQL*Plus Connection for a User
Note 780749.1    - The Jobs Are Failing With ORA-28031
Note 159757.1    - How to Verify the Enabled Roles for a Session Within a Trigger or PL/SQL Routine
Note 1060417.6 - ORACLE_8 ROLES, SELECT_CATALOG ROLE, EXECUTE_CATALOG_ROLE, DELETE_CATALOG_ROLE
Note 260111.1    - How to Interpret the ACCOUNT_STATUS Column in DBA_USERS
Note 745407.1    - What Roles Can Be Set as Default for a User?

Oracle Database Privileges and Roles Troubleshooting

System privileges

Note 205297.1 - ORA-1031 on TRUNCATE TABLE even if granted DELETE ANY TABLE
Note 232513.1 - ORA-01031 During CREATE DATABASE Statement
Note 100714.1 - ORA-01031 When Creating Unique Index Using a Function
Note 265130.1 - ORA-01031: ALTER TABLE AnotherSchema.Table MODIFY column
Note 342489.1 - ORA-01031 SELECT ANY DICTIONARY System Privilege Granted Through Role Does Not Allow View Creation
Note 1005208.6 - Cannot Create or Replace a Stored Object in Another Schema
Note 1049536.6 - ORA-28009 upon sqlplus connect sys/<password>>
Note 1074762.6 - Revoking "CREATE DATABASE LINK" Privilege from User
Note 112211.1 - Privileges Required to Run SQL_TRACE
Note 222860.1 - ORA-00942 When Commit DML Transaction on a Base Table Belonging to a MV
Note 240769.1 - Grant CREATE SECURITY PROFILE Fails with IMP-00017, ORA-00990 during FULL Import
Note 259816.1 - ORA-990 Trying To Revoke RULE, QUEUE Or EVALUATION System Privileges
Note 304139.1 - You Have Insufficient Privileges To Run The Advisor
Note 309809.1 - ORA-1031 : CREATE DATABASE LINK Fails after ALTER SESSION SET CURRENT_SCHEMA

Object privileges

Note 1393228.1 - Granting The Execute Privilege On SYS Owned Packages Fails With ORA-01031 On 11.2.0.3.0

Note 168168.1 - Getting ORA-942 or ORA-1031 and PLS-201 in PL/SQL, works in SQL*Plus
Note 170973.1 - Unable to Revoke Rights from Object Owner
Note 121384.1 - ORA-1927 While Revoking Object Privileges as the Object Owner
Note 1004923.6 - ORA-01031, ORA-02063 on insert via database link
Note 1102124.1 - Scheduler Does Not Support Object Privileges Granted Through Roles
Note 1005146.6 - ORA-942 Even Though User Has Been Granted Privileges on Object
Note 1039161.6 - Cannot grant execute privilege on dbms_pipe
Note 1062335.6 - ORA-942 when select from any v$view within stored PL/SQL procedure
Note 94092.1 - ORA-1031 Trying to Create PK/FK on Another User's Table
Note 116540.1 - Replacing an Existing View loses Granted Permissions on the View
Note 161011.1 - Grant Execute on SYS.SYS_GROUP Fails with ORA-4042
Note 120687.1 - ORA-990 when trying to Grant Privileges to User
Note 235325.1 - ANALYZE 'SYS' Tables by Other Users is not Permitted in Oracle 9i
Note 100076.1 - ORA-942 or ORA-1031 Creating Views Based on Data Dictionary Objects
Note 159051.1 - Describe of Remote Table over Public Database Link and Private Synonym Fails with ORA-4043
Note 159674.1 - Unable to select/update v$session in a Trigger
Note 208234.1 - ORA-1031 While Executing DBMS_SESSION Through Procedure
Note 228831.1 - ORA-01720 When Granting Object Privileges on Own Table Using Object Types
Note 159968.1 - DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION Results in ORA-06550 and PLS-00201
Note 160870.1 - Intermedia Text Index not Being Rebuilt using Dbms_job (and Drjobdml.sql)
Note 238567.1 - ORA-006564 When Creating View On External Table
Note 390225.1 - Execute Privileges Are Reset For Public After Applying Patchset
Note 271587.1 - ORA-1031 CANNOT CREATE A VIEW ON A TABLE GRANTED VIA A ROLE

Roles

Note 1392321.1 - Oracle External OS Roles Are Not Being Granted to Windows User Accounts
Note 151788.1 - Change in Behavior for Password Protected Roles
Note 1005485.6 - ORA-1950 When Creating an Object and Resource Role is Granted to the User
Note 1066067.6 - Roles could not be executed even after they were recreated
Note 1075927.6 - View ROLE_TAB_PRIVS returns zero rows
Note 1084014.6 - Revoking DBA or RESOURCE Privilege Revokes UNLIMITED TABLESPACE from the User
Note 97583.1 - JServerPermission Memory.GC Java exception when calling enableNewspace()
Note 101078.1 - VMS: Using DBLINKS When OPS$ Accounts and Password Files Accounts are Set Up
Note 106140.1 - AFTER LOGON Triggers Don't Allow DBMS_SESSION.SET_ROLE to Keep Roles Enabled
Note 111288.1 - Create a New User, no Grants but the User can Connect
Note 117872.1 - Why ORA-01925 Occurs and How to Resolve It
Note 121633.1 - ORA-24347 with Select * from dba_role_privs OR Select * from user_role_privs
Note 150418.1 - ORA-28201 Not Enough Privileges to Enable Application Role
Note 119752.1 - ORA-942 V$Session V$Parameter C Starting SQL*Plus From Windows NT Client
Note 169289.1 - ORA-01031: insufficient privileges when altering user to identify externally
Note 167421.1 - ORA-18008 Creating Procedure, Trigger, Package or Function
Note 197931.1 - External role details not in ROLE_SYS_PRIVS
Note 370013.1 - Ora-1013 When Creating User, Granting Connect, Resource From A Procedure

Errors

PLS-00201

Note 1018687.6 - PLS-341 - WHEN RUNNING PLSQL PROCEDURE IN SQLPLUS
Note 168168.1 - Getting ORA-942 or ORA-1031 and PLS-201 in PL/SQL, works in SQL*Plus
Note 210377.1 - Executing a Stored Procedure Fails with PLS-00201
Note 113186.1 - PLS-201 GRANTING PRIVILEGES THRU A ROLE
Note 1062535.6 - Possible Reasons for Generating a PLS-201 Error
Note 200415.1 - PLS-00201: Identifier '%s' Must be Declared When Compiling a Procedure
Note 27287.1 - OERR: PLS-201 identifier '%s' must be declared

ORA-01031

Note 1048327.6 - ORA-1031 WHILE EXECUTING A STORED PROCEDURE
Note 1011393.6 - 0RA-01031 IN STORED PROCEDURE WHEN USING DBMS_SQL TO CREATE A VIEW
Note 1079983.6 - ORA-01031 DDL on Materialized View With Enable Query Rewrite Option
Note 1011211.6 - ORA-01031 WHEN EXECUTING 'GRANT CREATE SESSION' STATEMENT
Note 18622.1 - OERR: ORA 1031 "insufficient privileges"

PLS-00904

Note 1014765.6 - PLS-00904 WHEN COMPILING PL/SQL STORED PROCEDURE, FUNCTION, OR DATABASE TRIGGER
Note 27442.1 - OERR: PLS-904 insufficient privilege to access object %s

ORA-00942

Note 1062335.6 - ORA-942 when select from any v$view within stored PL/SQL procedure
Note 100076.1 - ORA-942 or ORA-1031 Creating Views Based on Data Dictionary Objects
Note 1011899.6 - Roles and Creating Stored Objects / Views

Scripts

System privileges

Note 18074.1 - Script To Capture System Privilege Grants
Note 1020286.6 - Script to Create View to Show All User Privs
Note 241997.1 - Script to Create a Procedure to Show All User Privs and Roles

Object privileges

Note 1020176.6 - SCRIPT: Script to Generate object privilege GRANTS
Note 1050267.6 - SCRIPT: Script to show table privileges for users and roles
Note 138232.1 - SCRIPT: How to grant select on dictionary tables only

Roles

Note 18079.1 Script to Capture Role Grants
Note 18080.1 Script to Create Roles
Note 1019486.6 Script: Report Roles Granted to Users
Note 1019508.6 Script to Show System and Object Privs for a User
Note 1020086.6 SCRIPT: To Report Privileges Granted To a User
Note 107182.1 SCRIPT: Generate ROLE Creation Script for 8.X.X
Note 241997.1 SCRIPT: Create procedure to Show All User Privs and roles
Note 98572.1 Script to create user OUTLN in 8i
Note 240478.1 Script to create user OUTLN in 9i

Parameters

System privileges

Note 206795.1 - What is 07_DICTIONARY_ACCESSIBILITY and how Should it be set?
Note 47316.1 - PARAMETER: O7_DICTIONARY_ACCESSIBILITY
Note 68625.1 - PARAMETER: QUERY_REWRITE_INTEGRITY
Note 68624.1 - PARAMETER:QUERY_REWRITE_ENABLED
Note 50010.1 - OERR: ORA-28009 connection to sys should be as sysdba or sysoper

Object privileges

Roles

Note 30797.1 - PARAMETER: INIT.ORA: REMOTE_OS_AUTHENT
Note 30785.1 - PARAMETER: INIT.ORA: OS_AUTHENT_PREFIX
Note 30796.1 - PARAMETER: INIT.ORA: REMOTE_LOGIN_PASSWORDFILE

Oracle Database Privileges and Roles Documentation

Oracle 10g documentation
Oracle 11g documentation

Using My Oracle Support Effectively

Note 166650.1 - Working Effectively With Global Customer Support

REFERENCES

NOTE:13615.1 - Roles and Privileges Administration and Restrictions
NOTE:121633.1 - ORA-24347 with Select * from dba_role_privs OR Select * from user_role_privs
NOTE:124121.1 - How to Disable a SQL*Plus Connection for a User
NOTE:1357623.1 - How To Revoke Lock Privilege From A User That Has Select Any Table Privilege
NOTE:1014765.6 - PLS-00904 WHEN COMPILING PL/SQL STORED PROCEDURE, FUNCTION, OR DATABASE TRIGGER
NOTE:1050267.6 - SCRIPT: Script to show table privileges for users and roles
NOTE:1062335.6 - ORA-942 when select from any v$view within stored PL/SQL procedure
NOTE:1062535.6 - Possible Reasons for Generating a PLS-00201
NOTE:1066067.6 - Roles could not be executed even after they were recreated
NOTE:238567.1 - ORA-006564 When Creating View On External Table
NOTE:167421.1 - ORA-18008 Creating Procedure, Trigger, Package or Function
NOTE:130425.1 - How to Know if a Stored Procedure is Defined as AUTHID CURRENT_USER ?
NOTE:106140.1 - AFTER LOGON Triggers Don't Allow DBMS_SESSION.SET_ROLE to Keep Roles Enabled
NOTE:204699.1 - How to revoke ALTER SESSION Privilege --
NOTE:205297.1 - ORA-1031 On TRUNCATE TABLE Even If Granted DELETE ANY TABLE
NOTE:1075927.6 - View ROLE_TAB_PRIVS returns zero rows

NOTE:234551.1 - PUBLIC : Is it a User, a Role, a User Group, a Privilege ?
NOTE:390225.1 - Execute Privileges Are Reset For Public After Applying Patchset
NOTE:168168.1 - Getting ORA-942 or ORA-1031 and PLS-201 or ORA-28111 in PL/SQL, works in SQL*Plus
NOTE:232513.1 - This document has been replaced with more recent information on this topic. Please refer to more recent documentation.
NOTE:39333.1 - Identifying PC Clients in V$SESSION
NOTE:414423.1 - Which Privilege to Grant To DESCRIBE Table Schema Definition?
NOTE:47316.1 - Init.ora Parameter "O7_DICTIONARY_ACCESSIBILITY" Reference Note
NOTE:1071358.6 - What is the OUTLN User?
NOTE:68624.1 - Init.ora Parameter "QUERY_REWRITE_ENABLED" Reference Note
NOTE:50010.1 - OERR: ORA-28009 "connection as SYS should be as SYSDBA or SYSOPER" (varies by version) Reference Note
NOTE:222860.1 - ORA-00942 When Commit DML Transaction on a Base Table Belonging to a MV
NOTE:235325.1 - ANALYZE 'SYS' Tables by Other Users is not Permitted in Oracle 9i
NOTE:68625.1 - Init.ora Parameter "QUERY_REWRITE_INTEGRITY" Reference Note
NOTE:121384.1 - ORA-1927 While Revoking Object Privileges as the Object Owner
NOTE:94092.1 - ORA-1031 Trying to Create PK/FK on Another User's Table
NOTE:107843.1 - Grant Object Privileges on Another Schema Object to Other Users as SYSTEM or SYS
NOTE:112211.1 - What Privileges are Required to Use the SQL_TRACE Parameter
NOTE:112523.1 - How to see which Roles are Active within a Session
NOTE:1079975.6 - Enabling, Disabling, and Granting Default Roles
NOTE:1102124.1 - Scheduler Does Not Support Object Privileges Granted Through Roles
NOTE:109891.1 - A User Connected AS SYSOPER Can Only Perform STARTUP and SHUTDOWN

NOTE:153510.1 - Use SELECT ANY DICTIONARY or SELECT_CATALOG_ROLE or O7_DICTIONARY_ACCESSIBILITY?
NOTE:156303.1 - Is it possible to exclude a user from PUBLIC scope?
NOTE:159051.1 - Describe of Remote Table over Public Database Link and Private Synonym Fails with ORA-4043
NOTE:159674.1 - Unable to select/update v$session in a Trigger (Oracle 7.3.4)
NOTE:159757.1 - How to Verify the Enabled Roles for a Session Within a Trigger or PL/SQL Routine
NOTE:159968.1 - DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION Results in ORA-06550 and PLS-00201
NOTE:162489.1 - Invokers Rights Procedure Executed by Definers Rights Procedures
NOTE:1068753.6 - How To Isolate a Table To Run Update Without Losing Granted Roles
NOTE:1060417.6 - Orable 8i Roles : SELECT_CATALOG_ROLE, EXECUTE_CATALOG_ROLE, DELETE_CATALOG_ROLE
NOTE:1079983.6 - Create Local Materialized View with Query Rewrite Option Fails with ORA-1031 Insufficient Privileges
NOTE:1084014.6 - Revoking DBA or RESOURCE Roles Revokes UNLIMITED TABLESPACE from the User
NOTE:111288.1 - Create a New User, no Grants but the User can Connect
NOTE:114673.1 - RESOURCE Role In DBA_SYS_PRIVS Does Not Include UNLIMITED TABLESPACE Privilege
NOTE:119752.1 - ORA-942 V$Session V$Parameter C Starting SQL*Plus From Windows NT Client
NOTE:120687.1 - ORA-990 when trying to Grant Privileges to User
NOTE:100076.1 - ORA-942 or ORA-1031 Creating Views Based on Data Dictionary Objects
NOTE:240769.1 - Grant CREATE SECURITY PROFILE Fails with IMP-00017,ORA-00990 during FULL Import
NOTE:241997.1 - Script to Create a Procedure to Show All User Privs and Roles
NOTE:1019486.6 - SCRIPT: Report Roles Granted to Users
NOTE:1545816.1 - Security Checklist: 10 Basic Steps to Make Your Database Secure from Attacks
NOTE:1020086.6 - SCRIPT: To Report Privileges Granted To a User
NOTE:271587.1 - ORA-1031 While Creating A View On A Table On Which The Select Privilege Is Granted Via A Role
NOTE:107182.1 - SCRIPT: Generate ROLE (Re-)Creation Script
NOTE:745407.1 - What Roles Can Be Set as Default for a User?
NOTE:77666.1 - WIN: Granting Database Roles
NOTE:778785.1 - What to Check When Dealing With Ora-28031: Maximum Of 148 Enabled Roles Exceeded?
NOTE:780749.1 - ORA-28031 / ORA-01925: maximum of 148 enabled roles exceeded
NOTE:312066.1 - Which Dictionary View Store The Privileges Of Object Directory
NOTE:317258.1 - Predefined Roles Evolution From 8i to 10g R2: CONNECT Role Change in 10gR2
NOTE:166650.1 - Working Effectively With Oracle Support - Best Practices
NOTE:160870.1 - Intermedia Text Index not Being Rebuilt using Dbms_job (and Drjobdml.sql)
NOTE:259816.1 - ORA-990 Trying To Revoke RULE, QUEUE Or EVALUATION System Privileges
NOTE:342489.1 - ORA-01031 SELECT ANY DICTIONARY System Privilege Granted Through Role Does Not Allow View Creation
NOTE:18074.1 - Script To Capture System Privilege Grants
NOTE:106698.1 - WINNT: Assigning External Operating System Roles to NT Global Groups
NOTE:1019508.6 - Script to Show System and Object Privs for a User
NOTE:247093.1 - Be Cautious When Revoking Privileges Granted to PUBLIC
NOTE:260111.1 - How to Interpret the ACCOUNT_STATUS Column in DBA_USERS
NOTE:1005485.6 - ORA-1950 When Creating an Object and Resource Role is Granted to the User
NOTE:100714.1 - ORA-01031 When Creating Unique Index Using a Function
NOTE:174138.1 - This document has been replaced with more recent information on this topic. Please refer to more recent documentation.
NOTE:101078.1 - This document has been replaced with more recent information on this topic. Please refer to more recent documentation.
NOTE:309809.1 - ORA-1031 : CREATE DATABASE LINK Fails after ALTER SESSION SET CURRENT_SCHEMA
NOTE:151788.1 - Change in Behavior for Password Protected Roles
NOTE:1039161.6 - Cannot grant execute privilege on dbms_pipe
NOTE:1005208.6 - Cannot Create or Replace a Stored Object in Another Schema
NOTE:104355.1 - This document has been replaced with more recent information on this topic. Please refer to more recent documentation.
NOTE:304139.1 - You Have Insufficient Privileges To Run The Advisor.
NOTE:30785.1 - Init.ora Parameter "OS_AUTHENT_PREFIX" Reference Note
NOTE:30796.1 - Init.ora Parameter "REMOTE_LOGIN_PASSWORDFILE" Reference Note
NOTE:30797.1 - Init.ora Parameter "REMOTE_OS_AUTHENT" Reference Note
NOTE:69483.1 - Changing Role within Stored Procedures using dbms_session.set_role
NOTE:138232.1 - SCRIPT: How to grant select on dictionary tables only
NOTE:116540.1 - Replacing an Existing View loses Granted Permissions on the View
NOTE:11740.1 - Role Restrictions
NOTE:117872.1 - ORA-01925: Increase MAX_ENABLED_ROLES or Decrease Default Roles Number
NOTE:266536.1 - What are the SELECT ANY TRANSACTION / FLASHBACK ANY TABLE Privileges ?
NOTE:271288.1 - Granting a SELECT Privilege on a View Not Owned by You Results in Revoking the SELECT Privilege.
NOTE:235690.1 - How To Create A Database User With '.' (dot) In Name
NOTE:208234.1 - ORA-1031 While Executing DBMS_SESSION Through Procedure
NOTE:210377.1 - Compiling a Stored Procedure Fails with PLS-00201
NOTE:200415.1 - This document has been replaced with more recent information on this topic. Please refer to more recent documentation.
NOTE:215331.1 - How to Know if an User Has Grants to Execute a Function or a Procedure
NOTE:1049536.6 - This document has been replaced with more recent information on this topic. Please refer to more recent documentation.
NOTE:1011211.6 - ORA-01031 WHEN EXECUTING 'GRANT CREATE SESSION' STATEMENT
NOTE:1011393.6 - 0RA-01031 IN STORED PROCEDURE WHEN USING DBMS_SQL TO CREATE A VIEW
NOTE:1011899.6 - Roles and Creating Stored Objects / Views
NOTE:1005146.6 - ORA-942 Even Though User Has Been Granted Privileges on Object
NOTE:1004923.6 - ORA-01031, ORA-02063 on insert via database link
NOTE:265130.1 - ORA-01031: ALTER TABLE AnotherSchema.Table MODIFY column
NOTE:727902.1 - ORA-28031 max enabled roles exceeded during import of Transportable Tablespace Set
NOTE:556692.1 - Users Being Granted The CONNECT Privilege Via Another Role Cannot Connect
NOTE:365418.1 - Prevent Truncate of a Table in Your Own Schema
NOTE:228831.1 - ORA-01720 When Granting Object Privileges on Own Table Using Object Types
NOTE:370013.1 - ORA-01031 When Creating User, Granting Connect, Resource From A Procedure
NOTE:1025296.6 - HOW TO CREATE A USER THROUGH A STORED PL/SQL PROCEDURE?
NOTE:18079.1 - Script to Capture Role Grants
NOTE:203318.1 - How to create a user and grant privileges in a single GRANT statement
NOTE:97583.1 - JServerPermission Memory.GC Java exception when calling enableNewspace()
NOTE:98572.1 - This document has been replaced with more recent information on this topic. Please refer to more recent documentation.
NOTE:180028.1 - Set up a Secure Access to Application Data within a Database: DBAs, Schemas and Users
NOTE:1022776.6 - How to Make Trace Files Created by Oracle Readable by All Users ?
NOTE:1018687.6 - Compile a PL/SQL Object Fails With PLS-00341 PLS-00201
NOTE:161011.1 - Grant Execute on SYS.SYS_GROUP Fails with ORA-4042
NOTE:27287.1 - OERR: PLS-201 identifier '%s' must be declared
NOTE:27442.1 - OERR: PLS-904 insufficient privilege to access object %s
NOTE:1392321.1 - Oracle External OS Roles Are Not Being Granted to Windows User Accounts
NOTE:1048327.6 - ORA-1031 WHILE EXECUTING A STORED PROCEDURE
NOTE:240478.1 - Script to create user OUTLN in 9i
NOTE:1020286.6 - Script to Create View to Show All Users' System Privileges
NOTE:199389.1 - How To Request Management Attention on a Service Request (SR) with Oracle Support Services
NOTE:207560.1 - Can the 9i Sample Schemas Be Safely Removed?
NOTE:1020176.6 - SCRIPT: Script to Generate object privilege GRANTS
NOTE:18080.1 - Script to Create Roles
NOTE:18622.1 - OERR: ORA-1031 "insufficient privileges"
NOTE:1393228.1 - Granting The Execute Privilege On SYS Owned Packages Fails With ORA-01031 On 11.2.0.3.0
NOTE:1074762.6 - Revoking "CREATE DATABASE LINK" Privilege from User
NOTE:150418.1 - ORA-28201 Not Enough Privileges to Enable Application Role
NOTE:206795.1 - What is O7_DICTIONARY_ACCESSIBILITY and how should it be set ?
NOTE:197611.1 - How To Stop A User From Dropping It's Own Objects
NOTE:197931.1 - External Role Details Not Listed In ROLE_SYS_PRIVS
NOTE:180019.1 - This document has been replaced with more recent information on this topic. Please refer to more recent documentation.
NOTE:169289.1 - ORA-01031: insufficient privileges when altering user to identify externally.
NOTE:170973.1 - Unable to Revoke Rights from Object Owner

OracleAppsDBAKK1

Monday, December 2, 2019

Master Note For Privileges And Roles (Doc ID 1347470.1)