Skip Headers
Oracle® TimesTen In-Memory Database Operations Guide
11g Release 2 (11.2.2)

E21633-12
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

4 Managing Access Control

The TimesTen Access Control provides authentication for each user and authorization for all objects in the database. Authentication is provided with the correct user password. Management of authorization for all objects in the database is provided by granting appropriate privileges to specific users.

The following sections describe the TimesTen authentication and authorization:

Managing users to control authentication

For users to access and manipulate data within the database, you must create users and provide appropriate passwords. When you create a user, you should also grant the appropriate privileges for connecting to the database or for access to objects in the database. For more information on granting privileges, see "Providing authorization to objects through privileges".

The following sections describe how to create and manage your users:

Overview of users

There are three types of users in the TimesTen database:

  • Instance administrator: The instance administrator is the user who installed the TimesTen instance. This user has full privileges for everything within the TimesTen instance. For information on creating this user, see "TimesTen Installation" in the Oracle TimesTen In-Memory Database Installation Guide.

    Note:

    In addition to the instance administrator, there are four system users created during the TimesTen install. These system users are used internally by TimesTen as follows: SYSTEM for internal use, SYS for system objects, GRID for cache grid objects and TTREP for replication objects.
  • Internal user: An internal user is created within TimesTen for use within the TimesTen database. An internal user authenticates with a password for a particular database in which it was defined.

    TimesTen user names are case-insensitive, of type TT_CHAR and limited to 30 characters. For details on all user naming conventions, see "Names, Namespace and Parameters" in the Oracle TimesTen In-Memory Database SQL Reference.

    You can create an internal user with the CREATE USER statement, which is described in "CREATE USER" in the Oracle TimesTen In-Memory Database SQL Reference.

  • External user: An external user is created within the operating system. External users are assumed to have been authenticated by the operating system at login time, so there is no stored password within the database. One cannot connect as an external user from a different host from which the TimesTen database is installed. On the same host, we use the operating system credentials of the client to enable the client to connect as that particular external user. For example, if an external user logs into the UNIX system, they can connect to the TimesTen database without specifying a password since they already provided it during the login, as long as the external user has been granted the correct privileges. The external user must also be in the TimesTen users group and have the correct permissions granted to it, as described in "TimesTen instance administrators and users groups" in the Oracle TimesTen In-Memory Database Installation Guide.

    You cannot connect with an external user defined on one host to a TimesTen data source on a remote host. External users can only be used to connect to the local TimesTen data source, because the local operating system authenticates the external user. When connecting over a client/server connection, the external user must be defined on the same host the client and server. Thus, in when using an external user, both the client and the server must be on the same host since the operating system provides the authentication of the user.

    While the external user is created within the operating system, you still need to identify the user to the database as an external user with the IDENTIFIED EXTERNALLY clause of the CREATE USER statement. For details on this SQL statement, see "CREATE USER" in the Oracle TimesTen In-Memory Database SQL Reference.

    UNIX external user names are case sensitive. Windows external user names are not. When connecting from UNIX platforms, TimesTen automatically converts the external user name to upper case, rendering it case insensitive.

    If you do not want to use clear text passwords to log into TimesTen, then use the PWDCrypt attribute to create a hash of the password. The only reason to use this attribute is if the password is used for logging into other entities, such as an Oracle database. The PWDCrypt version of the password can always be used to connect to TimesTen, but you cannot convert it back to the original password in order to connect to Oracle Database.

Note:

Both the instance administrator and all external users must be in the TimesTen users group specified during the install. For more details, see "TimesTen Installation" in the Oracle TimesTen In-Memory Database Installation Guide.

Creating or identifying users to the database

Only the instance administrator or a user with the ADMIN privilege can create the internal user or identify the external user with the CREATE USER statement. For security purposes, you can only create or alter the internal user with the CREATE USER or ALTER USER statements using a direct connection to the TimesTen database. Thus, executing CREATE USER or ALTER USER from a client-server application or through passthrough execution is not allowed. You can use the ALTER USER statement to change a user from an internal to an external user or from an external to an internal user. The full syntax for the CREATE USER statement is detailed in the "SQL Statements" chapter in the Oracle TimesTen In-Memory Database SQL Reference.

Note:

For details on a user with the ADMIN privilege, see "Granting administrator privileges".

To create an internal user, provide the user name and password in the CREATE USER statement. The following example creates the internal user TERRY with the password "secret":

CREATE USER TERRY IDENTIFIED BY "secret";
User created.

To identify an external user, provide the user name in the CREATE USER IDENTIFIED EXTERNALLY statement. The following example identifies the external user PAT to the TimesTen database:

CREATE USER PAT IDENTIFIED EXTERNALLY;
User created.

To change the external user PAT to an internal user, perform the following ALTER USER statement:

ALTER USER PAT IDENTIFIED BY "secret"; 

To change the internal user PAT to an external user, perform the following ALTER USER statement:

ALTER USER PAT IDENTIFIED EXTERNALLY; 

You can see what users have been created by executing a SELECT statement on the following system views:

  • SYS.ALL_USERS lists all users of the database that are visible to the current user.

  • SYS.USER_USERS describes the current user of the database.

  • SYS.DBA_USERS describes all users of the database. To perform a select statement on this view, you must have the appropriate privileges granted.

For example, to see the current user, perform the following:

SELECT * FROM sys.user_users;
< PAT, 4, OPEN, <NULL>, <NULL>, USERS, TEMP, 2009-02-25 12:00:17.027100, <NULL>,
<NULL> >
1 row found.

For more details on these views, see "System Tables" in the Oracle TimesTen In-Memory Database System Tables and Views Reference.

Changing the password of the internal user

Only the internal user has a password that can be modified within the database. A user can alter their own password. A user with the ADMIN privilege can alter the password of any user. These users can change the password with the IDENTIFIED BY clause of the ALTER USER statement.

For example, to change the password for internal user TERRY to "12345" from its current setting, perform the following:

ALTER USER TERRY IDENTIFIED BY "12345";
User altered.

Dropping users from the database

If granted the appropriate privileges, you can use the DROP USER statement to drop users created in the database. You cannot drop the user in the following instances:

  • You cannot drop the instance administrator.

  • You cannot drop a user unless all objects owned by that user have first been deleted.

  • You cannot drop a user if the user is currently connected to the database.

The following DROP USER statement drops the user TERRY from the database:

Command> drop user terry;
User dropped.

The following error occurs if you try to drop the instance administrator:

Command> drop user instadmin;
15103: System-defined users and roles cannot be dropped
The command failed.

The following error occurs if user Pat tries to drop user Terry when Pat does not have the required ADMIN privilege:

Command> drop user terry;
15100: User PAT lacks privilege ADMIN
The command failed.

Note:

Currently, we do not support DROP USER CASCADE.

Providing authorization to objects through 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 following sections describe authorization to objects through the use of privileges:

Privileges overview

TimesTen provides user authorization to objects in the database through privileges. Users must be granted privileges for access to database resources or objects. These privileges restrict what operations users may perform on those objects. A user has all privileges on all objects in their own schema, and these privileges cannot be revoked. A user can be granted privileges for objects in other users' schemas.

TimesTen evaluates each user's privileges when the SQL statement is executed. Each SQL statement can be executed by an arbitrary user. For example:

SELECT * from PAT.TABLE1;

If this statement is executed by Pat, then no extra privileges are necessary because Pat owns this object. However, if another user, such as Terry, executes this statement, then Terry must have been granted the SELECT privilege for PAT.TABLE1.

Privileges provide the following:

  • Define what data users, applications, or functions can access or what operations they can perform.

  • 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 the right to perform the following:

  • Connect to the database and create a session

  • Create a table

  • Select rows from a table that is owned by another user

  • Perform any cache group operation

In addition, a user may need certain privileges in order to perform the following:

  • Call TimesTen built-in procedures.

  • Run TimesTen command-line utilities.

  • Initiate a connection with first connection attributes.

  • Execute SQL statements.

Built-in procedure, utilities, and connection attributes are documented in the Oracle TimesTen In-Memory Database Reference. SQL statements are documented in the Oracle TimesTen In-Memory Database SQL Reference.

There are two levels of privileges:

  • System privileges: These privileges enable system-wide functionality, such as access to all objects. Granting system privileges can enable a user to perform standard administrator tasks or access to objects in other users' schemas. These privileges extend beyond a single object. Restrict them only to trusted users.

  • Object privileges: Each type of object has privileges associated with it.

A subset of these privileges are automatically granted to each user upon creation through the PUBLIC role. Privilege hierarchy rules apply to all privileges granted to a user.

Grant privileges to users so that they can accomplish tasks required for their job. We recommend that you are intentional about who you grant privileges, so that they have only the exact privileges that they need to perform necessary operations.

Privileges are checked at prepare time and when the statement is first executed for each SQL statement. Subsequent executions of that statement require further privilege checks only when a revoke operation is executed in the database.

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.

Note:

The instance administrator can perform all operations. So, any operation that can be performed by a user with ADMIN privileges can also be performed by the instance administrator.

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.

Note:

Some objects, such as cache group and replication objects, require system level privileges before a user can perform certain operations.

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.

Note:

The only exception to this behavior is that any privileges that were granted to PUBLIC by user SYS cannot be revoked. The privileges that were granted as part of database creation are shown when you execute the following SQL statement:
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 then have unrestricted access to all database objects and are 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 "TimesTen Upgrades" in the Oracle TimesTen In-Memory Database Installation Guide for full details on when and for what purposes to use this approach.

Note:

For a full description of the default privileges assigned to the PUBLIC role, see "The PUBLIC role" in the Oracle TimesTen In-Memory Database SQL Reference.

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, or 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 does 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 "Privilege hierarchy" section in the Oracle TimesTen In-Memory Database SQL Reference.

Granting or revoking system privileges

To grant or revoke a system privilege, use the GRANT or REVOKE statements. Only the instance administrator or a user with the ADMIN privilege can grant or revoke system privileges. The GRANT or REVOKE syntax for system privileges includes the system privilege and the user who receives that privilege. Both the syntax for the GRANT and REVOKE statements and the required privileges for executing each SQL statement are described in the "SQL Statements" chapter in the Oracle TimesTen In-Memory Database SQL Reference.

Note:

How to grant and revoke object privileges is described in "Granting or revoking object privileges".

The most powerful system privilege is ADMIN. When you grant a user the ADMIN privilege, you enable this user to perform any operation for any database object.

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. These system views are described in "Viewing user privileges".

The following sections describe some of the system privileges available in TimesTen:

Note:

For a full list of all system privileges, see "Privileges" 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

Note:

For more information on viewing privileges for users from system tables or views, see "Viewing user privileges".

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;

Note:

Since Pat is the owner of departments, Pat may also grant the SELECT object privilege 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.

Note:

To grant or revoke privileges for a single object, use object privileges, which are described in "Granting or revoking object privileges".

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.

Note:

For a full description of these privileges, see "Privileges" in the Oracle TimesTen In-Memory Database SQL Reference. For details on the cache group system privileges that contain the ANY keyword, see "Granting or revoking privileges for cache groups".

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

To create a table, view, materialized view, sequence, PL/SQL procedure, PL/SQL function, PL/SQL package, or synonym within the user's namespace or another user's namespace, you must have the appropriate CREATE object_type or CREATE ANY object_type system privileges.

The following describes the CREATE and CREATE ANY system privileges:

  • The CREATE object_type privilege grants a user the ability to create that object, but only in the user's own schema. After creation, the user owns this object and thus, automatically has been granted all privileges for that object.

    Other privileges are required if a user wants to create cache groups.

  • The CREATE ANY object_type privilege grants a user the ability to create any object of that type in the database, even in another user's schema. The object types include table, index, view, materialized view, sequence, synonym and procedure. The CREATE ANY object_type privileges are CREATE ANY TABLE, CREATE ANY INDEX, CREATE ANY VIEW, CREATE ANY MATERIALIZED VIEW, CREATE ANY SEQUENCE, CREATE ANY SYNONYM and CREATE ANY PROCEDURE.

The following example grants the privilege to create any table in other users' schemas to user TERRY:

GRANT CREATE ANY TABLE TO TERRY;

The following example grants the privilege to create a table within the user's own schema:

GRANT CREATE TABLE TO TERRY;
Dropping a table, view, materialized view, sequence, procedure, function, package or synonym

Grant the DROP ANY object_type system privilege in order for a user to drop an object of object_type that the user does not own. For example, granting Pat this privilege enables Pat to drop the employees table that is owned by the user HR. A user always has the right to drop a table they own. The DROP ANY object_type privilege enables a user to drop any object of the specified type in the database, except for cache groups that require other privileges.

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

ALTER ANY PROCEDURE allows users to alter any procedure, function or package in the database. The ALTER ANY object_type privilege is necessary to modify the properties of objects that the user does not own. For example, if a procedure is created in the HR schema named Proc1 and if Pat is granted the ALTER ANY PROCEDURE privilege, Pat can successfully alter the procedure HR.Proc1.

Granting or revoking object privileges

To grant or revoke an object privilege, use the GRANT or REVOKE statements. The syntax for the object-level GRANT or REVOKE statement requires the name of the object on which the grant or revoke is applied. The syntax for the GRANT and REVOKE statements is described in the "SQL Statements" chapter in the Oracle TimesTen In-Memory Database SQL Reference.

Note:

  • Each SQL statement may require a certain privilege. The required privileges are documented with each statement description in the "SQL Statements" chapter in the Oracle TimesTen In-Memory Database SQL Reference.

  • For a full list of all object privileges, see "Privileges" in the Oracle TimesTen In-Memory Database SQL Reference.

The following sections describe and provide examples on the object privileges for all object types, except for the cache admin objects. The cache object privileges are described in "Granting or revoking privileges for cache groups".

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 notifies 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.

Behavior of Invalid Materialized Views

In order for the materialized view to be valid, the owner of the view must be granted and must keep the SELECT object privilege for all of the detail tables referenced by the materialized view. If the owner of an existing materialized view loses the SELECT privilege on any detail table on which the materialized view is based, the materialized view becomes invalid.

The status of the materialized view is provided in the STATUS column of the SYS.DBA_OBJECTS, SYS.ALL_OBJECTS, and SYS.USER_OBJECTS views. The owner of the materialized view can see the status of its materialized views in the USER_OBJECTS view. Alternatively, execute the ttIsql describe command, which appends INVALID to the materialized view when it becomes invalid.

Note:

If the owner of the materialized view was granted with a higher-level system privilege, such as SELECT ANY TABLE or ADMIN, the owner loses the required SELECT privileges on the detail tables if the higher-level system privilege is revoked. At this point, the materialized view becomes invalid.
  • Users may still select from an invalid asynchronous materialized view without error. However, users receive an error when selecting from an invalid synchronous materialized view.

  • Users that have the privilege to do so can still update the detail tables of the materialized view. However, an invalid materialized view does not reflect these changes. In addition, for asynchronous materialized views, the materialized view log is not updated if no valid materialized views depend on the detail tables.

  • REFRESH on an invalid synchronous materialized view fails with an error.

  • If the owner of the materialized view has been re-granted the privilege that was previously revoked, a REFRESH on an invalid COMPLETE asynchronous materialized view succeeds and the asynchronous materialized view is now valid.

  • In order to fix an invalid materialized view, you must grant the appropriate privileges to the owner of the materialized view and then drop and re-create the materialized view.

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 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:

Table 4-1 Privileges for synonyms

Action Required privilege

Create a private synonym in the user's own schema.

CREATE SYNONYM

Create a private synonym in another user's schema.

CREATE ANY SYNONYM

Create a public synonym.

CREATE PUBLIC SYNONYM

Drop a private synonym in the user's own schema.

No privilege needed.

Drop a private synonym in another user's schema.

DROP ANY SYNONYM

Drop a public synonym.

DROP PUBLIC SYNONYM


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.

Granting or revoking multiple privileges with a single SQL statement

You can grant multiple object privileges in the same GRANT or REVOKE statement for the same database object for one or more users. For example, the following grants Terry the SELECT and UPDATE object privileges on the HR.employees table in the same SQL statement.

GRANT SELECT, UPDATE ON HR.employees TO TERRY;

You can also grant multiple system privileges to one or more users with the same GRANT or REVOKE statement. The following example grants multiple system privileges to both Terry and Pat.

GRANT CREATE ANY TABLE, CREATE SESSION TO TERRY, PAT;

You cannot combine system and object privileges in the same GRANT or REVOKE statement.

Granting or revoking privileges for cache groups

In order for a user to be able to perform activities involving any cache group, the user must have the appropriate cache group privileges. There are system and object privileges for cache groups, where system privileges confer abilities beyond a singular object.

Note:

Passthrough does not require any privileges to be granted, since the privilege checking is performed by the Oracle Database with the user credentials. For details on passthrough, see "Setting a passthrough level" in the Oracle TimesTen Application-Tier Database Cache User's Guide.

The following sections provide an overview of cache group privileges:

For a full list of all system and object privileges for cache group operations, see "Privileges" in the Oracle TimesTen In-Memory Database SQL Reference.

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;

Note:

An asynchronous writethrough (AWT) cache group combines both cache groups and replication. The CACHE_MANAGER privilege provides all of the privileges that you need for creating AWT cache groups.

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.

Note:

All cache group privileges are described in detail in the "Setting Up a Caching Infrastructure" chapter in the Oracle TimesTen Application-Tier Database Cache User's Guide.

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;

Note:

Users with certain privileges must also be created on the Oracle database to own tables and to store cache grid information. The privileges required for the Oracle Database cache administration user and the TimesTen cache manager user for each cache operation are listed in the "Setting Up a Caching Infrastructure" chapter in the Oracle TimesTen Application-Tier Database Cache User's Guide.

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

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

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

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

  • REFRESH ANY CACHE GROUP: Enables 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: Enables the user to flush a cache group owned by another user.

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

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

  • REFRESH: Enables 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 "Cache Group Operations" in the Oracle TimesTen Application-Tier Database Cache User's Guide.

Viewing user privileges

You can view the privileges granted to each user through the following views:

Table 4-2 System privilege views

View name Description

SYS.USER_SYS_PRIVS

Returns all of the system privileges granted to the current user.

SYS.DBA_SYS_PRIVS

Returns the list of system privileges granted to all users and inherited from the PUBLIC role. Requires the ADMIN privilege to select from this view.

SYS.USER_TAB_PRIVS

Returns all of the object privileges granted to the current user.

SYS.ALL_TAB_PRIVS

Returns the results of both USER_TAB_PRIVS and the object privileges inherited from the PUBLIC role for a user. This shows all object privileges granted to a user.

SYS.DBA_TAB_PRIVS

Returns the object privileges granted to all users and inherited from the PUBLIC role. Requires the ADMIN privilege to select from this view.


For example, perform the following to see all of the system privileges granted to all users:

Command> SELECT * FROM SYS.DBA_SYS_PRIVS;
< SYS, ADMIN, YES >
< SYSTEM, ADMIN, YES >
< TERRY, ADMIN, YES >
< TERRY, CREATE ANY TABLE, NO >
< PAT, CACHE_MANAGER, NO >
5 rows found.

Note:

For details on these views, see "System Tables" in the Oracle TimesTen In-Memory Database System Tables and Views Reference.

Privileges needed for utilities, built-in procedures and first connection attributes

Many of the utilities and built-in procedures require a certain privilege in order to execute. In addition, in order to modify or connect with certain first connection attributes, certain privileges are required. First connection attributes are set when a database is first loaded, and only the instance administrator can load a database with first connection attribute settings. The required privilege for each is described with the utility, built-in procedure or first connection attribute description in the Oracle TimesTen In-Memory Database Reference.

Privilege checking rules for parent-child tables

If you have tables related by foreign key constraints, then the following applies:

  • If ON DELETE CASCADE is specified on a foreign-key constraint for a child table, a user can delete rows from the parent table resulting in deletions from the child table without requiring an explicit DELETE privilege on the child table. However, a user must have the DELETE privilege on the parent table for this to occur automatically.

  • When you perform an insert or update on a child table, TimesTen determines if there is a foreign key constraint violation on the parent table resulting from the change to the child table. In this case, a user is required to have the INSERT or UPDATE privilege on the child table, but not a SELECT privilege on the parent table.

  • A user who creates a child table needs the REFERENCES object privilege on the parent table to create a foreign key dependency. See "Object Privileges needed when creating foreign key with REFERENCES clause" for more details.