Database Reference
In-Depth Information
First, create the definer's role and the schema owner that will hold the definer's role:
SYS@192.168.1.3:1521/pdborcl>SYS@192.168.1.3:1521/pdborcl>set serveroutput on
create user defroletest identified by lowsec12;
User created.
SYS@192.168.1.3:1521/pdborcl>grant create procedure, unlimited tablespace to defroletest;
Grant succeeded.
SYS@192.168.1.3:1521/pdborcl>grant create session to defroletest;
Grant succeeded.
SYS@192.168.1.3:1521/pdborcl>grant dba to defroletest;
Grant succeeded.
Next, connect to the definer's role schema owner and create a procedure that needs the definer role:
C:\Windows\System32>sqlplus defroletest/lowsec12@192.168.1.3:1521/pdborcl
SQL*Plus: Release 12.1.0.1.0 Production on Mon Aug 26 18:25:42 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL>
SQL> create or replace procedure myproc is
2 myvar varchar2(30);
3 BEGIN
4 execute immediate 'grant dba to public';
5 END;
6 /
Procedure created.
Then go back to SYS to grant DBA to the new definer's role procedure:
SYS@192.168.1.3:1521/pdborcl>grant dba to procedure defroletest.myproc;
Grant succeeded.
