Privileges Overview

About Privileges

When there are multiple users who could potentially access database objects, access to these objects is authorized according to the granting of privileges.

Every object has an owner. Object privileges authorize a user to access or 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.

There are also system level privileges to authorize actions such as connecting to the database.

Note:

A user has all privileges on all objects that they own, and these privileges cannot be revoked.

Granting and Revoking Privileges

Use the SQL GRANT statement to grant privileges to allow a user to access a particular object, objects, or types of objects. Use the SQL REVOKE statement to revoke privileges.

You must have administrative privilege to grant or revoke system privileges or to grant or revoke object privileges for an object you do not own.

Examples:

GRANT admin TO terry;
GRANT SELECT ON pat.customers TO terry;
GRANT SELECT ON emp_details_view TO terry;

REVOKE admin, ddl FROM terry;
REVOKE update ON pat.customers FROM terry;

See GRANT and REVOKE in Oracle TimesTen In-Memory Database SQL Reference.

Functionality of Privileges

TimesTen evaluates each user's privileges when a SQL statement is executed.

For example:

Command> SELECT * from pat.table1;

If this statement is executed by pat, then no extra privileges are necessary because pat owns this object. However, before another user, such as terry, executes this statement, they must be granted the SELECT privilege for pat.table1:

Command> GRANT SELECT ON pat.table1 TO terry;

Privileges accomplish the following:

They define what data users, applications, or functions can access or what operations they can perform.
They prevent users from adversely affecting system performance or from consuming excessive system resources. For example, a privilege restricting the creation of indexes is provided not because of an authorization concern, but because it may affect DML performance and occupies space.

Some examples of privileges include authorization to perform the following:

Connect to the database and create a session
Create a table
Select rows from a table
Perform cache group operations

There are two levels of privileges:

System privileges enable system-wide functionality, such as access to all objects. Granting system privileges can enable a user to perform administrator tasks or access objects in other users' schemas. Grant them only to trusted users. See Overview of System Privileges.
Object privileges enable access to a specific database object, such as a particular table or view. See Overview of Object Privileges

A subset of these privileges are automatically granted to each user upon creation through the PUBLIC role. See Overview of the PUBLIC Role.

Privileges are checked when a SQL statement is prepared and the first time it is executed. Subsequent executions of the statement require further privilege checks only if a REVOKE statement has been executed in the database.

Overview of System Privileges

A system privilege authorizes a user to perform system-level activities across the database or perform a specified type of operation for all database objects of a specified type (for example, CREATE ANY TABLE).

Examples of system privileges are ADMIN, SELECT ANY TABLE, CREATE SESSION and CREATE ANY SEQUENCE. See System Privileges.

Only the instance administrator or a user with the ADMIN (administrative) privilege can grant a system privilege to a user.

Note:

A user with ADMIN privileges has a special set of system privileges, as discussed in Administrative Privileges. The instance administrator has an all-encompassing set of system privileges, as covered in Instance Administrator.

Overview of Object Privileges

An object privilege enables a user to perform a specific operation on a specific object. Separate object privileges are available for each object type, such as CREATE TABLE.

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.

Object privileges are granted or revoked by the instance administrator, a user with the ADMIN privilege, or the user who owns the object.

See Privileges for SQL Objects.

Privileges for TimesTen Utilities

Sometimes special privileges are required to run a TimesTen utility.

Any special privilege required to run a TimesTen utility is noted under "Required privilege" in the description of the utility in Utilities or TimesTen Scaleout Utilities in Oracle TimesTen In-Memory Database Reference.

Note:

If any user other than the instance administrator tries to run a utility that requires special privilege when the database is not loaded into memory, they will receive an error because TimesTen cannot determine the privilege of the user.

Overview of the PUBLIC Role

A role called PUBLIC is automatically created in each TimesTen database and given specific privileges, and each user created in a TimesTen database inherits these privileges. Each subsequent privilege that is also granted to the PUBLIC role is also automatically granted to all users simultaneously.

For example, this command results in CREATE SESSION privilege for all users:

Command> GRANT CREATE SESSION TO PUBLIC;

Also see Privileges Through the PUBLIC Role in this document and The PUBLIC Role in Oracle TimesTen In-Memory Database SQL Reference.

Note:

TimesTen does not support any other roles.