As a database administrator, you can create roles to grant all privileges to a secure application role required to run a database application. You can then grant the secure application role to other roles or users. An application can have various roles, each granted a different set of privileges that allow the user access more or less data while using the application. For example, you can create a role with a password to prevent unauthorized use of the privileges granted to the role. An application can be designed in such a way so that when it starts, it enables the proper role. As a result, an application user does not need to know the password for an application's role.
Depending on what is granted or revoked, a grant or revoke takes effect at different times, such as:
All grants and revokes for system and object privileges to users, roles, and PUBLIC grants take immediate effect.
All grants and revokes of roles to users, other roles, and PUBLIC take effect only when a current user session issues a SET ROLE statement to re-enable the role after the grant and revoke, or when a new user session is created after the grant or revoke.
You can see which roles are currently enabled by examining the SESSION_ROLES data dictionary view.
In Oracle Identity Manager, there are prerequisite grants that are provided to Oracle Identity Manager schema to create necessary objects before installing Oracle Identity Manager. Some of these grants can be revoked later on after installing the Oracle Identity Manager and can be granted to particular users in future as required by the application.
Table E-1 describes the grants required for database applications.
Table E-1 Role Grants for Database Applications
| Role Name | Description | Oracle Identity Manager Usage | Can this Role/Grants be Removed Safely After Installation? | If Revoked |
|---|---|---|---|---|
|
CREATE TABLE |
Enables a user to create, modify, and delete tables in the user's schema. |
Although this is part of grant resource, this is explicitly required because the grant resource does not allow to create a table through a procedure. |
Conditional |
User will not be able to create any new tables programmatically. You can revoke this grant when the Oracle Identity Manager deployment is stable, which means all the components and connectors are imported and working as expected. This is because each connector creates its own schema object. This grant is needed for initial run of any archival utility because the archival utilities create tables programmatically. |
|
CONNECT |
Provides the create session privileges |
To create sessions for users |
Conditional |
This can be replaced with create session after installation. You can do this when the Oracle Identity Manager deployment is stable, which means all the components and connectors are imported and working as expected. This is because each connector creates its own schema object. |
|
RESOURCE |
Enables a user to create, modify, and delete certain types of schema objects in the schema associated with that user. Grant this role only to developers and to other users that must create schema objects. This role grants a subset of the create object system privileges. For example, it grants the CREATE TABLE system privilege, but does not grant the CREATE VIEW system privilege. It grants the following privileges:
In addition, this role grants the UNLIMITED TABLESPACE system privilege, which effectively assigns a space usage quota of UNLIMITED on all tablespaces in which the user creates schema objects. |
To create sequences, indexes, procedures, triggers, and packages |
Conditional |
User will not be able to create any database objects. Only SYS user will be able to do so. You can revoke this grant when the Oracle Identity Manager deployment is stable, which means all the components and connectors are imported and working as expected. This is because each connector creates its own schema object. Specify the quota for tablespaces correctly. |
|
CREATE VIEW |
Enables a user to create, modify, and delete views in the user's schema |
To create SDP_VISIBLE_V, SDP_REQUIRED_V, SDP_LOOKUPCODE_V, and SDP_RECURSIVE_V views in Oracle Identity Manager |
Yes |
The user will not be able to create any views. Only SYS user will be able to do so. |
|
DBMS_SHARED_POOL |
Fits a database object in a shared pool memory |
Used for pinning all the procedures and functions used in Oracle Identity Manager in shared memory. Oracle Identity Manager pinned sequences, function/procedures into memory. Oracle Identity Manager also pinned USR table into memory if Oracle Identity Manager has less than 50000 users in the USR table. |
Conditional |
It can be revoked after installation but may impact performance because some of the procedures and functions may not be pinned explicitly. The pin_obj procedure is created only for Oracle Identity Manager. It is used to explicitly pin database objects into shared memory. Before revoking this role, make sure that the database-level trigger cache_seq is dropped, if already created. |
|
SYS.DBMS_SYSTEM |
Enables an XA Resource Manager and sets privileges so that the XA Resource Manager can manage the interaction between the Oracle database and the applications. Note: Each database connection is enlisted with the transaction manager as a transactional resource. The transaction manager obtains an XA Resource for each connection participating in a global transaction. The transaction manager uses the start method to associate the global transaction with the resource, and it uses the end method to disassociate the transaction from the resource. The resource manager associates the global transaction to all work performed on its data between the start and end method invocations. |
For XA resource and database transactions |
Yes |
On Oracle Database version 10.2.0.4 onwards, it can be removed safely. Oracle has redeemed themselves by moving the DIST_TXN_SYNC procedure to a new package called DBMS_XA that is available to the public. Therefore, XA clients do not require execute privilege on DBMS_SYSTEM for later oracle versions. |
|
SYS.DBMS_FLASHBACK |
Enables self-service repair. If you accidentally delete rows from a table, then you can recover the deleted rows. |
For any failure during reconciliation, you can roll back the changes by using this. |
No |
This is required for the reconciliation engine in Oracle Identity Manager for error handling. |
|
CREATE_MATERIALIZED_VIEW |
Creates a materialized view in the grantee's schema |
To create the OIM_RECON_CHANGES_BY_RES_MV materialized view |
Yes |
User will not be able to create any materialized view. Only SYS user will be able to do so. This materialized view is required for reporting purpose only. |
|
SELECT ON V$XATRANS SELECT ON PENDING_TRANS$ SELECT ON DBA_2PC_PENDING SELECT ON DBA_PENDING_TRANSACTIONS |
Enables an XA Resource Manager and sets privileges so that the XA Resource Manager can manage the interaction between the Oracle database and the applications. |
NA |
No |
Not recommended to remove. Required for XA support. |
|
ADMINISTER DATABASE TRIGGER |
Allows the creation of database-level triggers. |
To create DDL trigger named ddl_trigger in Oracle Identity Manager |
Yes |
Users will not be able to create new DDL triggers. It can be removed after schema creation. |
|
CREATE SEQUENCE |
Allows to Create sequences in the grantee's schema. |
To create sequences |
Conditional |
Not recommended. User will not be able to create any sequence in the Oracle Identity Manager schema. Only SYS user will be able to do so. Note: You can revoke this grant when the Oracle Identity Manager deployment is stable, which means all the components and connectors are imported and working as expected. This is because each connector creates its own schema object which includes sequences also. |
|
CREATE SYNONYM |
Allows to Create synonyms in the grantee's schema. |
To create the following synonyms in Oracle Identity Manager schema:
|
Yes |
The user will not be able to create any synonym. Only SYS user will be able to do so. |
|
CTXAPP |
Before you can create Oracle Text indexes and use Oracle Text PL/SQL packages, you must grant with the CTXAPP role to the grantee's schema. |
To create Oracle Text indexes and Oracle Text PL/SQL in Oracle Identity Manager schema. |
No |
Not recommended. Oracle Text feature will not work. |
|
EXECUTE ON CTXSYS.CTX_ADM EXECUTE ON CTXSYS.CTX_CLS EXECUTE ON CTXSYS.CTX_DDL EXECUTE ON CTXSYS.CTX_DOC EXECUTE ON CTXSYS.CTX_OUTPUT EXECUTE ON CTXSYS.CTX_QUERY EXECUTE ON CTXSYS.CTX_REPORT EXECUTE ON CTXSYS.CTX_THES EXECUTE ON CTXSYS.CTX_ULEXER |
Oracle Text includes several packages that let you perform actions ranging from synchronizing an Oracle Text index to highlighting documents. |
Oracle Identity Manager is directly consuming CTXSYS.CTX_DDL. Other may consumed indirectly. |
No |
Not recommended. Optimization jobs for catalog will start failing. |
|
CREATE JOB |
It grants the Create Job privileges to the grantee schema. |
To create jobs in Oracle Identity Manager. |
Yes |
Users will not be able to create new jobs. It can be removed after schema creation. |
|
EXECUTE ON DBMS_SCHEDULER |
The DBMS_SCHEDULER package provides a collection of scheduling functions and procedures that can be called from any PL/SQL program. |
To schedule the following Jobs in Oracle Identity Manager: PURGE FAST_OPTIMIZE_CAT_TAGS REBUILD_OPTIMIZE_CAT_TAGS PURGE_ADF_BC_TXN_TABLE |
No |
Once revoked, jobs will start failing. |
|
UTL_FILE |
UTL_FILE is not used by Oracle Identity Manager. |
NA |
NA |
NA |
|
Database scheduling using CONTROL-M |
In Oracle Identity Manager, Quartz scheduler is used for application side queuing and DBMS_SCHEDULER_JOB for database jobs scheduling. |
CONTROL-M is not recommended/supported by Oracle Identity Manager. |
No |
NA |
|
Advance Queuing Option |
Advanced QUEUE feature is used by SOA. |
Used by SOA. |
No |
NA |
|
CREATE ANY INDEX |
Used by OPSS. |
Used by OPSS. |
No |
NA |