Restricting access to program units by using accessible by

In this recipe, you'll learn about the effects of using the accessible by clause.

Getting ready

To complete this recipe, you'll use a user who has the create procedure privilege.

How to do it...

1. Connect as a user who has the create procedure privilege (for example, zoran):

   SQL> connect zoran
   

2. Create the protected_pkg package that is only accessible by public_pkg:

   CREATE OR REPLACE PACKAGE protected_pkg   
      ACCESSIBLE BY (public_pkg)
   IS
      PROCEDURE protected_proc;
   END;
   /
   CREATE OR REPLACE PACKAGE BODY protected_pkg
   IS
      PROCEDURE protected_proc
      IS
      BEGIN
            DBMS_OUTPUT.PUT_LINE ('This is a Protected Procedure
                that can only be accessed from Public Package');
         END;
   END;
   /
   

3. Create the public_pkg package:

   CREATE OR REPLACE PACKAGE public_pkg
   IS
         PROCEDURE public_proc;
   END;
   /
   CREATE OR REPLACE PACKAGE BODY public_pkg
   IS
         PROCEDURE public_proc
         IS
         BEGIN
            DBMS_OUTPUT...