Oracle ACCESSIBLE BY Clause
Versions 12.1 - 19.3

Security Advisory
The ACCESSIBLE BY clause, new to version 12.1 can be used to restrict the use of PL/SQL objects to only the ones included the ACCESSIBLE BY clause.

In 12.2 this clause was enhanced with additional granularity so that it no longer, as in 12.1, applies to an entire PL/SQL package but can now be used to identify specific objects within a PL/SQL package. The list may only restrict access to the subprogram – it cannot expand access.

We strongly recommend that every PL/SQL object in every Oracle Database that is part of an application be considered for an ACCESSIBLE BY clause and that the clause not be used only if there is a compelling reason not to use it.

Read the "How Oracle Works" section below for a detailed explanation of the known vulnerability and how it works.
 
Recommended Security Rules

 NEVER
  • Design or code an application using PL/SQL objects without consider how each object could be misused if called independent of its valid usage as part of the application.
 WITH GREAT CARE
  • Integrate ACCESSIBLE BY clauses into all PL/SQL objects including function, operators, packages, pipelined table functions, and procedures.
 CAUTIONS
  • N/A
 
How Oracle Works
With Default behavior is a risk Oracle's default behavior for PL/SQL objects creates a risk of misuse.

A PL/SQL object owned by a schema, for example UWCLASS can, by default, always be executed by any other object in that same schema or by a user with the credentials required to connect as the owner of that schema.

Thus, if the object, for example the VERIFY_PASSWORD_FUNCTION can be run from SQL*Plus if if not being called by the user's profile during a password change. This default behavior makes it far easier for an object to be called for purposes other than those for which it was intended and makes it far easier for someone to reverse engineer the objects internal functionality.

When the ACCESSIBLE BY clause is in place an PL/SQL object, or in the case of a package objects within a PL/SQL package can only be called if they are explicitly listed as an approved originator for that call.
 
ACCESSIBLE BY Information
Documented Yes
Exceptions
Error Code Reason
ORA-06553 PLS-904: insufficient privilege to access object <object_name>
PLS-00904 Insufficient privilege to access object <object_name>
First Available 12.1
Security Model Eliminates the ability for a PL/SQL object to be called from the command line or from another object not explicitly authorized to execute the call.
 
Syntax
Function Demo CREATE OR REPLACE <EDITIONABLE | NONEDITIONABLE> FUNCTION [<schema_name.>]<function_name>
(<parameter_declaration>) RETURN <data type>
ACCESSIBLE BY (<comma_delimited_accessor_list>) [unit_kind][schema_name.]<unit_name>
AUTHID <DEFINER | CURRENT USER> IS
 <declarations>
BEGIN
  <function_code>
EXCEPTION
  <exception_handlers>
END <function_name>;
/
CREATE OR REPLACE FUNCTION test_src RETURN PLS_INTEGER
ACCESSIBLE BY (FUNCTION test_yes) AUTHID DEFINER IS
BEGIN
  RETURN 42;
END test_src;
/

CREATE OR REPLACE FUNCTION test_yes RETURN PLS_INTEGER AUTHID DEFINER IS
BEGIN
  RETURN test_src;
END test_yes;
/

CREATE OR REPLACE FUNCTION test_no RETURN PLS_INTEGER AUTHID DEFINER IS
BEGIN
  RETURN test_src;
END test_no;
/

Warning: Function created with compilation errors.

SQL> show err
Errors for FUNCTION TEST_NO:

LINE/COL ERROR
-------- ------
3/3      PL/SQL: Statement ignored
3/10     PLS-00904: insufficient privilege to access object TEST_SRC
Function by Trigger Demo CREATE OR REPLACE <EDITIONABLE | NONEDITIONABLE> FUNCTION [<schema_name.>]<function_name>
(<parameter_declaration>) RETURN <data type>
ACCESSIBLE BY (TRIGGER [schema_name.]<trigger_name>) <DEFINER | CURRENT USER> IS
TBD
Package Demos CREATE OR REPLACE <EDITIONABLE | NONEDITIONABLE> PACKAGE [<schema_name.>]<package_name>
ACCESSIBLE BY (<comma_delimited_accessor_list>)
[unit_kind][schema_name.]<unit_name> AUTHID <DEFINER | CURRENT USER> IS
 <declarations>
END <package_name>;
/
CREATE OR REPLACE PACKAGE uw_constants ACCESSIBLE BY (PROCEDURE testproc) AUTHID DEFINER IS
 cStartDate  CONSTANT DATE := TO_DATE('07-JAN-2012');
 cInstructor CONSTANT VARCHAR2(30) := 'A. Morgan';
 cPi         CONSTANT NUMBER(8,7) := 3.1415926;
END uw_constants;
/

CREATE OR REPLACE PROCEDURE testproc AUTHID DEFINER IS
 x VARCHAR2(20);
BEGIN
  x := 'Daniel ' || uw_constants.cInstructor;
  dbms_output.put_line(x);
END;
/
CREATE OR REPLACE PACKAGE uw_accessible_by ACCESSIBLE BY (PROCEDURE testproc) AUTHID DEFINER IS
 public_object_proc;
 private_object_proc ACCESSIBLE BY(FUNCTION encrypt_string);
END uw_accessible_by;
/

CREATE OR REPLACE PACKAGE BODY uw_accessible_by IS
 PROCEDURE public_object_proc IS
 BEGIN
   dbms_output.put_line('May be executed from the command line or by any object');
 END public_object_proc;

 PROCEDURE private_object_proc ACCESSIBLE BY (PROCEDURE testproc) IS
 BEGIN
   dbms_output.put_line('You will never see this message except if you create something named TESTPROC to call it');
 END private_object_proc;
END uw_accessible_by;
/
Procedure Demos CREATE OR REPLACE <EDITIONABLE | NONEDITIONABLE> PROCEDURE [<schema_name.>]<procedure_name>
(<parameter_declaration>)
ACCESSIBLE BY (<comma_delimited_accessor_list>)
[unit_kind][schema_name.]<unit_name> AUTHID <DEFINER | CURRENT USER> IS
 <declarations>
BEGIN
  <procedure_code>
EXCEPTION
  <exception_handlers>
END <procedure_name>;

/
TBD
Type Create Demo CREATE OR REPLACE <EDITIONABLE | NONEDITIONABLE> TYPE [<schema_name.>]<type_name>
ACCESSIBLE BY ([unit_kind][schema_name.]<unit_name>) AUTHID <DEFINER | CURRENT USER> AS OBJECT(
<data type>);

...
CREATE OR REPLACE TYPE ssn_t ACCESSIBLE BY (FUNCTION testfunc) AUTHID DEFINER
AS OBJECT (ssn_type CHAR(11));
/
Type Alter Demo ALTER TYPE [schema_name.]<type_name>
ACCESSIBLE BY ([unit_kind][schema_name.]<unit_name>);
CREATE OR REPLACE TYPE ssn_t AUTHID DEFINER AS OBJECT (
ssn_type CHAR(11));
/

ALTER TYPE ssn_t
REPLACE ACCESSIBLE BY (FUNCTION testfunc) AUTHID DEFINER AS OBJECT (
ssn_type CHAR(11));
View Demo

View respects the ACCESSIBLE By Clause
CREATE OR REPLACE FUNCTION test_src(obj# IN NUMBER) RETURN PLS_INTEGER
ACCESSIBLE BY (VIEW no_work) AUTHID DEFINER IS
BEGIN
  RETURN 42;
END test_src;
/
SQL> sho err
Errors for FUNCTION TEST_SRC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/16 PLS-00103: Encountered the symbol "VIEW" when expecting one of
the following:
function package procedure type <an identifier>
<a double-quoted delimited-identifier> trigger
The symbol "VIEW" was ignored.


CREATE OR REPLACE FUNCTION test_src(obj# IN NUMBER) RETURN PLS_INTEGER
ACCESSIBLE BY (FUNCTION no_work) AUTHID DEFINER IS
BEGIN
  RETURN 42;
END test_src;
/

CREATE OR REPLACE VIEW no_work AS
SELECT test_src(object_id) no_work_objid
FROM dba_objects;

SELECT *
FROM no_work
WHERE rownum < 4;
SELECT *
       *
ERROR at line 1:
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-904: insufficient privilege to access object TEST_SRC
Function Based Index

FBIs respects the ACCESSIBLE By Clause
CREATE OR REPLACE FUNCTION test_src(obj# IN NUMBER) RETURN PLS_INTEGER
ACCESSIBLE BY (FUNCTION no_work) DETERMINISTIC AUTHID DEFINER IS
BEGIN
  RETURN 42;
END test_src;
/

CREATE TABLE no_work_tab AS
SELECT object_id
FROM dba_objects;

CREATE INDEX fbi_no_work_fbi
ON no_work_tab(test_src(object_id));
               *
ERROR at line 2:
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-904: insufficient privilege to access object TEST_SRC

Related Topics
Functions
Operators
Packages
Pipelined Table Functions
Procedures
Table Triggers
Views