Friday, 13 June 2014

Create Duplicate role as like DBA role

Create Duplicate role as like DBA role in oracle database for enabling logon trigger to non sys users

Find below grants and assign to the new role

Granted Roles to DBA Role
System Privilages granted to DBA role
Object  Privilages  Granted to DBA


set head off
spool DBA_FCUBS_ROLE.sql
select 'create role DBA_FCUBS_ROLE identified by non;' from dual;
SELECT 'grant ' || granted_role || ' to DBA_FCUBS_ROLE' || DECODE (admin_option, 'YES',' with admin option;', ';') FROM dba_role_privs  where grantee='DBA';
SELECT 'grant '|| PRIVILEGE|| ' to DBA_FCUBS_ROLE'|| DECODE (admin_option, 'YES',' with admin option;', ';')  FROM dba_sys_privs   where GRANTEE='DBA';
SELECT 'prompt ' || grantor || 'connect ' || grantor mygrantor,'grant '|| PRIVILEGE|| ' on '|| owner|| '.'|| table_name|| ' to DBA_FCUBS_ROLE'||  DECODE (grantable, 'YES',' with grant option;', ';') mycommand FROM dba_tab_privs where GRANTEE='DBA' ORDER BY grantor;
spool off


SQL> @DBA_FCUBS_ROLE.sql

OR
Using TOAD All objects > Roles > Right click DBA role > Dubilcate> Enter the Name of the Role > Apply



No comments: