Which has the higher priority in your organization: Deploying a new database or securing the ones you already have?
Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Purpose
This PL/SQL clause, new to version 12.1 is used to restrict the use of PL/SQL objects to only the one 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 package but can be used to identify specific objects within a PL/SQL package. The list may only restrict access to the subprogram, it cannot
be used to expand access.
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.
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 (<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 Demo
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
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
