Sunday, March 8, 2015

PRAGMA RESTRICT_REFERENCES for Restricting Package security.

PRAGMA RESTRICT_REFERENCES is an instruction or a hint to the compiler which is processed at compile time, not at runtime, it used to control the side effects of PL/SQL Subprograms. Every PL/SQL Subprograms must follow some rules in terms of transaction control and security. 


PRAGMA RESTRICT_REFERENCES mainly used in PL/SQL Package and it ensures that a subprogram does not read or write database tables or package variables. 

PRAGMA RESTRICT_REFERENCES can appear only in a package specification or object type specification. Typically, this pragma is specified for functions. If a function calls procedures, specify the pragma for those procedures also.

From Oracle 11g, PRAGMA RESTRICT REFERENCES is deprecated. Oracle recommends using DETERMINISTIC and PARALLEL_ENABLE instead of RESTRICT REFERENCES.

  • RNDS – Read No Database State. function not to read or query tables
  • RNPS – Read No Package State. function not to read or reference package variables
  • WNDS – Write No Database State. function not modify database tables
  • WNPS – Write No Package State. function not modify package variables
  • TRUST – function can be trusted not to violate one or more rules. Used only when C or JAVA routines are called from PL/SQL.
Without using pragma in package, package successfully complied but it may result runtime error, and developers need to change the code.

ORA-14551: cannot perform a DML operation inside a query

Using pragma in package, package will be successfully complied with compile time error, which leads developers need to change the code.

PLS-00452: Subprogram ‘send_emp_alloc’ violates its associated pragma
  
CREATE OR REPLACE PACKAGE pkg_transfergoods
IS
function send_emp_alloc(p_empno integer) return number;    
PRAGMA restrict_references(send_emp_alloc, RNDS, RNPS, WNDS, WNPS);
END pkg_transfergoods;
/

To apply PRAGMA restrict_references functionalities on all package functions – sub programs by using Keyword = DEFAULT to all of the sub programs inside the package. It applies (WNDS – Write No Database State to all package not modify database tables) to all database tables which are used in package.

CREATE OR REPLACE PACKAGE pkg_emp_alloc
IS
function send_emp_alloc(p_empno integer) return number;
function get_emp_name(p_empno integer) return varchar2;
PRAGMA restrict_references(DEFAULT, WNDS);
END pkg_emp_alloc;
/