Enhanced Code Accessibility 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 just one enhancement, but a really nice one:

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.

In this post, I also show how you can specify the "unit kind" (program unit type) of the whitelisted program unit. This feature was available in 12.1, but I have not previously demonstrated it. So I will include it here. [Thanks to Iudith for pointing out that this is not a new 12.2 feature!]

The ability to specify the "unit kind" 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.

And as Iudith points out in her comments below, what's really going on is that the PL/SQL engine is trying to interpret "pkg1.myproc1" as a program unit named "MYPROC1" owned by a schema named "PKG1".

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