13.1 ACCESSIBLE BY Clause
ACCESSIBLE BY clause restricts access to units and subprograms.
ACCESSIBLE BY clause limits access to a unit or subprogram by other units. The accessor list, also known as the white list, explicitly lists those units which may have access.
ACCESSIBLE BY clause may appear in the declarations of object types, object type bodies, packages, and subprograms.
ACCESSIBLE BYclause can appear in the following SQL statements :
Specifies a stored PL/SQL unit that can invoke the entity.
accessor specifies another PL/SQL entity that may access the entity which includes the
ACCESSIBLE BY clause.
ACCESSIBLE BY clause appears, only entities named in the clause may access the entity in which the clause appears.
accessor may appear more than once in the
ACCESSIBLE BY clause.
ACCESSIBLE BY clause can appear only once in the unit declaration.
An entity named in an
accessor is not required to exist.
When an entity with an
ACCESSIBLE BY clause is invoked, it imposes an additional access check after all other checks have been performed. These checks are:
The invoked unit must include an
accessorwith the same unit_name and unit_kind as the invoking unit.
accessorincludes a schema, the invoking unit must be in that schema.
accessordoes not include a schema, the invoker must be from the same schema as the invoked entity.
Specifies if the unit is a
The unit_kind is optional, but it is recommended to specify it to avoid ambiguity when units have the same name. For example, it is possible to define a trigger with the same name as a function.
ACCESSIBLE BY clause allows access only when the call is direct. The check will fail if the access is through static SQL, DBMS_SQL, or dynamic SQL.
Any call to the initialization procedure of a package specification or package body will be checked against the accessor list of the package specification.
A unit can always access itself. An item in a unit can reference another item in the same unit.
RPC calls to a protected subprogram will always fail, since there is no context available to check the validity of the call, at either compile-time or run-time.
Calls to a protected subprogram from a conditional compilation directive will fail.
Example 13-1 Restricting Access to Top-Level Procedures in the Same Schema
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.
You can view and run this example on Oracle Live SQL at Restricting Access to Top-Level Procedures in the Same Schema
PROCEDURE top_protected_proc ACCESSIBLE BY (PROCEDURE top_trusted_proc) AS BEGIN DBMS_OUTPUT.PUT_LINE('Executed top_protected_proc.'); END; PROCEDURE top_trusted_proc AS BEGIN DBMS_OUTPUT.PUT_LINE('top_trusted_proc calls top_protected_proc'); top_protected_proc; END; EXEC top_trusted_proc; top_trusted_proc calls top_protected_proc Executed top_protected_proc. EXEC top_protected_proc; BEGIN top_protected_proc; END; PLS-00904: insufficient privilege to access object TOP_PROTECTED_PROC
Example 13-2 Restricting Access to a Unit Name of Any Kind
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.
You can view and run this example on Oracle Live SQL at Restricting Access to a Unit Name of Any Kind
PROCEDURE protected_proc2 ACCESSIBLE BY (top_trusted_f) AS BEGIN DBMS_OUTPUT.PUT_LINE('Executed protected_proc2.'); END; FUNCTION top_protected_f RETURN NUMBER ACCESSIBLE BY (TRIGGER top_trusted_f ) AS BEGIN RETURN 0.5; END top_protected_f; 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; SELECT top_trusted_f FROM DUAL; .381773176 1 row selected. Executed protected_proc2.
Example 13-3 Restricting Access to a Stored Procedure
This example shows a package procedure that can only be called by top_trusted_proc procedure. The
ACCESSIBLE BY clause of a subprogram specification and body must match. A compilation error is raised if a call is made to an existing procedure with an
ACCESSIBLE BY clause that does not include this procedure in its accessor list.
You can view and run this example on Oracle Live SQL at Restricting Access to a Stored Procedure
CREATE OR REPLACE PACKAGE protected_pkg AS PROCEDURE public_proc; PROCEDURE private_proc ACCESSIBLE BY (PROCEDURE top_trusted_proc); END; CREATE OR REPLACE PACKAGE BODY protected_pkg AS PROCEDURE public_proc AS BEGIN DBMS_OUTPUT.PUT_LINE('Executed protected_pkg.public_proc'); END; PROCEDURE private_proc ACCESSIBLE BY (PROCEDURE top_trusted_proc) AS BEGIN DBMS_OUTPUT.PUT_LINE('Executed protected_pkg.private_proc'); END; END; CREATE OR REPLACE PROCEDURE top_trusted_proc AS BEGIN DBMS_OUTPUT.PUT_LINE('top_trusted_proc calls protected_pkg.private_proc '); protected_pkg.private_proc; END; Procedure created. EXEC top_trusted_proc; top_trusted_proc calls protected_pkg.private_proc Executed protected_pkg.private_proc EXEC protected_pkg.private_proc PLS-00904: insufficient privilege to access object PRIVATE_PROC
In this chapter:
In other chapters: