TimesTen has features to control database access with object-level resolution for database objects such as tables, views, materialized views, indexes, sequences, functions, procedures, and packages, for example. You can refer to "Managing Access Control" in Oracle TimesTen In-Memory Database Operations Guide for introductory information about TimesTen access control features.
This chapter introduces access control as it relates to PL/SQL users.
Note:
Access control is enabled when you install TimesTen. You cannot disable it.Topics in this chapter include the following:
This section covers the following topics:
For PL/SQL users, access control affects the ability 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 schema other than your own. 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.
To execute a procedure or function, you must be the owner, have the EXECUTE privilege for the procedure or function (or for the package to which it belongs, if applicable), or have the EXECUTE ANY PROCEDURE privilege. This is all summarized in Table 7-1.
Table 7-1 Privileges for using PL/SQL procedures and functions
| Action | SQL statement or operation | Required Privilege |
|---|---|---|
|
Create a procedure, function, package definition, or package body. |
|
Or:
|
|
Alter a procedure, function, or package. |
|
Ownership of the procedure, function, or package Or:
|
|
Drop a procedure, function, package definition, or package body. |
|
Ownership of the procedure, function, or package Or:
|
|
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:
Or:
|
|
Create a private synonym for a procedure, function, or package. |
|
Or:
|
|
Create a public synonym for a procedure, function, or package. |
|
|
|
Use a synonym to execute a procedure or function. |
Invoke the procedure or function through its synonym. |
Privilege to execute the underlying procedure or function |
|
Drop a private synonym for a procedure, function, or package. |
|
Ownership of the synonym Or:
|
|
Drop a public synonym for a procedure, function, or package. |
|
|
See "SQL Statements" in Oracle TimesTen In-Memory Database SQL Reference for the syntax and required privileges of SQL statements discussed in this section.
Notes:
A user who has been granted privilege to execute a procedure (or function) can execute the procedure even if he or she has no privilege 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, he 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.
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.
Use the SQL statement GRANT to grant a privilege. Use REVOKE to revoke one.
The following example grants 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;
This example revokes the privileges:
Command> revoke execute on user1.myproc from user2; Command> revoke execute on user1.mypkg from user2;
Example 7-1 Granting of required privileges
This example shows a series of attempted operations by a user, user1, as follows:
The user attempts each operation before having the necessary privilege. The resulting error is shown.
The instance administrator grants the necessary privilege.
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 his own schema, he can execute it because he owns 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 he cannot execute it yet because he does not own it or have 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.
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';
See "SQL Statements" in Oracle TimesTen In-Memory Database SQL Reference for information about the ALTER statements.
Example 7-2 Invalidated object
This example shows a sequence that results in an invalidated object, in this case a PL/SQL procedure, as follows:
A user is granted CREATE ANY PROCEDURE privilege, creates a procedure in another user's schema, then creates a procedure in his own schema that calls the procedure in the other user's schema.
The user is granted EXECUTE privilege to execute the procedure in the other user's schema.
The user executes the procedure in his schema that calls the procedure in the other user's schema.
EXECUTE privilege for the procedure in the other user's schema is revoked from the user, invalidating the user's own procedure.
EXECUTE privilege for the procedure in the other user's schema is granted to the user again. When he executes his own procedure, it is implicitly recompiled and revalidated.
Instance administrator:
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.
Instance administrator:
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.
Instance administrator:
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, 2009-06-04 14:51:34, 2009-06-04 14:58:23, 2009-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.
Instance administrator:
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, 2009-06-04 14:51:34, 2009-06-04 16:13:00, 2009-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.
For any query or SQL DML statement executed in an anonymous block, or any SQL DDL statement executed in an EXECUTE IMMEDIATE statement, including all such operations discussed in this document or used in any example, it is assumed that the user has appropriate privilege to execute the statement and access the desired objects. SQL executed in a PL/SQL anonymous block requires the same privilege as when executed directly. For example, to insert rows of data into a table you own, no privilege is required. If you want to insert rows of data into a table you do not own, you must be granted INSERT privilege on that table or granted INSERT ANY TABLE.
Refer to "SQL Statements" in Oracle TimesTen In-Memory Database SQL Reference for details SQL statements and their required privileges.
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). Similarly, 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.
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.
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.
See "Examples using the AUTHID clause" for examples using definer's and invoker's rights.
Refer to "Invoker's Rights and Definer's Rights (AUTHID Property)" in Oracle Database PL/SQL Language Reference for additional information.
This section covers the following:
Note the following when connecting to the database:
Privilege to connect to a database must be explicitly granted to every user, other than the instance administrator, through the CREATE SESSION privilege. This is a system privilege so must be granted to the user either by the instance administrator or by a user with ADMIN privilege. This can be accomplished either directly or through the PUBLIC role. Refer to "Managing Access Control" in Oracle TimesTen In-Memory Database Operations Guide for additional information and examples.
Required privileges for PL/SQL connection attributes are included in "PL/SQL connection attributes".
Note the following regarding access to system views and PL/SQL supplied packages.
SELECT and EXECUTE privileges on various system tables, system views, PL/SQL functions, PL/SQL procedures, and PL/SQL packages are granted by default to all users through the PUBLIC role, of which all users are a member. This role is documented in "Privileges" in Oracle TimesTen In-Memory Database SQL Reference. Use the following command to see the list of these public database objects and the associated privileges:
SELECT table_name, privilege FROM sys.all_tab_privs WHERE grantee='PUBLIC';
All users have SELECT privilege for the ALL_* and USER_* system views.
EXECUTE ANY PROCEDURE does not apply to supplied packages; however, most are accessible through the PUBLIC role. Access control for PL/SQL packages provided with TimesTen is noted at the beginning of Chapter 8, "TimesTen Supplied PL/SQL Packages."
The ttPLSQLMemoryStats built-in procedure, which returns statistics about library cache performance and activity, can be called by any user. This procedure is documented under "ttPLSQLMemoryStats" in Oracle TimesTen In-Memory Database Reference. Also see Example 6-3.