6 Privileges

The following sections describe privileges that are required to perform TimesTen operations:

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, 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 6-1 System privileges

Privilege Description

ADMIN

Allows a user to perform administrative tasks including checkpointing, backups, migration, and user creation and deletion.

ALTER ANY CACHE GROUP

Allows a user to alter any cache group in the database.

ALTER ANY INDEX

Allows a user to alter any index in the database.

Note: There is no ALTER INDEX statement.

ALTER ANY MATERIALIZED VIEW

Allows a user to alter any materialized view in the database.

Note: There is no ALTER MATERIALIZED VIEW statement.

ALTER ANY PROCEDURE

Allows a user to alter any PL/SQL procedure, function or package in the database.

ALTER ANY SEQUENCE

Allows a user to alter any sequence in the database.

Note: There is no ALTER SEQUENCE statement.

ALTER ANY TABLE

Allows a user to alter any table in the database.

ALTER ANY VIEW

Allows a user to alter any view in the database.

Note: There is no ALTER VIEW statement.

CACHE_MANAGER

Allows a user to perform operations related to cache groups.

CREATE ANY CACHE GROUP

Allows a user to create a cache group owned by any user in the database.

CREATE ANY INDEX

Allows a user to create an index on any table or materialized view in the database.

CREATE ANY MATERIALIZED VIEW

Allows a user to create a materialized view owned by any user in the database.

CREATE ANY PROCEDURE

Allows a user to create a PL/SQL procedure, function or package owned by any user in the database.

CREATE ANY SEQUENCE

Allows a user to create a sequence owned by any user in the database.

CREATE ANY SYNONYM

Allows a user to create a private synonym owned by any user in the database.

CREATE ANY TABLE

Allows a user to create a table owned by any user in the database.

CREATE ANY VIEW

Allows a user to create a view owned by any user in the database.

CREATE CACHE GROUP

Allows a user to create a cache group owned by that user.

CREATE MATERIALIZED VIEW

Allows a user to create a materialized view owned by that user.

CREATE PROCEDURE

Allows a user to create a PL/SQL procedure, function or package owned by that user.

CREATE PUBLIC SYNONYM

Allows a user to create a public synonym.

CREATE SEQUENCE

Allows a user to create a sequence owned by that user.

CREATE SESSION

Allows a user to create a connection to the database.

CREATE SYNONYM

Allows a user to create a private synonym.

CREATE TABLE

Allows a user to create a table owned by that user.

CREATE VIEW

Allows a user to create a view owned by that user.

DELETE ANY TABLE

Allows a user to delete from any table in the database.

DROP ANY CACHE GROUP

Allows a user to drop any cache group in the database.

DROP ANY INDEX

Allows a user to drop any index in the database.

DROP ANY MATERIALIZED VIEW

Allows a user to drop any materialized view in the database.

DROP ANY PROCEDURE

Allows a user to drop any PL/SQL procedure, function or package in the database.

DROP ANY SEQUENCE

Allows a user to drop any sequence in the database.

DROP ANY SYNONYM

Allows a user to drop a synonym owned by any user in the database.

DROP ANY TABLE

Allows a user to drop any table in the database.

DROP ANY VIEW

Allows a user to drop any view in the database.

DROP PUBLIC SYNONYM

Allows a user to drop a public synonym.

EXECUTE ANY PROCEDURE

Allows a user to execute any PL/SQL procedure, function or package in the database.

FLUSH ANY CACHE GROUP

Allows a user to flush any cache group in the database.

INSERT ANY TABLE

Allows a user to insert into any table in the database.

LOAD ANY CACHE GROUP

Allows a user to load any cache group in the database.

REFRESH ANY CACHE GROUP

Allows a user to flush any cache group in the database.

SELECT ANY SEQUENCE

Allows a user to select from any sequence in the database.

SELECT ANY TABLE

Allows a user to select from any table, view or materialized view in the database.

UNLOAD ANY CACHE GROUP

Allows a user to unload any cache group in the database.

UPDATE ANY TABLE

Allows a user to update any table in the database.

XLA

Allows 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, 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 6-2 Object privileges

Privilege Object type Description

DELETE

Table

Allows a user to delete from a table.

EXECUTE

PL/SQL package, procedure or function

Allows a user to execute a PL/SQL package, procedure or function directly.

FLUSH

Cache group

Allows a user to flush a cache group.

INDEX

Table or materialized view

Allows a user to create an index on a table or materialized view.

INSERT

Table

Allows a user to insert into a table.

LOAD

Cache group

Allows a user to load a cache group

REFERENCES

Table or materialized view

Allows 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

Allows a user to refresh a cache group

SELECT

Table, sequence, view or materialized view

Allows a user to select from a table, sequence, view or materialized view.

The SELECT privilege allows a user to perform all operations on a sequence.

A user can be granted the SELECT privilege on a view without having the SELECT privilege on its detail table.

UNLOAD

Cache group

Allows a user to unload a cache group

UPDATE

Table

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

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