Oracle Database 12c PL/SQL Enhancements
Bu ay içerisinde elime ulaşan ORACLE Magazine'nin son yayınında Steven Feuerstein'in (
ORACLE ACE Director ) 12c ile PL/SQL tarafına gelen özelliklerine ait makalesini keyifli ve güzel detaylar içermesi nedeniyle blogum da sizlerle orjinal yayın formatında paylaşmak istedim. Emininki sizlerde keyif alarak okuyacaksınızdır.
ORACLE ACE Director ) 12c ile PL/SQL tarafına gelen özelliklerine ait makalesini keyifli ve güzel detaylar içermesi nedeniyle blogum da sizlerle orjinal yayın formatında paylaşmak istedim. Emininki sizlerde keyif alarak okuyacaksınızdır.
Oracle Database 12c enhances the PL/SQL function result cache, improves PL/SQL execution in SQL, adds a whitelist, and fine-tunes privileges.
Oracle Database 12c offers a variety of enhancements to the way you can define and execute PL/SQL program units. This article covers several new Oracle Database 12c features that enable you to do the following:
- Optimize invoker rights functions for the RESULT_CACHE clause
- Define and execute PL/SQL functions inside SQL statements
- Restrict access to program units through use of a “whitelist,” specified by the ACCESSIBLE BY clause
- Fine-tune privileges for a program unit by granting roles to that unit
Invoker Rights and the PL/SQL Function Result Cache
Oracle Database 11g introduced the PL/SQL function result cache, which offers a very powerful, efficient, and easy-to-use caching mechanism. The main objective of this cache is to ensure that if a row of data hasn’t changed since it was last fetched from the database, no SQL statement needs to execute for it to be retrieved again.
Answer to Previous Challenge |
This holds true across the entire database instance. In other words, suppose a user connected to schema USER_ONE executes a result-cached function to retrieve the row from the employees table for employee ID = 100. When a user connected to schema USER_TWO executes the same function call for the same employee ID, that row of information is retrieved directly from the cache and not by execution of a SELECT statement.
If you are not already using this feature (and you are using Oracle Database 11g), I strongly encourage you to investigate it and start applying it—in close collaboration with your DBA so that the result cache pool is sized properly.
Even in Oracle Database 11g Release 2, however, you could not combine invoker rights (AUTHID CURRENT_USER clause) with the function result cache (RESULT_CACHE keyword). An attempt to compile the following function:
CREATE OR REPLACE FUNCTION last_name (
employee_id_in
IN employees.employee_id%TYPE)
RETURN employees.last_name%TYPE
AUTHID CURRENT_USER
RESULT_CACHE
IS
l_return employees.last_name%TYPE;
BEGIN
SELECT last_name
INTO l_return
FROM employees
WHERE employee_id = employee_id_in;
RETURN l_return;
END;
/
results in this compilation error:
PLS-00999: implementation restriction (may be temporary) RESULT_CACHE is disallowed on subprograms in Invoker-Rights modules
The reason for this restriction is the whole point of invoker rights. At runtime the PL/SQL engine uses the privileges of the current user to resolve references to database objects such as tables and views. But if such a function was compiled with RESULT_CACHE, then (using the example above) after USER_ONE executed the function, passing in 100, when USER_TWO makes the same function call, the body of the function would not be executed and the reference to the EMPLOYEES table would not be resolved according to USER_TWO’s privilege. This could have caused serious security issues.
Well, the good news is that this restriction was temporary. In Oracle Database 12c, you can now compile functions such as last_name (above) without error—and Oracle Database 12c does the right thing, of course.
Behind the scenes, Oracle Database 12c passes the name of the current user as a hidden parameter; this value is cached along with the values of all the arguments passed to the function. So each time the last_name function is called, Oracle Database 12c checks to see if that function has been previously called with both the same employee ID and the same current user.
This means that the result cache for an invoker rights function is (logically) partitioned by the name of the current user. Consequently, the result cache for an invoker rights function will improve performance only in situations in which the same user calls the function with the same argument values repeatedly. Another way of explaining this is to point out that in Oracle Database 11g Release 2, I could have achieved the same effect, but only if I had changed the implementation of the last_name function, as shown in Listing 1.
Code Listing 1: “Partitioned” Oracle Database 11g Release 2 invoker’s rights function
CREATE OR REPLACE PACKAGE employee_api
AUTHID CURRENT_USER
IS
FUNCTION last_name (
employee_id_in IN employees.employee_id%TYPE)
RETURN employees.last_name%TYPE;
END;
/
CREATE OR REPLACE PACKAGE BODY employee_api
IS
FUNCTION i_last_name (
employee_id_in IN employees.employee_id%TYPE,
user_in IN VARCHAR2 DEFAULT USER)
RETURN employees.last_name%TYPE
RESULT_CACHE
IS
l_return employees.last_name%TYPE;
BEGIN
SELECT last_name
INTO l_return
FROM employees
WHERE employee_id = employee_id_in;
RETURN l_return;
END;
FUNCTION last_name (
employee_id_in IN employees.employee_id%TYPE)
RETURN employees.last_name%TYPE
IS
l_return employees.last_name%TYPE;
BEGIN
RETURN i_last_name (employee_id_in,
USER);
END;
END;
/
Note that the last_name function is defined in the package specification and is not result-cached. Instead, that public function (declared in the package specification) merely calls the private/internal “version” of the function, which has a second parameter: the user.
So each time you call employee_api.last_name, Oracle Database 11g Release 2 adds the name of the user to the set of values used by the database to determine whether there is a match in the result cache.
This is no longer necessary; in Oracle Database 12c, you simply need to decide if you think it is worth adding RESULT_CACHE to invoker rights programs.
Define PL/SQL Subprograms in a SQL Statement
Developers have long been able to call their own PL/SQL functions from within a SQL statement. Suppose, for example, I have created a function named BETWNSTR that returns the substring between the specified start and end locations:
FUNCTION betwnstr (
string_in IN VARCHAR2
, start_in IN PLS_INTEGER
, end_in IN PLS_INTEGER
)
RETURN VARCHAR2
IS
BEGIN
RETURN ( SUBSTR (
string_in, start_in,
end_in - start_in + 1 ));
END;
I can then use it in a query as follows:
SELECT betwnstr (last_name, 3, 5) FROM employees
This approach offers a way to both “extend” the SQL language with application-specific functionality and reuse (rather than copy) algorithms. A downside of user-defined function execution in SQL is that it involves a context switch between the SQL and PL/SQL execution engines.
With Oracle Database 12c, you can now define PL/SQL functions and procedures in the WITH clause of a subquery and then use them as you would any other built-in or user-defined function. This feature enables me to consolidate the BETWNSTR function and query shown above into a single statement:
WITH
FUNCTION betwnstr (
string_in IN VARCHAR2,
start_in IN PLS_INTEGER,
end_in IN PLS_INTEGER)
RETURN VARCHAR2
IS
BEGIN
RETURN (SUBSTR (
string_in,
start_in,
end_in - start_in + 1));
END;
SELECT betwnstr (last_name)
FROM employees
So why would a developer want to copy logic from a PL/SQL function into a SQL statement? To improve performance. When I call my own PL/SQL function in a SQL statement, the SQL engine must perform a performance-affecting context switch to the PL/SQL engine. Moving the code inside the SQL statement means that that context switch no longer occurs.
Reference a Packaged Constant
Although you can call a packaged function in SQL, you cannot reference a constant declared in a package (unless that SQL statement is executed inside a PL/SQL block). Here’s an example of the constant reference limitation:
SQL> CREATE OR REPLACE PACKAGE pkg
2 IS
3 year_number
CONSTANT INTEGER := 2013;
4 END;
5 /
Package created.
SQL> SELECT pkg.year_number
FROM employees
2 WHERE employee_id = 138
3 /
SELECT pkg.year_number FROM employees
ERROR at line 1:
ORA-06553: PLS-221: 'YEAR_NUMBER' is not
a procedure or is undefined
The classic workaround to this limitation has been to define a function in the package and then call the function:
SQL> CREATE OR REPLACE PACKAGE pkg
2 IS
3 FUNCTION year_number
4 RETURN INTEGER;
5 END;
6 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY pkg
2 IS
3 c_year_number
CONSTANT INTEGER := 2013;
4
5 FUNCTION year_number
6 RETURN INTEGER
7 IS
8 BEGIN
9 RETURN c_year_number;
10 END;
11 END;
12 /
Package body created.
SQL> SELECT pkg.year_number
2 FROM employees
3 WHERE employee_id = 138
4 /
YEAR_NUMBER
———————————
2013
That’s a lot of code and effort simply to be able to reference the constant’s value in a SQL statement. And, with Oracle Database 12c, it is no longer necessary. I can, instead, simply create a function in the WITH clause:
WITH FUNCTION year_number RETURN INTEGER IS BEGIN RETURN pkg.year_number; END; SELECT year_number FROM employees WHERE employee_id = 138
You will also find in-SQL PL/SQL functions to be handy in standby read-only databases. Although you will not be able to create “helper” PL/SQL functions in such a database, you will be able to define that function directly inside the query.
This WITH FUNCTION feature is a very useful enhancement to the SQL language. You should, however, ask yourself this question each time you contemplate using it: “Do I need this same functionality in multiple places in my application?”
If you do need it, you should decide if the performance improvement of using WITH FUNCTION outweighs the potential downside of copying and pasting this logic into multiple SQL statements.
Whitelists and the ACCESSIBLE BY Clause
Most PL/SQL-based applications are made up of many packages, some of which are the “top level” API to be used by programmers to implement user requirements and others of which are “helper” packages that are to be used only by certain other packages.
Before Oracle Database 12c, PL/SQL could not prevent a session from using any and all subprograms in packages to which that session’s schema had been granted EXECUTE authority. As of Oracle Database 12c, in contrast, all PL/SQL program units have an optional ACCESSIBLE BY clause that enables you to specify a whitelist of other PL/SQL units that can access the PL/SQL unit you are creating or altering.
Let’s take a look at an example. First I create my “public” package specification, which is intended for use by other developers to build the application.
CREATE OR REPLACE PACKAGE public_pkg IS PROCEDURE do_only_this; END; /
Next, I create the specification of my “private” package. The package is private in the sense that I want to make sure that it can be invoked only from within the public package (public_pkg). So I add the ACCESSIBLE_BY clause:
CREATE OR REPLACE PACKAGE private_pkg ACCESSIBLE BY (public_pkg) IS PROCEDURE do_this; PROCEDURE do_that; END; /
Now it’s time to implement the package bodies. The public_pkg.do_only_this procedure calls the private_pkg subprograms:
CREATE OR REPLACE PACKAGE BODY public_pkg
IS
PROCEDURE do_only_this
IS
BEGIN
private_pkg.do_this;
private_pkg.do_that;
END;
END;
/
CREATE OR REPLACE PACKAGE BODY
private_pkg
IS
PROCEDURE do_this
IS
BEGIN
DBMS_OUTPUT.put_line ('THIS');
END;
PROCEDURE do_that
IS
BEGIN
DBMS_OUTPUT.put_line ('THAT');
END;
END;
/
I can now run the public package’s procedure without any problem:
BEGIN public_pkg.do_only_this; END; / THIS THAT
But if I try to call a subprogram in the private package in an anonymous block, I see this error:
BEGIN private_pkg.do_this; END; / ERROR at line 2: ORA-06550: line 2, column 1: PLS-00904: insufficient privilege to access object PRIVATE_PKG ORA-06550: line 2, column 1: PL/SQL: Statement ignored
And the same error occurs if I try to compile a program unit that tries to call a subprogram in the private package:
SQL> CREATE OR REPLACE PROCEDURE
use_private
2 IS
3 BEGIN
4 private_pkg.do_this;
5 END;
6 /
Warning: Procedure created with
compilation errors.
SQL> SHOW ERRORS
Errors for PROCEDURE USE_PRIVATE:
LINE/COL ERROR
———————— ——————————————————————————
4/4 PL/SQL: Statement ignored
4/4 PLS-00904: insufficient
privilege to access object
PRIVATE_PKG
As the “PLS” error indicates, this issue is caught at compilation time. There is no runtime performance hit for using this feature.
Grant Roles to Program Units
Before Oracle Database 12c, a definer’s rights program unit (defined with the AUTHID DEFINER or no AUTHID clause) always executed with the privileges of the definer of that unit. An invoker’s rights program unit (defined with the AUTHID CURRENT_USER clause) always executed with the privileges of the invoker of that unit.
A consequence of these two distinct AUTHID settings is that program units that need to be executed by all users would have to be created as definer’s rights units. The program units would then execute with all the privileges of the definer, which might not be optimal from a security standpoint.
As of Oracle Database 12c, you can grant roles to PL/SQL packages and schema-level procedures and functions. Role-based privileges for program units enable developers to fine-tune the privileges available to the invoker of a program unit.
You can now define a program unit as having invoker’s rights and then complement the invoker’s privileges with specific, limited privileges granted through the role.
Let’s walk through an example that shows how to grant roles to program units and the impact it has. Suppose that the HR schema contains the departments and employees tables, defined and populated with data as follows:
CREATE TABLE departments
(
department_id INTEGER,
department_name VARCHAR2 (100),
staff_freeze CHAR (1)
)
/
BEGIN
INSERT INTO departments
VALUES (10, 'IT', 'Y');
INSERT INTO departments
VALUES (20, 'HR', 'N');
COMMIT;
END;
/
CREATE TABLE employees
(
employee_id INTEGER,
department_id INTEGER,
last_name VARCHAR2 (100)
)
/
BEGIN
DELETE FROM employees;
INSERT INTO employees
VALUES (100, 10, 'Price');
INSERT INTO employees
VALUES (101, 20, 'Sam');
INSERT INTO employees
VALUES (102, 20, 'Joseph');
INSERT INTO employees
VALUES (103, 20, 'Smith');
COMMIT;
END;
/
And suppose that the SCOTT schema contains only an employees table, defined and populated with data as follows:
CREATE TABLE employees
(
employee_id INTEGER,
department_id INTEGER,
last_name VARCHAR2 (100)
)
/
BEGIN
DELETE FROM employees;
INSERT INTO employees
VALUES (100, 10, 'Price');
INSERT INTO employees
VALUES (104, 20, 'Lakshmi');
INSERT INTO employees
VALUES (105, 20, 'Silva');
INSERT INTO employees
VALUES (106, 20, 'Ling');
COMMIT;
END;
/
HR also contains a procedure that removes all employees from the specified department as long as the department does not have its staff “frozen.” I will first create this procedure as a definer’s rights unit, as shown in Listing 2.
Code Listing 2: Definer’s rights procedure that removes employee records
CREATE OR REPLACE PROCEDURE remove_emps_in_dept (
department_id_in IN employees.department_id%TYPE)
AUTHID DEFINER
IS
l_freeze departments.staff_freeze%TYPE;
BEGIN
SELECT staff_freeze
INTO l_freeze
FROM HR.departments
WHERE department_id = department_id_in;
IF l_freeze = ‘N’
THEN
DELETE FROM employees
WHERE department_id = department_id_in;
END IF;
END;
/
And SCOTT can execute this procedure:
GRANT EXECUTE ON remove_emps_in_dept TO SCOTT /
|
DOWNLOAD When SCOTT executes the procedure as shown below, it will remove three rows—but from HR’s employees table, because the procedure is a definer’s rights unit.
BEGIN HR.remove_emps_in_dept (20); END; /
I need to change this procedure so that it will remove rows from SCOTT’semployees table, not HR’s. That is precisely what invoker’s rights do. But if I change the AUTHID clause of this procedure to
AUTHID CURRENT_USER
and run the procedure again, I see this:
BEGIN * ERROR at line 1: ORA-00942: table or view does not exist ORA-06512: at "HR.REMOVE_EMPS_IN_DEPT", line 7 ORA-06512: at line 2
The problem is that Oracle Database is now using the privileges of SCOTT to resolve the references to two tables: HR.departments and SCOTT.employees. SCOTT has no privileges on HR’s departments table, however, so Oracle Database raises the ORA-00942 error.
Prior to Oracle Database 12c, the DBA would have had to grant the necessary privileges on HR.departments to SCOTT. Now, however, DBAs can take the following steps instead:
CREATE ROLE hr_departments / GRANT hr_departments TO hr /
Connected to HR, grant the desired privilege to the role and then grant the role to the procedure:
GRANT SELECT ON departments TO hr_departments / GRANT hr_departments TO PROCEDURE remove_emps_in_dept /
And now when I execute the following statements from SCOTT, the rows are removed from the SCOTT.employees table:
SELECT COUNT (*)
FROM employees
WHERE department_id = 20
/
COUNT(*)
—————————————
3
BEGIN
hr.remove_emps_in_dept (20);
END;
/
SELECT COUNT (*)
FROM employees
WHERE department_id = 20
/
COUNT(*)
—————————————
0
Roles granted to a program unit do not affect compilation. Instead, they affect the privilege checking of SQL statements the unit issues at runtime. Thus, the procedure or function executes with the privileges of both its own roles and any other currently enabled roles.
This feature will be of most use with invoker rights program units. You will likely consider granting roles to a definer’s rights unit when that unit executes dynamic SQL, because the privileges for that dynamic statement are checked at runtime.
Up Next: PL/SQL Enhancements for Executing SQL
Oracle Database 12c offers significant improvements in flexibility and functionality when it comes to defining and executing program units. Oracle Database 12c features enable PL/SQL developers to use invoker rights with the function result cache, define and execute PL/SQL subprograms in SQL statements, restrict access to program units by way of a whitelist, and grant roles to program units.
