When multiple users can access database objects, authorization can be controlled to these objects with privileges. Every object has an owner. Privileges control if a user can modify an object owned by another user. Privileges are granted or revoked either by the instance administrator, a user with the ADMIN privilege or, for privileges to a certain object, by the owner of the object.
The “Providing authorization to objects through privileges” section in the Oracle TimesTen In-Memory Database Operations Guide provides a detailed description of how to grant and revoke privileges for the different objects. In addition, the following sections provide a quick reference on all privileges that are required to perform TimesTen operations:
■ System privileges
■ Object privileges
■ Privilege hierarchy
■ The PUBLIC role
System privileges
A system privilege is the right to perform a particular action or to perform an action on any object of a particular type. Objects include tables, views, materialized views,synonyms, indexes, sequences, cache groups, replication schemes and PL/SQL functions, procedures and packages. Only the instance administrator or a user with ADMIN privilege can grant or revoke system privileges.
Object privileges
An object privilege is the right to perform a particular action on an object or to access another user’s object. Objects include tables, views, materialized views, indexes,synonyms, sequences, cache groups, replication schemes and PL/SQL functions,procedures and packages.
An object’s owner has all object privileges for that object, and those privileges cannot be revoked. The object’s owner can grant object privileges for that object to other database users. A user with ADMIN privilege can grant and revoke object privileges from users who do not own the objects on which the privileges are granted.
Privilege hierarchy
Some privileges confer other privileges. For example, ADMIN privilege confers all other privileges. The CREATE ANY TABLE system privilege confers the CREATE TABLE object privilege.
Cache group privileges have a separate hierarchy except that ADMIN confers the CACHE_MANAGER privilege.
The CACHE_MANAGER privilege confers these privileges:
■ CREATE ANY CACHE GROUP
■ ALTER ANY CACHE GROUP
■ DROP ANY CACHE GROUP
■ FLUSH ANY CACHE GROUP
■ LOAD ANY CACHE GROUP
■ UNLOAD ANY CACHE GROUP
■ REFRESH ANY CACHE GROUP
■ FLUSH (object)
■ LOAD (object)
■ UNLOAD (object)
■ REFRESH (object)
The CACHE_MANAGER privilege also includes the ability to start and stop the cache agent and the replication agent and to perform cache grid operations.CREATE ANY CACHE GROUP confers the CREATE CACHE GROUP privilege for any cache group.
The PUBLIC role
All users of the database have the PUBLIC role. In a newly created TimesTen database,by default PUBLIC has SELECT and EXECUTE privileges on various system tables and views and PL/SQL functions, procedures and packages. You can see the list of objects by using this query:
SELECT table_name, privilege FROM sys.dba_tab_privs WHERE grantee=‘PUBLIC’;
Privileges that are granted to PUBLIC as part of database creation cannot be revoked. To
see a list of these privileges, use this query:
SELECT table_name, privilege FROM sys.dba_tab_privs WHERE grantor=‘SYS’;
