Use ACCESSIBLE BY to Control Access to Code

View All Scripts Login to Run Script

Script Name Use ACCESSIBLE BY to Control Access to Code
Description You might implement a database application as several PL/SQL packages—one package that provides the application programming interface (API) and helper packages to do the work. Ideally, only the API is accessible to clients. Also, you might create a utility package to provide services to only some other PL/SQL units in the same schema. Ideally, the utility package is accessible only to the intended PL/SQL units. Before Oracle Database 12c, PL/SQL could not prevent clients from using items exposed in helper packages. To isolate these items, you had to use relational database management system (RDBMS) security features. Some application deployment schemes made RDBMS security features hard to use. As of Oracle Database 12c, any program unit CREATE statement has an optional ACCESSIBLE BY clause that lets you specify a white list of PL/SQL units that can access the PL/SQL unit that you are creating or altering.
Area PL/SQL General / PL/SQL Procedures, Functions, Packages
Contributor Steven Feuerstein
Created Friday December 11, 2015

Statement 1
My fellow programmers should only be able to call this subprogram.
The Public Specification
```
CREATE OR REPLACE PACKAGE public_pkg  
IS  
   PROCEDURE do_only_this;  
END; 
```
```
Package created.
```
Statement 2
The body of my public package got too big. I needed to break it out into several different packages so we could manage it easier, and multiple developers can work on subprograms simultaneously. Consequently, I have to "expose" formerly-private subprograms into a package specification. But I only want them to be called from the body of my "public" package. What's a developer to do? Upgrade to Oracle Database 12c!
My Not-so-private Private Package
```
CREATE OR REPLACE PACKAGE private_pkg     
IS  
   PROCEDURE do_this;  
  
   PROCEDURE do_that;  
END; 
```
```
Package created.
```
Statement 3
Now I tell the PL/SQL compiler to only allow subprograms in the public_pkg to invoke subprograms in private_pkg. You can use a comma delimited list and include schema names, as in (other_schema.package1, package2).
Add ACCESSIBLE BY to Whitelist
```
CREATE OR REPLACE PACKAGE private_pkg     
   ACCESSIBLE BY (public_pkg)  
IS  
   PROCEDURE do_this;  
  
   PROCEDURE do_that;  
END; 
```
```
Package created.
```

Statement 4

I've moved the bulk of my logic into my "private" package. And since I've added the ACCESSIBLE BY clause, no one else can call them.

Public Package Body is a Redirect

CREATE OR REPLACE PACKAGE BODY public_pkg  
IS  
   PROCEDURE do_only_this  
   IS  
   BEGIN  
      private_pkg.do_this;  
      private_pkg.do_that;  
   END;  
END;

Package Body created.

Statement 5

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;

Package Body created.

Statement 6
Well, sure this is allowed.
Invoke My Public Program
```
BEGIN  
   public_pkg.do_only_this;  
END; 
```
```
THIS
THAT
```
Statement 7
An attempt to invoke subprograms in private_pkg from anything (anonymous block, another named program unit) BESIDES public_pkg fails.
But Not This
```
BEGIN  
   private_pkg.do_this;  
END; 
```
```
ORA-06550: line 2, column 4:
PLS-00904: insufficient privilege to access object PRIVATE_PKG 
```
More Details: https://docs.oracle.com/error-help/db/ora-06550

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database