Restricting Access to Top-Level Procedures in the Same Schema

View All Scripts Login to Run Script

Script Name Restricting Access to Top-Level Procedures in the Same Schema
Description This example shows that the top-level procedure top_protected_proc can only be called by procedure top_trusted_proc in the current schema. The user cannot call top_proctected_proc directly.
Area PL/SQL General
Referenced In Database PL/SQL Language Reference
Contributor Sarah Hirschfeld (Oracle)
Created Monday January 09, 2017

Statement 1

Create a procedure that can only be called by procedure top_trusted_proc.

CREATE OR REPLACE PROCEDURE top_protected_proc   
  ACCESSIBLE BY (PROCEDURE top_trusted_proc)   
AS   
BEGIN   
  DBMS_OUTPUT.put_line('Processed top_protected_proc.');   
END;

Procedure created.

Statement 2

Create the top-level procedure top_trusted_proc calling top_protected_proc in the current schema.

CREATE OR REPLACE PROCEDURE top_trusted_proc AS  
BEGIN  
  DBMS_OUTPUT.put_line('top_trusted_proc calls top_protected_proc');  
  top_protected_proc;  
END;

Procedure created.

Statement 3

The user cannot call top_proctected_proc directly.

EXEC top_protected_proc

ORA-06550: line 1, column 7:
PLS-00904: insufficient privilege to access object TOP_PROTECTED_PROC

More Details: https://docs.oracle.com/error-help/db/ora-06550

Statement 4

The top-level procedure top_protected_proc is accessible by the procedure top_trusted_proc.

EXEC top_trusted_proc

Statement processed.
top_trusted_proc calls top_protected_proc
Processed top_protected_proc.

Additional Information

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