Skip to main content

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

Comments

  1. Hello Steven,

    Just two short remarks:

    1. As by the documentation, specifying the 'unit kind' in the ACCESSIBLE BY clause was also available in 12cR1.
    Specifying TRIGGER was mandatory, so, when 'unit kind' was NOT specified,
    it was implied as one of "the other namespace"
    ( FUNCTION, PACKAGE, PROCEDURE or TYPE ).

    So, if we wanted to make a unit accessible from both a trigger and another unit having the same name,
    then we should have probably had to specify the two accessors separately,
    while in 12.2 it is sufficient to omit the unit kind entirely.


    2. In the procedure this_for_pkgd_proc1_only, when you specify "ACCESSIBLE BY (PROCEDURE pkg1.myproc1)",
    the accessor is interpreted as PROCEDURE myproc1 from schema pkg1.

    If you wanted to reference package pkg1, the syntax should have been
    "ACCESSIBLE BY (PACKAGE pkg1)" and, indeed, it is NOT possible to specify a specific procedure from that package, like in "ACCESSIBLE BY (PACKAGE myschema.pkg1.myproc1)".
    Using such a syntax produces compilation error.

    Best Regards,
    Iudith

    ReplyDelete
    Replies
    1. Thanks so much for bringing your usual excellent scrutiny to bear on my writings, Iudith. I have made changes to the post and given you credit.

      Delete
  2. Hi Steven,

    I would like to say thank you,because your blogs are help full for me to implement logic for writing new code.

    I have one query ...........
    Is it possible to create trigger on dynamic Table?

    Table is created dynamically like "Table_||seq.nextval".

    ReplyDelete
  3. Surya, I am not really sure what you are asking. You show an example of a dynamic REFERENCE to a table, but other than that I do not know what you mean by "dynamic table". If you mean a table that is created at runtime via a call to execute immediate, then sure, right after you create the table dynamically, you can create your trigger the same way.

    ReplyDelete
    Replies
    1. Thanks for your Support.
      Now I got solution for my new logic to implement in my code.

      Delete

Post a Comment

Popular posts from this blog

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p

The differences between deterministic and result cache features

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel