Oracle PL/SQL Accessible By Clause
Version 12.1.0.1

General Information
Library Note This page has been updated for Oracle release 12cR1 and shown to be compatible in the Container DB.
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

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-2014 Daniel A. Morgan All Rights Reserved