2 Authorization in TimesTen

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.

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

Privileges for TimesTen utilities

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

Overview of the PUBLIC role

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

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

Command> GRANT CREATE SESSION TO PUBLIC;

Also see "Privileges through the PUBLIC role" in this document and "The PUBLIC role" in Oracle TimesTen In-Memory Database SQL Reference.

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:

• "Instance administrator", "Understanding the TimesTen users group", and "TimesTen instances" in Oracle TimesTen In-Memory Database Installation, Migration, and Upgrade Guide

• "TimesTen Scaleout architecture" and "The operating system user" in Oracle TimesTen In-Memory Database Scaleout User's Guide

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.

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;

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;

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.

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.

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;

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.

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.

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.

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:

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

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;

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

Command> grant create procedure to user1;

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.
 

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.

Command> grant create any procedure to user1;

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.

Command> grant execute any procedure to user1;

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';

Command> grant create any procedure to 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.
 

Command> grant execute on user2.proc1 to user1;

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.

Command> revoke execute on user2.proc1 from user1;

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.

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.

Command> grant execute on user2.proc1 to user1;

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.

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

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; 

...
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;
/
...

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.

Command> disconnect all;

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.

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.

Command> disconnect all;

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;

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 object privileges

Command> GRANT ALTER ANY CACHE GROUP TO pat;

Command> GRANT FLUSH ON terry.cachegrp TO pat;