Privileges and Role Authorization – Part 2

Dear Readers,

My name is Franz Devantier, creator of this blog.  I am an Oracle Certified Professional (OCP DBA 11g) Security DBA.  I will be sharing with you the basic duties of an Oracle DBA, and also some of the undocumented, and not so well known tasks. 

Privileges and Role Authorization – Part 2

Managing User Roles:

User Roles

It is easier to maintain and control lists of privileges, when they are grouped together into a role.  A role can then be granted to users or other roles, and carries with it the list of privileges that is defined in it.  The uniqueness of a role is database wide, and not schema wide, as with other database objects such as tables.  Roles are not contained in a schema.  If a user creates a role, and then that use is dropped, the role continues to exist.

Roles are a quick easy and efficient way of granting groups of permissions to users.  You can use the database pre-defined roles, such as the CONNECT role, which now only has the CREATE SESSION privilege in it.  You would probably want to create your own roles, to suit your specific requirements.

·         A role can be granted system or object privileges

·         A role can be granted to any database user

·         For a given user, a granted role can be either enabled or disabled.  Database users and applications can selectively enable and disable roles to provide selective availability of privileges

·         A role can be granted to other roles.  However a role can’t be granted circularly, for example you can’t grant role A to role B, if role B is already granted to role A

·         A role can be granted indirectly to a user, for example if you grant role A to a user, and Role B has been granted to role A, then you have indirectly granted role B to the user.  Enabling and disabling role A, enables and disables Role B as well.  However if the role is password authenticated or a secure application role, then you can’t grant the role indirectly to a user

·         You have the option of making a directly granted role the default role, using the “DEFAULT ROLE” clause of the “ALTER USER” statement.  Remember you can only make roles default roles that have been directly granted to the user.  To find the directly granted roles for a user.

SQL> select grantee, granted_role from dba_role_privs where grantee = 'FRED';

To determine which roles have been granted to other roles;

SQL> select role, granted_role from role_role_privs where role = 'DBA' order by 2;

ROLE                           GRANTED_ROLE

------------------------------ ------------------------------

DBA                            DATAPUMP_EXP_FULL_DATABASE

DBA                            DATAPUMP_IMP_FULL_DATABASE

DBA                            DELETE_CATALOG_ROLE

DBA                            EXECUTE_CATALOG_ROLE

DBA                            EXP_FULL_DATABASE

DBA                            GATHER_SYSTEM_STATISTICS

DBA                            IMP_FULL_DATABASE

DBA                            JAVA_ADMIN

DBA                            JAVA_DEPLOY

DBA                            OLAP_DBA

DBA                            OLAP_XS_ADMIN

DBA                            SCHEDULER_ADMIN

DBA                            SELECT_CATALOG_ROLE

DBA                            WM_ADMIN_ROLE

DBA                            XDBADMIN

DBA                            XDB_SET_INVOKER

16 rows selected.

·         Password authenticated and secure application roles, can only be granted directly to a user.  To set these types of roles: SQL> set role fred_role identified by fred123;

How the properties of roles facilitate easier privilege management in a database.

Property	Explanation
Reduced privilege administration	Rather than granting the same set of privileges explicitly to several users, you can grant the privileges for a group of related users to a role, and then only the role must be granted to each member of the group.
Dynamic privilege management	If the privileges of a group must change, then only the privileges of the role need to be modified. The security domains of all users granted the group's role automatically reflect the changes made to the role.
Selective availability of privileges	You can selectively enable or disable the roles granted to a user. This allows specific control of a user's privileges in any given situation.
Application awareness	The data dictionary records which roles exist, so you can design applications to query the dictionary and automatically enable (or disable) selective roles when a user attempts to execute the application by way of a given user name.
Application-specific security	You can protect role use with a password. Applications can be created specifically to enable a role when supplied the correct password. Users cannot enable the role if they do not know the password.

You can grant a secure application role, all the privileges needed to run an application.  You can then grant this secure application role to other roles or users.  A more complex application can have several different roles, each of these application roles allowing specific data access, for example HR or Finance.  Password protected roles, prevent the roles from being used for security breaches.  Typically an application should be designed, so that when it starts, it determines the functionality required by the current user, and enables the appropriate role.  The Application user would not need to know the password for an application role.  A fairly complex application with diverse functionality like finance, HR, etc; should not have one application role that grants all the privileges for the entire application.  This would be a security risk.

How Roles are Commonly Used:

·         They could be used to manage the privileges of a database application

·         They could be used to manage the privileges for a user group

·         You can grant an application role all the privileges that are needed to run a database application, or create several application roles to cater for the application.

·         You can create a role for a group of database users, with common privilege requirements.

For example collections of application privileges are assigned to one or more application roles.  These application roles are then assigned to user roles in different combinations, so that you end up with a finance role, a manager role, a clerk role etc.  These user roles are then assigned to various end-users.

·         The security domain of a role includes the privileges granted to the role, plus those privileges that are granted to the roles that are granted to that role.

·         The Security domain of a user includes all the schema objects in the corresponding schema, and the privileges granted to that user. Also all the privileges of all the roles that are granted to that user and are currently enabled.  Also the privileges and roles that are granted to the PUBLIC role.

·         Roles can be simultaneously enabled for some users and disabled for other users.

·         Named PL/SQL block executed with Definer’s Rights; All the roles are disabled.

·         Named PL/SQL blocks executed with Invoker’s rights or Anonymous blocks, are executed based on the privileges granted through enabled roles.

Roles affect the usage of DDL statements.  For example to create a table the user may require the “CREATE TABLE” privilege.  The Oracle database restricts the use of certain specific privileges in certain DDL statements.

·         All system and object privileges that permit a user to perform DDL statements are usable when received through a role.  With the exception of the “REFERENCES” object privilege with regards to defining a foreign key for a table.

All system and object privileges that allow a user to perform a DML operation that is required to execute a DDL statement are not usable when received through a role.  The security Domain does not contain roles when a “CREATE VIEW” statement is used.  For example a user who is granted the “SELECT TABLE” privilege on another users table through a role, cannot use this privilege to create a view on that same table that belongs to another user.

The reason for this is because views are definers rights objects.  When creating a view you cannot use any system or object privileges that have been granted through a role.  However if the privilege had been granted directly to you, then you could have used the privilege.  If the privilege is revoked from your user after creating the view, then the view becomes invalid.

For example:

·         The user is granted a role that has the “CREATE VIEW” system privilege

·         The user is granted a role that has the “SELECT TABLE” privilege for the fruit table

·         The user is granted the “SELECT TABLE” privilege for the veggie table

As a result:

·         The user can select data from both the fruit and veggie tables, which are in other users schemas

·         The user cannot create a view on the fruit table, because the “SELECT TABLE” privilege was granted through a view

·         The user can create a view on the veggie table, because the “SELECT TABLE” privilege was granted directly to the user

In UNIX/LINUX environments, you can administer database security using the operating system. The operating system can be used to grant and revoke database roles and to manage their password authentication. This capability is not available on all operating systems.

When you use roles in a distributed environment, there is one other consideration.  You cannot enable a role through a remote procedure.  SO you need to make sure that all the roles at the remote site, are enabled or set as default roles for a distributed or remote session.

Predefined Roles

Oracle ships with a set of pre-defined roles.  These roles are defined when you create the database.  If you install additional options / products, the additional predefined roles are created.  You can grant and revoke privileges and roles from these pre-defined roles, in the same way that you can with any other user defined roles.  It would be a good idea to document the changes you make to the predefined roles.

Predefined Role	Notes
ADM_PARALLEL_EXECUTE_TASK	Provides privileges to update table data in parallel by using the DBMS_PARALLEL_EXECUTE PL/SQL package.
AQ_ADMINISTRATOR_ROLE	Provides privileges to administer Advanced Queuing. Includes ENQUEUE ANY QUEUE, DEQUEUE ANY QUEUE, and MANAGE ANY QUEUE, SELECT privileges on Advanced Queuing tables and EXECUTE privileges on Advanced Queuing packages.
AQ_USER_ROLE	Obsolete, but kept mainly for release 8.0 compatibility. Provides EXECUTE privileges on the DBMS_AQ and DBMS_AQIN packages.
AUTHENTICATEDUSER	Used by the XDB protocols to define any user who has logged in to the system.
CAPI_USER_ROLE	Provides access to packages used for implementing Information Lifecycle Management (ILM) and hierarchical storage and other applications.
CONNECT	Provides the CREATE SESSION system privilege. 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.
CSW_USR_ROLE	Provides user privileges to manage the Catalog Services for the Web (CSW) component of Oracle Spatial.
CTXAPP	Provides privileges to create Oracle Text indexes and index preferences, and to use PL/SQL packages. This role should be granted to Oracle Text users.
CWM_USER	Provides privileges to manage Common Warehouse Metadata (CWM), which is a repository standard used by Oracle data warehousing and decision support.
DATAPUMP_EXP_FULL_DATABASE	Provides privileges to export data from an Oracle database using Oracle Data Pump. Caution: This is a very powerful role because it provides a user access to any data in any schema in the database. Use caution when granting this role to users.
DATAPUMP_IMP_FULL_DATABASE	Provides privileges to import data into an Oracle database using Oracle Data Pump. Caution: This is a very powerful role because it provides a user access to any data in any schema in the database. Use caution when granting this role to users.
DBA	Provides all system privileges that were created with the ADMIN option. 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
DELETE_CATALOG_ROLE	Provides the DELETE privilege on the system audit table (AUD$).
EJBCLIENT	Provides privileges to connect to EJBs from a Java stored procedure.
EXECUTE_CATALOG_ROLE	Provides EXECUTE privileges on objects in the data dictionary.
EXP_FULL_DATABASE	Provides the privileges required to perform full and incremental database exports using the Export utility (later replaced with Oracle Data Pump). It includes these privileges: 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. This role is provided for convenience in using the export and import utilities. Caution: This is a very powerful role because it provides a user access to any data in any schema in the database. Use caution when granting this role to users.
GATHER_SYSTEM_STATISTICS	Provides privileges to update system statistics, which are collected using the DBMS_STATS.GATHER_SYSTEM_STATISTICS procedure
GLOBAL_AQ_USER_ROLE	Provides privileges to establish a connection to an LDAP server, for use with Oracle Streams AQ.
HS_ADMIN_EXECUTE_ROLE	Provides the EXECUTE privilege for users who want to use the Heterogeneous Services (HS) PL/SQL packages.
HS_ADMIN_ROLE	Provides privileges to both use the Heterogeneous Services (HS) PL/SQL packages and query the HS-related data dictionary views.
HS_ADMIN_SELECT_ROLE	Provides privileges to query the Heterogeneous Services data dictionary views.
IMP_FULL_DATABASE	Provides the privileges required to perform full database imports using the Import utility (later replaced with Oracle Data Pump). 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. Caution: This is a very powerful role because it provides a user access to any data in any schema in the database. Use caution when granting this role to users.
JAVADEBUGPRIV	Provides privileges to run the Oracle Database Java applications debugger.
JAVAIDPRIV	Deprecated for this release.
JAVASYSPRIV	Provides major permissions to use Java2, including updating Oracle JVM-protected packages.
JAVAUSERPRIV	Provides limited permissions to use Java2.
JAVA_ADMIN	Provides administrative permissions to update policy tables for Oracle Database Java applications.
JAVA_DEPLOY	Provides privileges to deploy ncomp DLLs into the javavm/admin directory using the ncomp and deployns utilities. Without this role, the javavm/deploy and javavm/admin directories can be accessible.
JMXSERVER	Provides privileges to start and maintain a JMX agent in a database session.
LBAC_DBA	Provides permissions to use the SA_SYSDBA PL/SQL package.
LOGSTDBY_ADMINISTRATOR	Provides administrative privileges to manage the SQL Apply (logical standby database) environment.
MGMT_USER	Grants the SELECT privilege on the different views used for the SYSMAN schema.
OEM_ADVISOR	Provides privileges to create, drop, select (read), load (write), and delete a SQL tuning set through the DBMS_SQLTUNE PL/SQL package, and to access to the Advisor framework using the ADVISOR PL/SQL package.
OEM_MONITOR	Provides privileges needed by the Management Agent component of Oracle Enterprise Manager to monitor and manage the database.
OLAP_DBA	Provides administrative privileges to create dimensional objects in different schemas for Oracle OLAP.
OLAP_USER	Provides application developers privileges to create dimensional objects in their own schemas for Oracle OLAP.
OLAP_XS_ADMIN	Provides privileges to administer security for Oracle OLAP.
ORDADMIN	Provides privileges to administer Oracle Multimedia DICOM.
OWB$CLIENT	Provides privileges to perform standard client-related tasks for Oracle Warehouse Builder, such as creating projects, modules, tables, views, maps, and so on. Warehouse Builder automatically grants this role to all workspace owners and users. (That is, you do not need to explicitly grant it to anyone who must use Warehouse Builder.) For security reasons, the OWB$CLIENT role is not a default role for Warehouse Builder users: Oracle Warehouse Builder enables this role only when it is needed.
OWB_DESIGNCENTER_VIEW	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 Builder administrator can use the ACCESS_PUBLICVIEW_BROWSER system privilege from the Warehouse Builder security level to control a Warehouse Builder user's access to those public views.
OWB_USER	Provides privileges to create and own an Oracle Warehouse Builder workspace. When a workspace owner registers other database users to this workspace, Oracle Database grants this role to these users. Users with this role also have access to Warehouse Builder Control Center public views and other Control Center utilities. Oracle Warehouse Builder grants this role to all Warehouse Builder users.
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.
SNMPAGENT	Used by the Enterprise Manager Management Agent.
SPATIAL_CSW_ADMIN	Provides administrative privileges to manage the Catalog Services for the Web (CSW) component of Oracle Spatial.
SPATIAL_WFS_ADMIN	Provides administrative privileges to manage the Web Feature Service (WFS) component of Oracle Spatial.
WFS_USR_ROLE	Provides user privileges for the Web Feature Service (WFS) component of Oracle Spatial.
WM_ADMIN_ROLE	Provides administrative privileges for Oracle Workspace Manage. This enables users to run any DBMS_WM procedures on all version enabled tables, workspaces, and savepoints regardless of their owner. It also enables the user to modify the system parameters specific to Workspace Manager.
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.

Franz Devantier,

Need a database health check?

devantierf@gmail.com

Income stabilizer (win-win opportunity)

Join the carefully selected and tested cash-flow generating program below to potentially create a long-term residual or annuity type income enhancer for yourself.

Traffic Wave - Free Report:  The report will give you all the information you need to start making a nice long-term residual income stream for yourself.