Enhanced Whitelist Management in 12.2

Way back in Oracle Database 12c Release 1, the PL/SQL team added whitelisting to the language. This means you can use the ACCESSIBLE BY clause to specify the "white list" of program units that are allowed to invoke another program unit (schema-level procedure, function, package).

For details on the 12.1 ACCESSIBLE BY feature set, check out my Oracle Magazine article, When Packages Need to Lose Weight. In that article, I step through the process of breaking up a large package body into “sub” packages whose access is restricted through use of the ACCESSIBLE BY feature.

I'll wait while you read the article.

Tick, tock, tick, tock….

OK, all caught up now? Great! In 12.2, there are two enhancements:

1. You can now specify whitelisting for a subprogram within a package. 

This is a very nice fine-tuning and is sure to come in handy.

2. You can specify the "unit kind" (program unit type) of the whitelisted program unit. 

This is useful when you have a trigger with the same name as a function, procedure or package (they do not, unfortunately, share the same namespace) and you need to distinguish which you want to include in the white list. Chances are, this will not be an issue for you, assuming you follow some common-sense naming conventions for your program unit.

Let's go exploring with code – all of which can be executed at LiveSQL.

First, I create a package spec and body that demonstrate the new functionality: I use ACCESSIBLE BY not at the package level, but with individual subprograms. Notice that the first two usages include the unit kind (PROCEDURE and TRIGGER).

The third usage does not include a unit kind. And the fourth usage tries to specify a packaged subprogram for whitelisting. I say “tries” because as you will soon see, that’s not supported.

CREATE TABLE my_data (n NUMBER);
CREATE OR REPLACE PACKAGE pkg
AUTHID DEFINER
IS
PROCEDURE do_this;
PROCEDURE this_for_proc_only
ACCESSIBLE BY (PROCEDURE generic_name);
PROCEDURE this_for_trigger_only
ACCESSIBLE BY (TRIGGER generic_name);
PROCEDURE this_for_any_generic_name
ACCESSIBLE BY (generic_name);
PROCEDURE this_for_pkgd_proc1_only
ACCESSIBLE BY (PROCEDURE pkg1.myproc1);
END;
/
Package created.
CREATE OR REPLACE PACKAGE BODY pkg
IS
PROCEDURE do_this
IS
BEGIN
NULL;
END;
PROCEDURE this_for_proc_only
ACCESSIBLE BY (PROCEDURE generic_name)
IS
BEGIN
NULL;
END;
PROCEDURE this_for_trigger_only
ACCESSIBLE BY (TRIGGER generic_name)
IS
BEGIN
NULL;
END;
PROCEDURE this_for_any_generic_name
ACCESSIBLE BY (generic_name)
IS
BEGIN
NULL;
END;
PROCEDURE this_for_pkgd_proc1_only
ACCESSIBLE BY (PROCEDURE pkg1.myproc1)
IS
BEGIN
NULL;
END;
END;
/

Package Body created.


So I now try to compile a trigger that calls the "trigger-only" procedure, and that works just fine. But if I try to use the "procedure-only" procedure, I get a compilation error.

CREATE OR REPLACE TRIGGER generic_name
   BEFORE INSERT
   ON my_data
   FOR EACH ROW
DECLARE
BEGIN
   pkg.this_for_trigger_only;
END;
/
Trigger created.
CREATE OR REPLACE TRIGGER generic_name
BEFORE INSERT
ON my_data
FOR EACH ROW
DECLARE
BEGIN
pkg.this_for_proc_only;
END;
/
PLS-00904: insufficient privilege to access object THIS_FOR_PROC_ONLY

Now I show the same thing for a procedure: it can't call the trigger-only version, but it can invoke the procedure-only subprogram.

CREATE OR REPLACE PROCEDURE generic_name
   AUTHID DEFINER
IS
BEGIN
   pkg.this_for_proc_only;
END;
/
Procedure created.
CREATE OR REPLACE PROCEDURE generic_name
AUTHID DEFINER
IS
BEGIN
pkg.this_for_trigger_only;
END;
/
PLS-00904: insufficient privilege to access object THIS_FOR_TRIGGER_ONLY

And now you can see that both the trigger and procedure can invoke the subprogram that did not include a "unit kind."

CREATE OR REPLACE TRIGGER generic_name
   BEFORE INSERT
   ON my_data
   FOR EACH ROW
DECLARE
BEGIN
   pkg.this_for_any_generic_name;
END;
/
Trigger created.
CREATE OR REPLACE PROCEDURE generic_name
AUTHID DEFINER
IS
BEGIN
pkg.this_for_any_generic_name;
END;
/

Procedure created.


Finally, I try to invoke the subprogram whose ACCESSIBLE BY clause specified "(PROCEDURE pkg1.myproc1)". Unfortunately, this is not yet supported. You can only list program units, not subprograms, in the list. So while the package named "pkg" compiles, you will it impossible to execute that subprogram from anywhere.

CREATE OR REPLACE PACKAGE pkg1
   AUTHID DEFINER
IS
   PROCEDURE myproc1;
END;
/
Package created.
CREATE OR REPLACE PACKAGE BODY pkg1
IS
PROCEDURE myproc1
IS
BEGIN
pkg.this_for_pkgd_proc1_only;
END;
END;
/

PLS-00904: insufficient privilege to access object THIS_FOR_PKGD_PROC1_ONLY