Oracle PL/SQL Accessible By Clause
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Header
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.2 to 12.2.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Also important ... there are rumors on the web about SPARC and Solaris being discontinued. Welcome to the age of fake news. There is absolutely not a byte of truth to these irresponsible rumors.
Purpose This PL/SQL clause, new to version 12.1.0.1 is used to restrict the use of PL/SQL objects to only the one included the ACCESSIBLE BY clause.
 
Syntax
Function Demo CREATE OR REPLACE <EDITIONABLE | NONEDITIONABLE> FUNCTION [<schema_name.>]<function_name>
(<parameter_declaration>) RETURN <data type>
ACCESSIBLE BY ([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 Demo CREATE OR REPLACE <EDITIONABLE | NONEDITIONABLE> PACKAGE [<schema_name.>]<package_name>
ACCESSIBLE BY ([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;
/
Procedure Demo CREATE OR REPLACE <EDITIONABLE | NONEDITIONABLE> PROCEDURE [<schema_name.>]<procedure_name>
(<parameter_declaration>)
ACCESSIBLE BY ([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

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
Packages
Procedures
Security
Table Triggers
Types
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2016 Daniel A. Morgan All Rights Reserved