System Privileges

About System Privileges

Aside from the instance administrator, the most powerful system privilege is ADMIN, which enables the user to perform system operations or operations on any database object. Only the instance administrator or a user with the ADMIN privilege can grant or revoke system privileges to other users.

An individual user can view their own system privileges in the SYS.USER_SYS_PRIVS system view. A user with the ADMIN privilege can view all system privileges for all users in the SYS.DBA_SYS_PRIVS system table. See User Privilege Views.

Instance Administrator

The instance administrator, a member of the TimesTen users group, is the user who creates the TimesTen installation and all TimesTen instances. This user has a number of special privileges and capabilities beyond those of other administrative users.

These are described in the following sections:

Instance Administrator Privileges

There are privileges that only the instance administrator can do.

Remove the TimesTen installation.
Create, modify (including upgrade), or destroy a TimesTen instance.
Create or destroy a database.
Load or unload a database manually (ramPolicy manual using ttAdmin -ramLoad).
Load a database when changes to first connection attribute settings are applied.
Open or close a database.
Restore a database.
Start and stop the TimesTen daemon.
Restart the TimesTen server.

In addition, for TimesTen Scaleout, only the instance administrator can execute any commands of the ttGridAdmin utility. Among many other functions, including those listed above, only the TimesTen Scaleout instance administrator can create a grid, create database definitions and connectables, change the distribution map of an existing database, create repositories, and perform backups, restores, exports, and imports.

See the following for related information:

Instance Administrator, Understanding the TimesTen Users Group, and TimesTen Instances in Oracle TimesTen In-Memory Database Installation, Migration, and Upgrade Guide
TimesTen Scaleout Architecture and The Operating System User in Oracle TimesTen In-Memory Database Scaleout User's Guide

Note:

The instance administrator cannot be the root user.
You cannot change to a different instance administrator.
In TimesTen Scaleout, the instance administrator's user name, user ID, group name, and group ID must all be the same on all hosts of the grid.

Instance Administrator Ownership and Privileges for Database and Log Directories

The instance administrator owns the database directory (indicated by the DataStore connection attribute), where checkpoint files are written, and the log directory (indicated by the LogDir connection attribute).

Proper ownership and permissions must be set for these directories. In addition to the owner being the instance administrator, the group must be the TimesTen users group and the directory permissions must be set for read/write/execute permission for owner and group with no access by anyone else.

Administrative Privileges

The ADMIN privilege confers system privileges and privileges on all database objects, which enables these users to perform administrative tasks and any valid database operation. Only the instance administrator or another user with ADMIN privilege can grant ADMIN privilege.

A user with the ADMIN privilege can do the following:

Perform create, alter, drop, select, update, insert, or delete operations on all database objects.
Grant or revoke all privileges.
Perform checkpointing operations.
Create and delete users.
View system tables, views, and packages.
Create, alter or drop replication schemas or active standby pairs.

Note:

For more information on viewing privileges for users from system tables or views, see User Privilege Views.

To grant the ADMIN privilege to the user terry, the instance administrator or another user with ADMIN privilege executes this statement:

GRANT ADMIN TO terry;

To grant the SELECT privilege to terry on the departments table owned by pat:

GRANT SELECT ON pat.departments TO terry;

Note:

Since pat is the owner of departments, pat may also grant the SELECT object privilege to terry.

Privileges to Connect to the Database

A user must be granted the CREATE SESSION system privilege by the instance administrator or a user with the ADMIN privilege in order to connect to the database.

The following example grants the CREATE SESSION privilege to pat:

Command> GRANT CREATE SESSION TO pat;

Note:

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.

ANY Keyword

Privileges used with the ANY keyword enable the user to perform the operation on any object of the specified 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.

ANY TABLE also includes views and materialized views.

ALL PRIVILEGES

ALL PRIVILEGES, which can be granted by the instance administrator or a user with ADMIN privilege, grants system privileges to a user.

If you want to limit the privileges granted, you can grant ALL PRIVILEGES then revoke those system privileges that you do not want the user to have.

Once granted, ALL PRIVILEGES can subsequently be revoked.

Privilege Hierarchy

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

When a user needs a privilege for an operation, first verify whether the user already has the privilege through a higher level privilege. For example, if the user pat needs to have the SELECT privilege for terry.table2, you can check the following:

Has pat been granted the SELECT ANY TABLE privilege? This privilege means pat would have SELECT on any table, view, or materialized view.
Has pat been granted the ADMIN privilege? This 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 (ANY level or object level).

The following series of statements is allowed, and pat can still update the hr.employees table because of the UPDATE ANY TABLE privilege. (The second statement of course is unnecessary, but the third statement would not be allowed without it.)

Command> GRANT UPDATE ANY TABLE TO pat;
Commanc> GRANT UPDATE ON hr.employees TO pat;
Command> REVOKE UPDATE ON hr.employees FROM pat;

This next example also leaves pat with the ability to update hr.employees, because that was granted explicitly:

Command> GRANT UPDATE ANY TABLE TO pat;
Commanc> GRANT UPDATE ON hr.employees TO pat;
Command> REVOKE UPDATE ANY TABLE FROM pat;

The following example attempts to revoke the ability to update the hr.employees table from the user, but is not allowed because there was no GRANT statement for that specific object.

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

See Privilege Hierarchy in Oracle TimesTen In-Memory Database SQL Reference.

Additional System Privileges

In addition to the ADMIN privilege, some system privileges authorize a range of operations across certain areas of database functionality.

XLA: You must have the XLA system privilege to connect as an XLA reader, who can have global impact on the system. An XLA reader can create extra log volume and can cause long log holds if they do not advance their bookmarks.
CACHE_MANAGER: The CACHE_MANAGER privilege is required for cache group administrator operations. See Privileges for Cache Groups.

Privileges Through the PUBLIC Role

The instance administrator or a user with the ADMIN privilege can grant or revoke default privileges for all users by granting or revoking privileges for the PUBLIC role.

Note:

If a user has been explicitly granted a privilege, it is not revoked if that privilege is revoked from PUBLIC.
Any privileges that were granted to PUBLIC by user SYS cannot be revoked. These privileges, granted as part of database creation, are shown when you execute the following SQL statement:
```
Command> 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. (See User Privilege Views.) As shown, revoking SELECT ANY TABLE from PUBLIC does not revoke SELECT ANY TABLE from pat. (The second column indicates a privilege held by the user. The third column, NO in the example, indicates whether the user can grant that privilege to others.)

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.

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 by querying the SYS.DBA_TAB_PRIVS view. In the query below, the privilege granted to PUBLIC is in the fifth column, as indicated by the DESCRIBE statement that precedes the query.

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.