2 Authorization in TimesTen

One aspect of TimesTen access control is the use of permissions, or privileges, to authorize or limit access to database objects such as tables or views. 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. 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.

This chapter discusses TimesTen features for authorization, covering these topics:

Note:

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

  • For TimesTen SQL statements discussed in this chapter, syntax and required privileges are documented in "SQL Statements" in Oracle TimesTen In-Memory Database SQL Reference.

  • Examples in this chapter use the TimesTen ttIsql utility, indicated by the Command> prompt.

Privileges overview

TimesTen allows access to objects in the database according to authorization through the granting of privileges. These privileges determine what operations users may perform. This section covers these topics:

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 for syntax and usage details.

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. For details on granting or revoking system privileges, 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 privileges".

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.

For details on granting or revoking object privileges, see "Privileges for SQL objects".

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" and "The PUBLIC role" in Oracle TimesTen In-Memory Database SQL Reference.

Note:

TimesTen does not support any other roles.

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. These system views are described in "User privilege views".

The following sections describe system privileges available in TimesTen:

Instance administrator privileges

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.

Only the instance administrator can:

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

Notes:

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

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:

Command> GRANT ADMIN TO terry;

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

Command> 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. For a complete description of first connection attributes, including their required privileges, see "Connection Attributes" in Oracle TimesTen In-Memory Database Reference.

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 legal, 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 legal 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 illegal 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 for additional information.

Additional system privileges

In addition to the ADMIN privilege, the following 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" for details.

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.

Notes:

  • 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. (For more information on this 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.

Overview of privileges to create, alter, or drop objects

This section provides an overview of privileges required to create, alter, or drop database objects.

Privileges to create database objects

To create a database object such as a table, view, materialized view, sequence, PL/SQL procedure, PL/SQL function, PL/SQL package, or synonym, you must have the appropriate CREATE object_type or CREATE ANY object_type privilege.

The following describes the CREATE and CREATE ANY privileges:

  • The CREATE object_type privilege grants a user the ability to create an object of the specified type (such as TABLE), but only in the user's own schema. After creation, the user owns the object and has all privileges for the object.

  • The CREATE ANY object_type privilege grants a user the ability to create any object of that type in any schema of the database. 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.

A user must be granted CREATE TABLE privilege to create a table in their schema, as in this example:

Command> GRANT CREATE TABLE TO terry;

This example grants the privilege to create any table in any schema to user terry:

Command> GRANT CREATE ANY TABLE TO terry;

Notes:

  • See "Object privileges for views" and "Object privileges for materialized views" for additional considerations in creating views and materialized views.

  • When CREATE OR REPLACE results in an object (such as a procedure, function, package, or synonym) being replaced, there is no effect on privileges that any users had previously been granted on that object. This is as opposed to when there is an explicit DROP and then CREATE to re-create an object, in which case all privileges on the object are revoked.

Privileges to alter database objects

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 proc1 is created in the hr schema and pat is granted the ALTER ANY PROCEDURE privilege, pat can alter the procedure hr.proc1.

The ALTER privilege cannot be granted on an individual object. Instead, you must grant the ALTER ANY privilege for the desired object type.

Privileges to drop database objects

The DROP ANY object_type privilege enables a user to drop any object of the specified type in the database and is necessary to drop an object of object_type that the user does not own. For example, granting pat the DROP ANY TABLE privilege enables pat to drop the employees table that is owned by the user hr.

The DROP privilege cannot be granted on an individual object. Instead, you must grant the DROP ANY privilege for the desired object type.

Privileges for SQL objects

User access to database objects is authorized by granting privileges, either for a single object or for that type of object anywhere in the database, through the GRANT statement. Access is removed through the REVOKE statement.

This section covers the following:

Object privileges for tables

For a user to create a table, that user must be granted the CREATE TABLE or CREATE ANY TABLE privilege.

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.

For example:

Command> GRANT SELECT ON hr.employees TO pat;
Command> GRANT UPDATE ON hr.employees TO pat;

The INDEX privilege enables a user to create an index on the table.

The REFERENCES privilege enables use of the REFERENCES clause in the CREATE TABLE or ALTER TABLE statement. This clause creates a foreign key dependency from a child table column (in the following example, table1.col1) to a parent table column (in the example, table2.pk).

Command> ALTER TABLE pat.table1 ADD CONSTRAINT fk1 FOREIGN KEY (col1) 
REFERENCES pat.table2 (pk);

If pat, owner of the tables, executes the statement, no additional privileges are needed. Any other user executing the statement would need ALTER ANY TABLE privilege.

In addition, if the user executing an ALTER TABLE ... REFERENCES statement does not own the table referenced by the REFERENCES clause, then REFERENCES object privilege on the applicable table column is required. For example, for pat to execute this statement:

Command> ALTER TABLE pat.table1 ADD CONSTRAINT fk1 
FOREIGN KEY (col1) REFERENCES terry.table2 (pk);

She would need the following privilege grant:

Command> GRANT REFERENCES (pk) ON terry.table2 TO pat; 

Note that the REFERENCES privilege implicitly grants SELECT privilege for a user creating a foreign key from 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 REFERENCES.

Notes:

If you have tables related by foreign key constraints, these notes apply:
  • 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 whether 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.

Object privileges for views

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 owner of the view must have the SELECT object privilege for all of the objects referenced by the view.

For user pat to create a view that references only objects owned by pat, as in the statement that follows, then pat needs only the CREATE VIEW privilege.

Command> CREATE VIEW pat.view1 AS SELECT * FROM pat.table1;

For pat to create a view that references a table owned by terry, as in the statement that follows, then pat also needs the SELECT object privilege on that table. The owner of a view must be granted the SELECT object privilege on each object referenced by the view.

Command> CREATE VIEW pat.view2 AS SELECT * FROM terry.table2;

For a third user, joe, to execute the preceding statement, he needs the CREATE ANY VIEW privilege. And pat, as the owner of the view, still must have been granted the SELECT object privilege in order to perform the select on the table that terry owns.

When you select from a view, TimesTen validates the view at execution time, as well as any views referenced by that view, for the required underlying privileges.

Now consider the following example:

Command> CREATE VIEW pat.view2 AS SELECT * from terry.table2;
Command> CREATE VIEW joe.view4 AS SELECT * from pat.view2, terry.table4;

For pat to execute these statements, the following privileges must be granted:

  • User pat must be granted the CREATE ANY VIEW privilege so pat can create a view in the schema owned by joe.

  • 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

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 enables the user to perform all operations on that sequence, including NEXTVAL, even though that ultimately updates the sequence.

For example, to grant SELECT privilege on the employees_seq sequence in the hr schema to the user pat:

Command> GRANT SELECT ON hr.employees_seq TO pat; 

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

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

Object privileges for materialized views

To create a materialized view, a user needs at least the CREATE MATERIALIZED VIEW privilege. To create a materialized view in another user's schema, the CREATE ANY MATERIALIZED VIEW privilege is required.

Additionally, the owner of the materialized view needs to have CREATE TABLE privilege as well as SELECT privilege on every detail table in that 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.

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.

Note:

The status of a materialized view is indicated 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 its status in the USER_OBJECTS view.

Also, if a materialized view is invalid, the ttIsql describe output appends INVALID for the materialized view.

Furthermore, regarding materialized views:

  • 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 order to revalidate 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 for synonyms

A synonym is an alias for a database object. Synonyms are often used for security and convenience, because they can be used to mask object names and object owners. In addition, you can use a synonym to simplify SQL statements. Synonyms provide independence by permitting applications to function without modification regardless of which object a synonym refers to. Synonyms can be used in DML statements and some DDL and TimesTen cache statements.

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

Table 2-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 SELECT privilege on the view.

ALL object privileges

You can grant all privileges for an object to a user with the ALL keyword. This grants a user the right to perform any operation on the object. The object owner and any user with the ADMIN privilege can execute the GRANT ALL and REVOKE ALL statements.

For example, GRANT ALL ON hr.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:

Command> GRANT ALL ON hr.employees TO pat;
Command> REVOKE DELETE ON hr.employees FROM pat; 

You may also REVOKE ALL object privileges that were granted to a user for the object, as demonstrated here for user pat:

Command> REVOKE ALL ON hr.employees FROM pat;

Privileges for PL/SQL objects

This section covers the following topics for authorization in PL/SQL:

Privileges for PL/SQL statements and operations

For PL/SQL users, authorization through the granting of privileges is necessary to enable a user to create, alter, drop, or execute PL/SQL procedures and functions, including packages and their member procedures and functions.

You need the CREATE PROCEDURE privilege to create a procedure, function, package definition, or package body if it is being created in your own schema, or CREATE ANY PROCEDURE if it is being created in any other schema. To alter or drop a procedure, function, package definition, or package body, you must be the owner or have the ALTER ANY PROCEDURE privilege or DROP ANY PROCEDURE privilege, respectively.

For a user to execute PL/SQL functions, PL/SQL procedures or PL/SQL packages that they do not own, they must be granted the EXECUTE object privilege for the procedure or function or for the package to which it belong, or granted EXECUTE ANY PROCEDURE. When you grant a user EXECUTE privilege on a package, this automatically grants EXECUTE privilege on its component procedures and functions.

EXECUTE privilege authorizes 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.

To explicitly compile using ALTER PROCEDURE or ALTER FUNCTION, the user must be granted the ALTER ANY PROCEDURE system privilege.

This is all summarized in Table 2-2.

Table 2-2 Privileges for using PL/SQL procedures and functions

Action SQL statement or operation Required Privilege

Create a procedure, function, package definition, or package body.

CREATE [OR REPLACE] PROCEDURE

CREATE [OR REPLACE] FUNCTION

CREATE [OR REPLACE] PACKAGE

CREATE [OR REPLACE] PACKAGE BODY

CREATE PROCEDURE in user's schema

Or:

CREATE ANY PROCEDURE in any other schema

Alter a procedure, function, or package.

ALTER PROCEDURE

ALTER FUNCTION

ALTER PACKAGE

Ownership of the procedure, function, or package

Or:

ALTER ANY PROCEDURE

Drop a procedure, function, package definition, or package body.

DROP PROCEDURE

DROP FUNCTION

DROP PACKAGE

DROP PACKAGE BODY

Ownership of the procedure, function, or package

Or:

DROP ANY PROCEDURE

Execute a procedure or function.

Invoke the procedure or function.

Ownership of the procedure or function, or of the package to which it belongs (if applicable)

Or:

EXECUTE for the procedure or function, or for the package to which it belongs (if applicable)

Or:

EXECUTE ANY PROCEDURE


The following statements grant and then revoke EXECUTE privilege to user2 for a procedure and a package that user1 owns:

Command> grant execute on user1.myproc to user2;
Command> grant execute on user1.mypkg to user2;
...
Command> revoke execute on user1.myproc from user2;
Command> revoke execute on user1.mypkg from user2;

Notes:

  • A user who has been granted privilege to execute a procedure (or function) can execute the procedure even without privileges on other procedures that the procedure calls. For example, consider a stored procedure user2.proc1 that executes procedure user2.proc2. If user1 is granted privilege to execute proc1 but is not granted privilege to execute proc2, the user could not run proc2 directly but could still run proc1.

  • Privilege to execute a procedure or function allows implicit compilation of the procedure or function if it is invalid or not compiled at the time of execution.

  • To invoke a procedure or function through a synonym, a user must have privilege to execute the underlying procedure or function.

  • A SQL statement executed in PL/SQL requires the same privilege as when executed directly.

  • EXECUTE ANY PROCEDURE does not apply to TimesTen supplied packages; however, most are accessible through the PUBLIC role.

Example 2-1 Granting of required privileges

This example shows a series of attempted operations by a user, user1, as follows:

  1. The user attempts each operation before having the necessary privilege. The resulting error is shown.

  2. The instance administrator grants the necessary privilege.

  3. The user successfully performs the operation.

The ttIsql utility is used by user1 to perform (or attempt) the operations and by the instance administrator to grant privileges.

user1:

Initially the user does not have permission to create a procedure. That must be granted even in his or her own schema.

Command> create procedure testproc is
         begin
         dbms_output.put_line('user1.testproc called');
         end;
         /
15100: User USER1 lacks privilege CREATE PROCEDURE
The command failed.

Instance administrator:

Command> grant create procedure to user1;

user1:

Once user1 can create a procedure in the user1 schema, that user owns it and can execute it.

Command> create procedure testproc is
         begin
         dbms_output.put_line('user1.testproc called');
         end;
         /
 
Procedure created.
 
Command> begin
         testproc();
         end;
         /
user1.testproc called
 
PL/SQL procedure successfully completed.
 

The user cannot yet create a procedure in another schema, though.

Command> create procedure user2.testproc is
         begin
         dbms_output.put_line('user2.testproc called');
         end;
         /
15100: User USER1 lacks privilege CREATE ANY PROCEDURE
The command failed.

Instance administrator:

Command> grant create any procedure to user1;

user1:

Now user1 can create a procedure in another schema, but cannot execute it without owning it or having necessary privilege.

Command> create procedure user2.testproc is
         begin
         dbms_output.put_line('user2.testproc called');
         end;
         /
 
Procedure created.

Command> begin
         user2.testproc();
         end;
         /
 8503: ORA-06550: line 2, column 7:
PLS-00904: insufficient privilege to access object USER2.TESTPROC
 8503: ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
The command failed.

Instance administrator:

Command> grant execute any procedure to user1;

user1:

Now user1 can execute a procedure in another schema.

Command> begin
         user2.testproc();
         end;
         /
user2.testproc called
 
PL/SQL procedure successfully completed.

Invalidated objects

When a privilege on an object is revoked from a user, all of that user's PL/SQL objects that refer to that object are temporarily invalidated. Once the privilege has been restored, a user can explicitly recompile and revalidate an object by executing ALTER PROCEDURE, ALTER FUNCTION, or ALTER PACKAGE, as applicable, on the object. Alternatively, each object is recompiled and revalidated automatically the next time it is executed.

For example, if user1 has a procedure user1.proc0 that calls user2.proc1, proc0 becomes invalid if EXECUTE privilege for proc1 is revoked from user1.

Use the following to see if any of your objects are invalid:

select * from user_objects where status='INVALID';

Example 2-2 Invalidated object

This example shows a series of actions resulting in an invalidated PL/SQL procedure:

  1. A user is granted CREATE ANY PROCEDURE privilege, creates a procedure in another user's schema, then creates a procedure in their own schema that calls the procedure in the other user's schema.

  2. The user is granted EXECUTE privilege to execute the procedure in the other user's schema.

  3. The user executes the procedure in their schema that calls the procedure in the other user's schema.

  4. EXECUTE privilege for the procedure in the other user's schema is revoked from the user, invalidating the user's own procedure.

  5. EXECUTE privilege for the procedure in the other user's schema is granted to the user again. When the user executes their own procedure, it is implicitly recompiled and revalidated.

Administrative user:

Command> grant create any procedure to user1;

user1:

Command> create procedure user2.proc1 is
         begin
         dbms_output.put_line('user2.proc1 is called');
         end;
         /
 
Procedure created.
 
Command> create procedure user1.proc0 is
         begin
         dbms_output.put_line('user1.proc0 is called');
         user2.proc1;
         end;
         /
 
Procedure created.
 

Administrative user:

Command> grant execute on user2.proc1 to user1;

user1:

Command> begin
         user1.proc0;
         end;
         /
user1.proc0 is called
user2.proc1 is called
 
PL/SQL procedure successfully completed.
 

And to confirm user1 has no invalid objects:

Command> select * from user_objects where status='INVALID';
0 rows found.

Administrative user:

Now revoke the EXECUTE privilege from user1.

Command> revoke execute on user2.proc1 from user1;

user1:

Immediately, user1.proc0 becomes invalid because user1 no longer has privilege to execute user2.proc1.

Command> select * from user_objects where status='INVALID';
< PROC0, <NULL>, 273, <NULL>, PROCEDURE, 2019-06-04 14:51:34, 2019-06-04 14:58:23,
2019-06-04:14:58:23, INVALID, N, N, N, 1, <NULL> >
1 row found.

So user1 can no longer execute the procedure.

Command> begin
         user1.proc0;
         end;
         /
 8503: ORA-06550: line 2, column 7:
PLS-00905: object USER1.PROC0 is invalid
 8503: ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
The command failed.

Administrative user:

Again grant EXECUTE privilege on user2.proc1 to user1.

Command> grant execute on user2.proc1 to user1;

user1:

The procedure user1.proc0 is still invalid until it is either explicitly or implicitly recompiled. It is implicitly recompiled when it is executed, as shown here. Or ALTER PROCEDURE could be used to explicitly recompile it.

Command> select * from user_objects where status='INVALID';
< PROC0, <NULL>, 273, <NULL>, PROCEDURE, 2019-06-04 14:51:34, 2019-06-04 16:13:00,
2019-06-04:16:13:00, INVALID, N, N, N, 1, <NULL> >
1 row found.
Command> begin
         user1.proc0;
         end;
         /
user1.proc0 is called
user2.proc1 is called
 
PL/SQL procedure successfully completed.
 
Command> select * from user_objects where status='INVALID';
0 rows found.

Definer's rights and invoker's rights (AUTHID clause)

When a PL/SQL procedure or function is defined, the optional AUTHID clause of the CREATE FUNCTION or CREATE PROCEDURE statement specifies whether the function or procedure executes with definer's rights (AUTHID DEFINER, the default) or invoker's rights (AUTHID CURRENT_USER).

The AUTHID setting affects the name resolution and privilege checking of SQL statements that a procedure or function issues at runtime. With definer's rights, SQL name resolution and privilege checking operate as though the owner of the procedure or function (the definer, in whose schema it resides) is running it. With invoker's rights, SQL name resolution and privilege checking simply operate as though the current user (the invoker) is running it.

For procedures or functions in a package, the AUTHID clause of the CREATE PACKAGE statement specifies whether each member function or procedure of the package executes with definer's rights or invoker's rights. The AUTHID clause is shown in the syntax documentation for these statements, under "SQL Statements" in Oracle TimesTen In-Memory Database SQL Reference.

Invoker's rights would be useful in a scenario where you might want to grant broad privileges for a body of code, but would want that code to affect only each user's own objects in his or her own schema.

Definer's rights would be useful in a situation where you want all users to have access to the same centralized tables or other SQL objects, but only for the specific and limited actions that are executed by the procedure. The users would not have access to the SQL objects otherwise.

Refer to "Invoker's Rights and Definer's Rights (AUTHID Property)" in Oracle Database PL/SQL Language Reference for additional information.

Example 2-3 AUTHID clause for definer's or invoker's rights

This example runs a script twice in ttIsql with just one change, first defining a PL/SQL procedure with AUTHID CURRENT_USER for invoker's rights, then with AUTHID DEFINER for definer's rights.

Script for AUTHID examples:

The script assumes three users have been created: a tool vendor and two tool users (brandX and brandY). Each has been granted CREATE SESSION, CREATE PROCEDURE, and CREATE TABLE privileges as necessary. The following setup is also assumed, to allow "use username;" syntax to connect to the database as username.

connect adding "uid=toolVendor;pwd=pw" as toolVendor;
connect adding "uid=brandX;pwd=pw" as brandX;
connect adding "uid=brandY;pwd=pw" as brandY;

The script does the following:

  • Creates the procedure, printInventoryStatistics, as the tool vendor.

  • Creates a table with the same name, myInventory, in each of the three user schemas, populating it with unique data in each case.

  • Runs the procedure as each of the tool users.

The different results between the two executions of the script show the difference between invoker's rights and definer's rights.

Following is the script for the invoker's rights execution.

use toolVendor;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('butter', 1);

create or replace procedure printInventoryStatistics authid current_user is
 inventoryCount pls_integer;
begin
 select count(*) into inventoryCount from myInventory;
 dbms_output.put_line('Total items in inventory: ' || inventoryCount);
 for currentItem in (select * from myInventory) loop
   dbms_output.put_line(currentItem.name || ' ' || currentItem.inventoryCount);
 end loop;
end;
/
grant execute on printInventoryStatistics to brandX;
grant execute on printInventoryStatistics to brandY;
 
use brandX;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('toothpaste', 100);
set serveroutput on
execute toolVendor.printInventoryStatistics;
 
use brandY;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('shampoo', 10);
set serveroutput on
execute toolVendor.printInventoryStatistics; 

The only difference for the definer's rights script is the change in the AUTHID clause for the procedure definition.

...
create or replace procedure printInventoryStatistics authid definer is
 inventoryCount pls_integer;
begin
 select count(*) into inventoryCount from myInventory;
 dbms_output.put_line('Total items in inventory: ' || inventoryCount);
 for currentItem in (select * from myInventory) loop
   dbms_output.put_line(currentItem.name || ' ' || currentItem.inventoryCount);
 end loop;
end;
/
...

Using AUTHID CURRENT_USER

This part shows the results when the procedure is defined with invoker's rights. Note that when the tool users brandX and brandY run the printInventoryStatistics procedure, each sees the data in his own (the invoker's) myInventory table.

Command> run invoker.sql
 
use toolVendor;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('butter', 1);
1 row inserted.
 
create or replace procedure printInventoryStatistics authid current_user is
 inventoryCount pls_integer;
begin
 select count(*) into inventoryCount from myInventory;
 dbms_output.put_line('Total items in inventory: ' || inventoryCount);
 for currentItem in (select * from myInventory) loop
   dbms_output.put_line(currentItem.name || ' ' || currentItem.inventoryCount);
 end loop;
end;
/

Procedure created.
 
grant execute on printInventoryStatistics to brandX;
grant execute on printInventoryStatistics to brandY;
 
use brandX;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('toothpaste', 100);
1 row inserted.
set serveroutput on;
 
execute toolVendor.printInventoryStatistics;
Total items in inventory: 1
toothpaste 100
 
PL/SQL procedure successfully completed.
 
use brandY;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('shampoo', 10);
1 row inserted.
set serveroutput on;
 
execute toolVendor.printInventoryStatistics;
Total items in inventory: 1
shampoo 10
 
PL/SQL procedure successfully completed.

Use the following to terminate all the connections:

Command> disconnect all;

Using AUTHID DEFINER

This part shows the results when the procedure is defined with definer's rights. Note that when the tool users brandX and brandY run printInventoryStatistics, each sees the data in myInventory belonging to the tool vendor (the definer).

Command> run definer.sql
 
use toolVendor;

create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('butter', 1);
1 row inserted.
 
create or replace procedure printInventoryStatistics authid definer is
 inventoryCount pls_integer;
begin
 select count(*) into inventoryCount from myInventory;
 dbms_output.put_line('Total items in inventory: ' || inventoryCount);
 for currentItem in (select * from myInventory) loop
   dbms_output.put_line(currentItem.name || ' ' || currentItem.inventoryCount);
 end loop;
end;
/
 
Procedure created.
 
grant execute on printInventoryStatistics to brandX;
grant execute on printInventoryStatistics to brandY;
 
use brandX;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('toothpaste', 100);
1 row inserted.
set serveroutput on;
 
execute toolVendor.printInventoryStatistics;
Total items in inventory: 1
butter 1
 
PL/SQL procedure successfully completed.
 
use brandY;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('shampoo', 10);
1 row inserted.
set serveroutput on;
 
execute toolVendor.printInventoryStatistics;
Total items in inventory: 1
butter 1
 
PL/SQL procedure successfully completed.

In this case, it is also instructive to see that although brandX and brandY can each access the toolVendor.myInventory table through the procedure, they cannot access it directly. That is a key use of definer's rights, to enable specific and restricted access to a table or other SQL object through the actions of a procedure.

Command> use brandX;
brandx: Command> select * from toolVendor.myInventory;
15100: User BRANDX lacks privilege SELECT on TOOLVENDOR.MYINVENTORY
The command failed.

brandx: Command> use brandY;
brandy: Command> select * from toolVendor.myInventory;
15100: User BRANDY lacks privilege SELECT on TOOLVENDOR.MYINVENTORY
The command failed.

When finished, terminate all the connections:

Command> disconnect all;

Privileges for cache groups

There are system and object privileges for cache groups. In order for a user to perform operations involving any cache group, the user must have the appropriate cache group privileges.

In addition, there are administrative users, namely the cache administration user (an Oracle Database user) and the cache manager user (a TimesTen user), who require special privileges.

These sections discuss cache group users and privileges:

See "Cache group users" for an introduction to these users.

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

Note:

Passthrough does not require any cache group privileges, because privileges are checked by the Oracle database with the user credentials.

Cache administration user privilege

On Oracle Database, run the SQL*Plus script grantCacheAdminPrivileges.sql in the timesten_home/install/oraclescripts directory as the SYS user to grant the cache administration user the minimum set of privileges required to perform cache operations.

See "Grant privileges to the Oracle database users" in Oracle TimesTen Application-Tier Database Cache User's Guide for details.

Cache manager user privilege

The required privilege for the TimesTen cache manager user is the CACHE_MANAGER system privilege, enabling the user to perform necessary cache group operations. A user 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.)

For a complete list of individual cache group operation privileges, see "Required privileges for the cache administration user and the cache manager user" in Oracle TimesTen Application-Tier Database Cache User's Guide.

This grants the CACHE_MANAGER privilege to pat:

Command> 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 needed for creating AWT cache groups.

Cache user privilege

Operations on a cache group or a cache table, such as loading a cache group or updating a cache table, can be performed by any TimesTen user who has sufficient privileges. Note that for these users, there must also be a corresponding Oracle Database user with the same name who has privilege to select from and update the cached Oracle Database tables.

For related information, see "Create the TimesTen users" and "Grant privileges to the TimesTen users" in Oracle TimesTen Application-Tier Database Cache User's Guide.

Individual cache group privileges are discussed in these sections:

Cache group system privileges

Cache group system privileges enable a user to operate on cache group objects across the database.

  • 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 whether the table is owned by the user.

  • 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 applicable. 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:

Command> GRANT ALTER ANY CACHE GROUP TO pat;

These cache group system privileges are for operations on objects not owned by the user:

  • FLUSH ANY CACHE GROUP

  • LOAD ANY CACHE GROUP

  • UNLOAD ANY CACHE GROUP

  • REFRESH ANY CACHE GROUP

Cache group object privileges

Object privileges for cache group operations enable a user to perform a particular operation on a particular cache group that the user does not own. These are the available cache group object privileges:

  • FLUSH

  • LOAD

  • UNLOAD

  • REFRESH

This example grants pat the cache group object privilege to perform a FLUSH on the cache group cachegrp that is owned by terry:

Command> GRANT FLUSH ON terry.cachegrp TO pat;

For details on cache group operations, see "Cache Group Operations" in Oracle TimesTen Application-Tier Database Cache User's Guide.

User privilege views

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

Table 2-3 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. ADMIN privilege is required 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. ADMIN privilege is required to select from this view.


This example displays 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 and Views" in Oracle TimesTen In-Memory Database System Tables and Views Reference.