4 Managing Access Control

System privileges

A system privilege enables a user the ability to perform system-level activities across multiple objects in the database. It confers the right to perform a particular operation in the database or to perform an operation on a type of object. For example, the privilege to create or modify an object in another user's schema in the database requires a system privilege to be granted to the user.

Only the instance administrator or a user with the ADMIN privilege can grant a system privilege to a user. The instance administrator always has full system and object privileges, which cannot be revoked at any time.

Some of the system privileges include ADMIN, SELECT ANY TABLE, CREATE SESSION and CREATE ANY SEQUENCE. For more details on granting or revoking system privileges, see "Granting or revoking system privileges".

Object privileges

An object privilege enables a user to perform defined operations on a specific object. Separate object privileges are available for each object type.

Every object owner has access and full privileges to their own objects. A user does not have access to objects owned by other users unless explicitly granted access by the object's owner or by a user with ADMIN privilege. If the PUBLIC role has been granted access to a given object, then all database users have access to that object. A user with ADMIN privileges cannot revoke an owner's privileges on the owner's object.

Object access control requires that a user either be the owner of an object or granted the appropriate object privilege to perform operations on the object. Object privileges are granted or revoked by the instance administrator, a user with the ADMIN privilege or the user who is the owner of the object.

For more details on granting or revoking object privileges, see "Granting or revoking object privileges".

PUBLIC role

A role called PUBLIC is automatically created in each TimesTen database. By default, TimesTen grants specific privileges to this role. Every user created within the TimesTen database are granted each privilege that is granted to the PUBLIC role. That is, when the instance administrator or a user with the ADMIN privilege creates a user, the privileges associated with the PUBLIC role are granted to each of these users. Each subsequent privilege that is granted to the PUBLIC role is also automatically granted to all users simultaneously. A user with the ADMIN privilege can add or remove default privileges for all users by granting or revoking privileges from the PUBLIC role. When the user revokes a privilege from PUBLIC, it is revoked from each user, except for those users who have this privilege granted to them explicitly.

SELECT * FROM DBA_TAB_PRIVS WHERE GRANTOR = 'SYS'

In the following example, user Pat is granted the SELECT ANY TABLE privilege and PUBLIC is granted the SELECT ANY TABLE privilege. Then, all system privileges are displayed from the SYS.DBA_SYS_PRIVS view. For more information on this view, see "Viewing user privileges". Revoking SELECT ANY TABLE from PUBLIC does not remove SELECT ANY TABLE from Pat, which is shown again through the SYS.DBA_SYS_PRIVS view.

Command> GRANT SELECT ANY TABLE TO PAT;
Command> GRANT SELECT ANY TABLE TO PUBLIC;
Command> SELECT * FROM SYS.DBA_SYS_PRIVS;
< SYS, ADMIN, NO >
< PUBLIC, SELECT ANY TABLE, NO >
< SYSTEM, ADMIN, NO >
< PAT, ADMIN, NO >
< PAT, SELECT ANY TABLE, NO >
5 rows found.
Command> REVOKE SELECT ANY TABLE FROM PUBLIC;
Command> select * from sys.dba_sys_privs;
< SYS, ADMIN, NO >
< SYSTEM, ADMIN, NO >
< PAT, ADMIN, NO >
< PAT, SELECT ANY TABLE, NO >
4 rows found.

If you must, you may create a database that grants the ADMIN privilege to PUBLIC. This grants the ADMIN privilege to all users who will then have unrestricted access to all database objects and be able to perform administrative tasks except for tasks that must be performed by the instance administrator. This is never recommended as a long-term approach, since it results in an insecure database. See "Database Upgrades" in the Oracle TimesTen In-Memory Database Installation Guide for full details on when and for what purposes to use this approach.

The PUBLIC role also grants access to certain objects, system tables and views. By default, in a newly created TimesTen database, 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 privileges granted to PUBLIC, and subsequently all users, by querying the SYS.DBA_TAB_PRIVS view. In the following query, the privilege granted to PUBLIC is in the fifth column.

Command> DESC SYS.DBA_TAB_PRIVS;
View SYS.DBA_TAB_PRIVS:
  Columns:
    GRANTEE                         VARCHAR2 (30) INLINE
    OWNER                           VARCHAR2 (30) INLINE
    TABLE_NAME                      VARCHAR2 (30) INLINE
    GRANTOR                         VARCHAR2 (30) INLINE
    PRIVILEGE                       VARCHAR2 (40) INLINE NOT NULL
    GRANTABLE                       VARCHAR2 (3) INLINE NOT NULL
    HIERARCHY                       VARCHAR2 (3) INLINE NOT NULL
1 view found.

Command> SELECT * FROM SYS.DBA_TAB_PRIVS WHERE GRANTEE='PUBLIC';
< PUBLIC, SYS, TABLES, SYS, SELECT, NO, NO >
< PUBLIC, SYS, COLUMNS, SYS, SELECT, NO, NO >
< PUBLIC, SYS, INDEXES, SYS, SELECT, NO, NO >
< PUBLIC, SYS, USER_COL_PRIVS, SYS, SELECT, NO, NO >
< PUBLIC, SYS, PUBLIC_DEPENDENCY, SYS, SELECT, NO, NO >
< PUBLIC, SYS, USER_OBJECT_SIZE, SYS, SELECT, NO, NO >
< PUBLIC, SYS, STANDARD, SYS, EXECUTE, NO, NO >
< PUBLIC, SYS, UTL_IDENT, SYS, EXECUTE, NO, NO >
< PUBLIC, SYS, TT_DB_VERSION, SYS, EXECUTE, NO, NO >
< PUBLIC, SYS, PLITBLM, SYS, EXECUTE, NO, NO >
< PUBLIC, SYS, DBMS_OUTPUT, SYS, EXECUTE, NO, NO >
< PUBLIC, SYS, DBMS_SQL, SYS, EXECUTE, NO, NO >
< PUBLIC, SYS, DBMS_STANDARD, SYS, EXECUTE, NO, NO >
< PUBLIC, SYS, DBMS_PREPROCESSOR, SYS, EXECUTE, NO, NO >
< PUBLIC, SYS, UTL_RAW, SYS, EXECUTE, NO, NO >
< PUBLIC, SYS, DBMS_UTILITY, SYS, EXECUTE, NO, NO >
< PUBLIC, SYS, DBMS_RANDOM, SYS, EXECUTE, NO, NO >
...
57 rows found.

Privilege hierarchy rules

There is a hierarchy for all of the privileges. The higher level privileges confer related lower level privileges. For example, the ADMIN privilege confers all privileges. The SELECT ANY TABLE privilege confers the SELECT privilege on any individual table.

Whenever a user needs a privilege for an operation, you can verify if the user already has the privilege if either the user is the owner of the object or has a higher level privilege that confers the necessary privileges for that operation. For example, if the user Pat needs to have the SELECT privilege for Terry.Table2, you can check the following:

• Is Pat the owner of the object? If so, owners have all object privileges on their objects

• Has Pat been granted the SELECT ANY TABLE privilege? This privilege means Pat would have SELECT ON any table, view, materialized view.

• Has Pat been granted the ADMIN privilege, which would mean that Pat can perform any valid SQL operation.

If you grant a privilege that is included in a higher level privilege, no error occurs. However, when you revoke privileges, they must be revoked in the same unit as granted. The following sequence of grant and revoke statements for user PAT grants the ability to update any table as well as an update privilege on a specific table:

GRANT UPDATE ANY TABLE TO PAT;
GRANT UPDATE ON HR.employees TO PAT;
REVOKE UPDATE ON HR.employees FROM PAT;
 

The UPDATE ANY TABLE privilege grants the ability to update any table in the database. The second grant is specific for UPDATE privilege to the HR.employees table. The second grant is unnecessary as the UPDATE ANY TABLE provides access to all tables, including employees, but it does not result in an error. You can revoke the second grant, but it will not affect the first grant of the UPDATE ANY TABLE system privilege. Thus, Pat can still update the HR.employees table.

You must revoke in the same unit as was granted. The following example grants the UPDATE ANY TABLE system privilege to Pat. A user tries to revoke the ability to update the HR.employees table from the user. But, the UPDATE ANY TABLE privilege is a system privilege and the UPDATE privilege is an object privilege. The execution of the REVOKE statement for a unit that was not granted fails with an error.

GRANT UPDATE ANY TABLE TO PAT;
REVOKE UPDATE ON HR.employees FROM PAT;
15143: REVOKE failed: User PAT does not have object privilege UPDATE on HR.EMPLOYEES
The command failed.

The full details of the privilege hierarchy is described in the "Privileges" chapter in the Oracle TimesTen In-Memory Database SQL Reference.

Granting administrator privileges

The ADMIN privilege confers all system and object privileges, which allows these users to perform all administrative tasks and valid database operations. For all objects, a user with the ADMIN privilege can perform create, alter, drop, select, update, insert, or delete operations. In addition, a user with the ADMIN privilege can perform replication tasks, checkpointing, backups, migration, user creation and deletion, and so on. Only a user with the ADMIN privilege can grant or revoke all privileges.

Only a user with the ADMIN privilege may view all system tables and views by default. Only a user with the ADMIN privilege can create, alter or drop replication schemas or active standby pairs. The following views and packages can only be accessed by users with the ADMIN privilege:

• The SYS.DBA_TAB_PRIVS view

• The SYS.DBA_SYS_PRIVS view

• The SYS.UTL_RECOMP package

To grant the ADMIN privilege to the user TERRY, execute the following statement:

GRANT ADMIN TO TERRY;

If you have the ADMIN privilege, then you can grant privileges to other users. For example, a user with the ADMIN privilege can grant the SELECT privilege to TERRY on the departments table owned by Pat, as follows:

GRANT SELECT ON PAT.departments TO TERRY;

Granting ALL PRIVILEGES

The ALL PRIVILEGES grants every system privilege to a user. If you want a user to have most of the system privileges, you can grant ALL PRIVILEGES to a user and then revoke only those system privileges that you do not want them to have. The following example grants all system privileges to user PAT. Then, revokes the ADMIN and DROP ANY TABLE privileges to disallow Pat the ability to perform all administration tasks or to drop any tables.

GRANT ALL PRIVILEGES TO PAT;
REVOKE ADMIN, DROP ANY TABLE FROM PAT;

You may also REVOKE ALL PRIVILEGES that were granted to a user. This removes all system privileges from the user, except what the user inherits from the PUBLIC role, as demonstrated below for user PAT:

REVOKE ALL PRIVILEGES FROM PAT;

Granting privileges to connect to the database

TimesTen databases are accessed through Data Source Names (DSNs). If a user tries to use a DSN that has connection attributes for which they do not have privileges, such as first connection attributes, they receive an error.

For a complete description of first connection attributes, see "Connection Attributes" in the Oracle TimesTen In-Memory Database Reference.

All users must be granted the CREATE SESSION system privilege by a user with the ADMIN privilege in order to connect to the database. The CREATE SESSION system privilege provides the authorization to connect to the database. The following example grants the CREATE SESSION privilege to Pat:

GRANT CREATE SESSION TO PAT;

A user with the ADMIN privilege can grant CREATE SESSION privilege to all users by granting this privilege to the PUBLIC role. This allows all users to connect to the database.

GRANT CREATE SESSION TO PUBLIC;

Granting additional system privileges

In addition to the ADMIN privilege, there are a few system privileges that confer a superset of abilities. The following provides a brief description of these privileges:

• XLA: XLA readers can have global impact on the system. They create extra log volume, and can cause long log holds if they do not advance their bookmarks. You must have the XLA system privilege to connect as an XLA reader.

• CACHE_MANAGER: The CACHE_MANAGER privilege is used for cache group administrator operations. See "Granting or revoking privileges for cache groups" for details.

Enabling users to perform operations on any database object type

When you want to grant or revoke privileges for a user, you can grant or revoke privileges for a single object or for that type of object anywhere in the database.

The system privileges that contain the ANY keyword enable the user to perform the functions on all objects of the same type in the database. These system privileges are CREATE ANY object_type, DROP ANY object_type, ALTER ANY object_type, SELECT ANY object_type, UPDATE ANY TABLE, INSERT ANY TABLE, DELETE ANY TABLE, and EXECUTE ANY PROCEDURE.

The following sections provide more details for the CREATE ANY object_type, DROP ANY object_type, and ALTER ANY object_type system privileges:

• Creating a table, index, view, materialized view, sequence, PL/SQL procedure, PL/SQL function, PL/SQL package or synonym

• Dropping a table, view, materialized view, sequence, procedure, function, package or synonym

• Altering a table, view, materialized view, sequence, procedure, function or package

GRANT CREATE ANY TABLE TO TERRY;

GRANT CREATE TABLE TO TERRY;

Grant all object privileges

You can grant all privileges for an object to a user with the ALL keyword. This essentially grants a user the right to perform any operation on the object.

There are no specific object privileges for DROP or ALTER. These operations cannot be granted for individual objects; instead, granting the appropriate system privilege enables a user other the owner of an object to DROP or ALTER that object.

For example, GRANT ALL ON employees TO PAT grants all privileges for the employees table to user PAT. It is possible to revoke individual privileges after granting all object privileges. For instance, the following is a valid sequence of operations:

GRANT ALL ON HR.employees TO PAT;
REVOKE DELETE ON HR.employees FROM PAT; 

You may also REVOKE ALL object privileges that were granted to a user for the object. This removes all privileges for the object from the user, as demonstrated below for user PAT:

REVOKE ALL ON HR.employees FROM PAT;

Both the object owner and a user with the ADMIN privilege can perform the GRANT ALL and REVOKE ALL statements.

Object privileges for tables

For a user to perform operations on tables that they do not own, they must be granted the appropriate object privilege for that table. This includes privileges for tables within cache groups. The object privileges for tables include SELECT, UPDATE, DELETE, INSERT, INDEX and REFERENCES.

The following object privileges may be appropriate not only for authorization, but also for performance reasons:

• The INDEX privilege enables the user to create an index on the table. Creating an index consumes additional space and impacts the performance of DML on the table. A specific grant for INDEX is required for a user to create an index.

• The REFERENCES privilege enables the user to create a foreign key dependency on the table. Foreign key dependencies impact the performance of DML operations on the parent. For more details on the REFERENCES privilege, see "Object Privileges needed when creating foreign key with REFERENCES clause".

The following example grants the SELECT object privilege for the employees table in the HR schema to the user PAT:

GRANT SELECT ON HR.employees TO PAT;

The next example shows an example of how to grant the UPDATE privilege on the employees table owned by the user HR to the user PAT:

GRANT UPDATE ON HR.employees TO PAT; 

Object privileges for views

For a user to create a view, that user must be granted the CREATE VIEW or CREATE ANY VIEW privilege. For a user to select from a view that they do not own, they need to be granted the SELECT object privilege for that view. Furthermore, the view itself needs to be valid; that is, the owner of the view must be granted the SELECT object privilege for all of the objects referenced by the view.

When user PAT creates a view owned by Pat and that view only references objects owned by Pat, then Pat is only required to be granted the CREATE VIEW privilege for this operation. If Pat creates a view owned by Terry that references objects owned by Terry, Pat is required to be granted the CREATE ANY VIEW privilege for this operation. For example:

CREATE VIEW PAT.VIEW1 as select * from PAT.TABLE1;

In this example, if Pat executes this statement, Pat only needs to be granted the CREATE VIEW privilege.

If user Pat creates a view, and the view references a table owned by Terry, then Pat needs to be granted the CREATE VIEW privilege and the SELECT object privilege on all of the objects referenced by the view. The owner of the view, not the view creator, must be granted the SELECT object privilege on the objects referenced by the view. Therefore, in this example, Pat must be granted the SELECT object privilege on TABLE2 that is owned by Terry. Once these privileges are granted, Pat can execute the following:

CREATE VIEW PAT.VIEW2 as select * from TERRY.TABLE2;

However, if a third user, Joe, executes this statement, then Joe must be granted the CREATE ANY VIEW privilege to create the view. Even though Joe is executing the statement, Pat, as the owner of the view, is still required to be granted the SELECT object privilege in order to perform the select on Terry's table.

TimesTen validates all views referenced at execution time. TimesTen will notify which privileges are not in place in order to perform the given operation.

For example:

CREATE VIEW PAT.VIEW2 as select * from TERRY.TABLE2;
CREATE VIEW JOE.VIEW4 as select * from PAT.VIEW2, TERRY.TABLE4;
 

If Pat is executing these statements, the following privileges must be granted:

• CREATE ANY VIEW privilege so that Pat can create the view in Pat's own schema as well as a view in Joe's schema.

• USER Joe must be granted the SELECT object privilege on Terry.Table4.

• USER Joe must be granted the SELECT object privilege on Pat.View2

• USER Pat must be granted the SELECT object privilege on Terry.Table2

When validating all references, TimesTen also validates that PAT.VIEW2 is still valid by verifying that Pat has the SELECT object privilege on TERRY.TABLE2. When you select from a view, TimesTen validates that the view itself is still valid, as well as any views referenced by that view.

Object privileges for sequences

For a user to perform operations on sequences that they do not own, they must be granted the SELECT object privilege. The SELECT privilege on a sequence allows the user to perform all operations on a sequence, including NEXTVAL, even though it ultimately updates the sequence.

For example, to grant SELECT privilege on the employees_seq sequence in the HR schema to the user PAT, issue the following statement:

GRANT SELECT ON HR.employees_seq TO PAT; 

Pat can subsequently generate the next value of the sequence with the following statement:

SELECT HR.employees_seq.NEXTVAL FROM DUAL;
< 207 >
1 row found. 

Object privileges for materialized views

In order to create a materialized view, a user needs the CREATE MATERIALIZED VIEW privilege. If the user is creating a materialized view in some other user's schema, the user needs the CREATE ANY MATERIALIZED VIEW privilege.

The owner of the materialized view needs to have CREATE TABLE privilege as well as SELECT privileges on every detail table in that materialized view. However, the owner of the materialized view is automatically granted the SELECT privilege on the detail tables if previously granted a higher-level system privilege, such as SELECT ANY TABLE or ADMIN.

For a user to select from a materialized view that they do not own, the user needs to be granted the object privileges for materialized views, which include SELECT, INDEX and REFERENCES. For more details on the privileges required, see the appropriate SQL statements in the Oracle TimesTen In-Memory Database SQL Reference.

Object Privileges needed when creating foreign key with REFERENCES clause

The REFERENCES clause in the CREATE or ALTER TABLE statements creates a foreign key dependency from the new child table column (TABLE1.COL1) on the parent table column (TABLE2.PK) as shown in the following operation:

ALTER TABLE PAT.TABLE1 ADD CONSTRAINT FK1
    FOREIGN KEY (COL1) REFERENCES PAT.TABLE2 (PK);

In this example, the user executing the SQL must have ALTER ANY TABLE privilege. Since Pat owns both tables, no additional privileges are needed since Pat owns both tables.

However, if the REFERENCES clause refers to a table not owned by this user, then the REFERENCES object privilege on the table not owned by the user is required before execution is allowed. For example:

ALTER TABLE PAT.TABLE1 ADD CONSTRAINT FK1
    FOREIGN KEY (COL1) REFERENCES TERRY.TABLE2 (PK);

In this example, the user executing this SQL must have ALTER ANY TABLE privilege. As in the previous example, if the user executing this SQL is Pat, the ALTER ANY TABLE privilege is not required because a table's owner can always modify its own table. In addition, the user Pat must be granted the REFERENCES privilege on TERRY.TABLE2 in order for Pat to create a foreign key involving a table owned by Terry.

A user who creates or alters a child table needs the REFERENCES object privilege on the parent table to create a foreign key dependency. The REFERENCES privilege implicitly grants SELECT privileges for a user creating a foreign key on the parent table. However, this implicit grant does not mean that the user has the SELECT privilege on the parent table, so any SELECT statements will fail if the only privilege on the parent table is the REFERENCES privilege.

Object privileges for PL/SQL functions, procedures and packages

For a user to perform operations on PL/SQL functions, PL/SQL procedures or PL/SQL packages that they do not own, they must be granted the EXECUTE object privilege. When you grant a user EXECUTE privilege on a package, this automatically grants EXECUTE privilege on its component procedures and functions.

This privilege grants the right to the following:

• Execute the procedure or function.

• Access any program object declared in the specification of a package.

• Compile the object implicitly during a call to a currently invalid or uncompiled function or procedure.

The EXECUTE privilege does not allow the user to create, drop or alter any procedure, function or package. This requires appropriate system privileges. For example, to explicitly compile using ALTER PROCEDURE or ALTER FUNCTION, the user must be granted the ALTER ANY PROCEDURE system privilege. For details on the system privileges for functions, procedures or packages, see "Enabling users to perform operations on any database object type".

Object privileges for synonyms

For a user to create or drop private or public synonyms, the user must have the following privileges:

In addition, in order to use a synonym, the user must have the appropriate access privileges for the object that the synonym refers to. For example, if you create a synonym for a view, then to select from that view using the synonym, the user would need the SELECT privilege that is necessary to select from a view.

Cache manager privilege

The cache group system privileges provide a user the ability to affect cache group objects across the database. The CACHE_MANAGER system privilege is the administrator privilege for cache groups. If a user has been granted the CACHE_MANAGER privilege, this user may perform any cache group operation. This privilege confers all cache group operation privileges, which are listed in the "Privilege hierarchy" section in the Oracle TimesTen In-Memory Database SQL Reference.

You must have the CACHE_MANAGER privilege to perform the initial load of a read-only cache group or to change the state of autorefresh on a read-only cache group. The initial load implicitly alters the state of the cache group autorefresh from paused to on.

The following grants the CACHE_MANAGER privilege to Pat:

GRANT CACHE_MANAGER TO PAT;

Cache group system privileges

The privileges that the TimesTen users require depend on the types of cache group operations that you want to perform.

• To create a cache group, a user must be granted either the CREATE CACHE GROUP or CREATE ANY CACHE GROUP system privilege. In addition, the user must be granted either the CREATE ANY TABLE or CREATE TABLE privilege to create any underlying cache tables, depending on if the table is owned by the user or not.

• To drop or alter a cache group that is not owned by the user, the user must be granted the DROP ANY CACHE GROUP or ALTER ANY CACHE GROUP privilege as appropriate. In addition, the user must be granted the DROP ANY TABLE privilege to drop any underlying cache tables, if the tables are not owned by the user.

For example, the following confers the privilege for a user to alter any cache group in the database:

GRANT ALTER ANY CACHE GROUP TO PAT;

Other system privileges for cache group operations are for performing the following on objects not owned by the user:

• FLUSH ANY CACHE GROUP: Allows users to flush any cache group in the database.

• LOAD ANY CACHE GROUP: Allows users to load any cache group in the database.

• UNLOAD ANY CACHE GROUP: Allows users to unload any cache group in the database.

• REFRESH ANY CACHE GROUP: Allows users to refresh any cache group in the database.

Cache group object privileges

The object privileges for cache group operations are granted to a user for performing the operation on a single, defined object. The following are the object privileges for cache group objects:

• FLUSH: Allows the user to flush a cache group owned by another user.

• LOAD: Allows the user to load a cache group owned by another user.

• UNLOAD: Allows the user to unload a cache group owned by another user.

• REFRESH: Allows the user to refresh a cache group owned by another user.

For example, the following example grants Pat the cache group object privilege to perform a FLUSH on the cache group CACHEGRP that is owned by Terry:

GRANT FLUSH ON TERRY.CACHEGRP TO PAT;
 

For details on cache group operations, see the Oracle In-Memory Database Cache User's Guide.