4-17. Executing Stored Code Under Your Own Privilege Set
Problem
You have loaded all of an application's objects into a single application schema. However, you do not want packages, procedures, and functions to execute as the schema owner. Instead, you want stored code to execute with the privileges and access of the user who is invoking that code.
Solution
Use invoker's rights by providing the AUTHID property within the declaration of your program. If the AUTHID property is specified when defining a package, procedure, or function, then you have the ability to specify whether the program should be invoked using the CURRENT_USER privileges or the DEFINER privileges. In the case of this solution, you would rather use the CURRENT_USER privileges to ensure that the user does not have the same level of access as the schema owner. The default is DEFINER.
The following code shows how to create a procedure for changing a password, and it uses the AUTHID property to ensure that the procedure will be run using the CURRENT_USER's privilege set. This particular procedure uses dynamic SQL to create a SQL statement. To learn more about using dynamic SQL, please see Chapter 8.
CREATE OR REPLACE PROCEDURE change_password(username IN VARCHAR2,
new_password IN VARCHAR2)
AUTHID CURRENT_USER IS
sql_stmt VARCHAR2(100);
BEGIN
sql_stmt := 'ALTER USER ' || username || ' IDENTIFIED BY ' || new_password;
EXECUTE IMMEDIATE sql_stmt;
END;
When the user executes this procedure, it will be executed using their own set of permissions. This will prevent them from changing anyone else's password unless they have the ability to do so under their allotted permission set.
How It Works
Invoker's rights are a great way to secure your application if you are planning to limit access to the CURRENT_USER's privilege set. To allow for invoker's rights to be set into place, the AUTHID property must be used with the CURRENT_USER keyword in the definition of a stored PL/SQL unit. This property affects the name resolution and privilege set for that unit. You can find the value of the AUTHID property if you take a look at the USER_PROCEDURES data dictionary view.
Using the invoker's rights methodology is a great way to protect a program as long as the users access the program with their own database account. If each user within the database has their own account, then they can be granted the required level of access via database roles. The AUTHID property can constrain the execution of code to the current user's privilege set. Because of that, if a user does not have the privileges that are required to execute a particular program, then they will not have access. Simply put, invoker's rights are a good means of securing your code as long as the approach is used correctly.