Oracle - Privileges granted to new schema user
Server Manager grants the following privileges to the users created during the installation of a new schema for the CAST scehmas.
For example, if you create and install a new schema called "CASTKB", a user also called "CASTKB" will be created and granted the Oracle system privileges listed below (note that using the Combined Install option will install three schemas and all three corresponding users will have the same privileges listed below):
| Oracle system privileges granted | With Admin Option |
| CONNECT | No |
| CREATE_TABLE | No |
| CREATE_PROCEDURE | No |
| CREATE_SEQUENCE | No |
| CREATE_SYNONYM | No |
| CREATE_VIEW | No |
| CREATE_TRIGGER | No |
| SELECT_ANY_DICTIONARY | Yes |
The privilege marked in bold is quite powerful and goes beyond "normal" privileges granted to ordinary database users.
How to minimize privileges for the user created during the installation process
The user created during the installation of a new schema is granted the SELECT_ANY_DICTIONARY privilege on Oracle version 10gi and later to be able to query the V$SESSION data dictionary view. CAST uses this view to retrieve the session id of the user's connection for its internal locking mechanism to manage concurrent access to objects in the in the CAST Management Base, CAST Knowledge Base and CAST Central Base.
If you do not want to give the SELECT_ANY_DICTIONARY privilege to the user created during the new schema installation process, you can revoke the privilege by executing the following query (where "CASTKB" is replaced by the name of the schema that hosts the CAST Management Base, CAST Knowledge Base or CAST Central Base):
grant select on sys.v_$session to CASTKB
This must be done after installation of the Knowledge Base (Local) and only the SYS user can grant this privilege.
Reasons for granting these privileges
The first set of privileges...
- CONNECT
- CREATE_TABLE
- CREATE_PROCEDURE
- CREATE_SEQUENCE
- CREATE_SYNONYM
- CREATE_VIEW
- CREATE_TRIGGER
...are the minimum required privileges for a user designated as a schema owner.
For:
- SELECT_ANY_DICTIONARY
These privileges are granted to the owner of the new schema because they allow read access to certain Oracle system tables to:
- identify (read only) detailed server version information and other server properties
- list the current sessions (for user management and CAST locks)
See Also
User privileges and server requirements for server-side installation
