7 Privileges

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.

Table 7-1 System privileges

Privilege	Description
`ADMIN`	Enables a user to perform administrative tasks including checkpointing, backups, migration, and user creation and deletion.
`ALTER ANY CACHE GROUP`	Enables a user to alter any cache group in the database.
`ALTER ANY INDEX`	Enables a user to alter any index in the database. Note: There is no `ALTER INDEX` statement.
`ALTER ANY MATERIALIZED VIEW`	Enables a user to alter any materialized view in the database. Note: There is no `ALTER MATERIALIZED VIEW` statement.
`ALTER ANY PROCEDURE`	Enables a user to alter any PL/SQL procedure, function or package in the database.
`ALTER ANY SEQUENCE`	Enables a user to alter any sequence in the database. Note: There is no `ALTER SEQUENCE` statement.
`ALTER ANY TABLE`	Enables a user to alter any table in the database.
`ALTER ANY VIEW`	Enables a user to alter any view in the database. Note: There is no `ALTER VIEW` statement.
`CACHE_MANAGER`	Enables a user to perform operations related to cache groups.
`CREATE ANY CACHE GROUP`	Enables a user to create a cache group owned by any user in the database.
`CREATE ANY INDEX`	Enables a user to create an index on any table or materialized view in the database.
`CREATE ANY MATERIALIZED VIEW`	Enables a user to create a materialized view owned by any user in the database.
`CREATE ANY PROCEDURE`	Enables a user to create a PL/SQL procedure, function or package owned by any user in the database.
`CREATE ANY SEQUENCE`	Enables a user to create a sequence owned by any user in the database.
`CREATE ANY SYNONYM`	Enables a user to create a private synonym owned by any user in the database.
`CREATE ANY TABLE`	Enables a user to create a table owned by any user in the database.
`CREATE ANY VIEW`	Enables a user to create a view owned by any user in the database.
`CREATE CACHE GROUP`	Enables a user to create a cache group owned by that user.
`CREATE MATERIALIZED VIEW`	Enables a user to create a materialized view owned by that user.
`CREATE PROCEDURE`	Enables a user to create a PL/SQL procedure, function or package owned by that user.
`CREATE PUBLIC SYNONYM`	Enables a user to create a public synonym.
`CREATE SEQUENCE`	Enables a user to create a sequence owned by that user.
`CREATE SESSION`	Enables a user to create a connection to the database.
`CREATE SYNONYM`	Enables a user to create a private synonym.
`CREATE TABLE`	Enables a user to create a table owned by that user.
`CREATE VIEW`	Enables a user to create a view owned by that user.
`DELETE ANY TABLE`	Enables a user to delete from any table in the database.
`DROP ANY CACHE GROUP`	Enables a user to drop any cache group in the database.
`DROP ANY INDEX`	Enables a user to drop any index in the database.
`DROP ANY MATERIALIZED VIEW`	Enables a user to drop any materialized view in the database.
`DROP ANY PROCEDURE`	Enables a user to drop any PL/SQL procedure, function or package in the database.
`DROP ANY SEQUENCE`	Enables a user to drop any sequence in the database.
`DROP ANY SYNONYM`	Enables a user to drop a synonym owned by any user in the database.
`DROP ANY TABLE`	Enables a user to drop any table in the database.
`DROP ANY VIEW`	Enables a user to drop any view in the database.
`DROP PUBLIC SYNONYM`	Enables a user to drop a public synonym.
`EXECUTE ANY PROCEDURE`	Enables a user to execute any PL/SQL procedure, function or package in the database.
`FLUSH ANY CACHE GROUP`	Enables a user to flush any cache group in the database.
`INSERT ANY TABLE`	Enables a user to insert into any table in the database. It also enables the user to insert into any table using the synonym, public or private, to that table.
`LOAD ANY CACHE GROUP`	Enables a user to load any cache group in the database.
`REFRESH ANY CACHE GROUP`	Enables a user to flush any cache group in the database.
`SELECT ANY SEQUENCE`	Enables a user to select from any sequence or synonym on a sequence in the database.
`SELECT ANY TABLE`	Enables a user to select from any table, view, materialized view, or synonym in the database.
`UNLOAD ANY CACHE GROUP`	Enables a user to unload any cache group in the database.
`UPDATE ANY TABLE`	Enables a user to update any table, or synonym in the database.
`XLA`	Enables a user to connect to a database as an XLA reader.

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.

Table 7-2 Object privileges

Privilege	Object type	Description
`DELETE`	Table	Enables a user to delete from a table.
`EXECUTE`	PL/SQL package, procedure or function	Enables a user to execute a PL/SQL package, procedure or function directly.
`FLUSH`	Cache group	Enables a user to flush a cache group.
`INDEX`	Table or materialized view	Enables a user to create an index on a table or materialized view.
`INSERT`	Table or synonym	Enables a user to insert into a table or into the table through a synonym.
`LOAD`	Cache group	Enables a user to load a cache group
`REFERENCES`	Table or materialized view	Enables a user to create a foreign key dependency on a table or materialized view. The `REFERENCES` privilege `on` a parent table implicitly grants `SELECT` privilege on the parent table.
`REFRESH`	Cache group	Enables a user to refresh a cache group
`SELECT`	Table, sequence, view, materialized view, or synonym	Enables a user to select from a table, sequence, view, materialized view, or synonym. The `SELECT` privilege enables a user to perform all operations on a sequence. A user can be granted the `SELECT` privilege on a synonym or a view without being explicitly granted the `SELECT` privilege on the originating table.
`UNLOAD`	Cache group	Enables a user to unload a cache group
`UPDATE`	Table	Enables a user to update a table

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. Table 7-3 shows the privilege hierarchy.

Table 7-3 Privilege hierarchy

Privilege	Confers these privileges
`ADMIN`	All other privileges including `CACHE_MANAGER`
`CREATE ANY INDEX`	`INDEX ON` (any table or materialized view)
`CREATE ANY MATERIALIZED VIEW`	`CREATE MATERIALIZED VIEW`
`CREATE ANY PROCEDURE`	`CREATE PROCEDURE`
`CREATE ANY SEQUENCE`	`CREATE SEQUENCE`
`CREATE ANY SYNONYM`	`CREATE SYNONYM`
`CREATE ANY TABLE`	`CREATE TABLE`
`CREATE ANY VIEW`	`CREATE VIEW`
`DELETE ANY TABLE`	`DELETE` (any table)
`EXECUTE ANY PROCEDURE`	`EXECUTE` (any procedure)
`INSERT ANY TABLE`	`INSERT` (any table)
`SELECT ANY SEQUENCE`	`SELECT` (any sequence)
`SELECT ANY TABLE`	`SELECT` (any table, view or materialized view)
`UPDATE ANY TABLE`	`UPDATE` (any table)

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. The built-in procedures and utilities for these operations are documented in Oracle TimesTen In-Memory Database Reference.

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';