Oracle Magazine, July/August 2017
Database Developer PL SQL The best way to avoid unintended access or actions is to apply the least privilege principle give a user the smallest number of and most narrowly defined privileges on database objects and the data inside those objects Oracle Database has always offered a very robust security mechanism you can access only objects you own or those to which you were granted access Within a PL SQL program unit you can choose the definer rights model users execute your code with your privileges or the invoker rights model users execute your code with their privileges But the granularity of this mechanism operates at the schema level making it difficult to apply the least privilege principle With Oracle Database 12c you can now restrict privileges as tightly as you would like right down to the individual program unit by granting roles to program units not just to schemas Ill explore this feature for both definer rights and invoker rights program units First with definer rights suppose that the HR schema was initially granted just two privileges CREATE SESSION and CREATE PROCEDURE I could then compile the following procedure in HR ORACLE MAGAZINE JULY AUGUST 2017 71 CREATE OR REPLACE PROCEDURE create_ table table_ name_ in IN VARCHAR2 AUTHID DEFINER IS BEGIN EXECUTE IMMEDIATE CREATE TABLE table_ name_ in n NUMBER END
You must have JavaScript enabled to view digital editions.