| 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
|
| |
| 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 |