Restricting Access to a Unit Name of Any Kind

View All Scripts Login to Run Script

Script Name Restricting Access to a Unit Name of Any Kind
Description This example shows that if the PL/SQL unit_kind is not specified in the ACCESSIBLE BY clause, then a call from any unit kind is allowed if the unit name matches. There is no compilation error if the unit_kind specified in the ACCESSIBLE BY clause does not match any existing objects. It is possible to define a trigger with the same name as a function. It is recommended to specify the unit_kind.
Area PL/SQL General
Referenced In Database PL/SQL Language Reference
Contributor Sarah Hirschfeld (Oracle)
Created Monday January 09, 2017

Statement 1

If the PL/SQL unit type is not specified in the accessible by clause, then a call from any unit types is allowed if the unit name matches. It is recommended to specify the unit_kind.

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

Procedure created.

Statement 2

CREATE OR REPLACE FUNCTION top_trusted_f RETURN NUMBER AUTHID DEFINER IS  
  FUNCTION g RETURN NUMBER DETERMINISTIC IS  
  BEGIN  
     RETURN 0.5;  
  END g;  
BEGIN  
  protected_proc2;  
  RETURN g() - DBMS_RANDOM.VALUE();  
END top_trusted_f;

Function created.

Statement 3

There is no compilation error if the type specified in the ACCESSIBLE BY clause does not match any existing objects.

CREATE OR REPLACE FUNCTION top_protected_f RETURN NUMBER  
ACCESSIBLE BY (TRIGGER top_trusted_f ) AS  
BEGIN  
   RETURN 0.5;  
END top_protected_f;

Function created.

Statement 4

SELECT top_trusted_f FROM DUAL


TOP_TRUSTED_F
-.13755664019044192036791287861211444954

Processed protected_proc2.

TOP_TRUSTED_F
-.13755664019044192036791287861211444954

Additional Information

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