GRANTS to USER

-- gen_drop_objects.sql

   set pages 0 feedb off
   accept inuser char prompt 'Enter Userid: '
   -- spool run_drop_objects_&&inuser..out
   spool run_drop_objects_MAXIMO.out
   SELECT 'DROP ' || OBJECT_TYPE || ' ' || owner||'.'||OBJECT_NAME ||' CASCADE CONSTRAINTS;'
   FROM   dba_objects
   WHERE OBJECT_TYPE = 'TABLE' and owner = upper('&inuser')
   union all
   select 'drop '||case
            when object_type='PACKAGE BODY' then 'PACKAGE'
            else object_type
          end
     ||' '||owner||'.'||object_name||' ;'
   from dba_objects
   where owner = upper('&inuser')
   /
   prompt purge dba_recyclebin
   prompt /
   
   spool off
   
   
-- gen_rdonly_grants_to_role.sql

set pages 0 lines 120 feedback off verify off
accept ToRole prompt 'Grants to What Role? '
accept ObjOwner prompt 'From What Owner? '
spool gen_rdonly_grants_to_role.out
select 'set echo on' from dual;
select 'create role &&ToRole;' from dual;
select 'connect &&ObjOwner' from dual;
select 'grant '||case object_type when 'TABLE' then 'select '
                                  when 'VIEW'  then 'select '
                 end
  ||'on '||object_name||' to &&ToRole;'
from dba_objects
where owner=upper('&&ObjOwner')
  and object_type in ('TABLE','VIEW')
/
spool off
set pages 30 feedback on

--  gen_grants_to_role.sql

set pages 0 lines 120 feedback off verify off
accept ToRole prompt 'Grants to What Role? '
accept ObjOwner prompt 'From What Owner? '
spool gen_grants_to_role.out
select 'set echo on' from dual;
select 'create role &&ToRole;' from dual;
select 'connect &&ObjOwner' from dual;
select 'grant '||case object_type when 'TABLE' then 'select, insert, update, delete '
                                  when 'VIEW'  then 'select '
                                  when 'SEQUENCE' then 'select '
                                  when 'PROCEDURE' then 'execute '
                                  when 'PACKAGE' then 'execute '
                 end
  ||'on '||object_name||' to &&ToRole;'
from dba_objects
where owner=upper('&&ObjOwner')
  and object_type in ('TABLE','VIEW','SEQUENCE','PROCEDURE','PACKAGE')
/
spool off
set pages 30 feedback on


-- gen_new_grants.sql

set feedback off pages 0 lines 100
spool run_new_grants.out
select 'create synonym SCOTT_USER.'||object_name||' for '||owner||'.'||object_name||';'
from dba_objects
where owner='SCOTT_OWN' and object_name in (
select object_name from dba_objects where owner='SCOTT_OWN' and object_type in ('PROCEDURE','PACKAGE','FUNCTION','TABLE')
minus
select synonym_name from dba_synonyms where table_owner='SCOTT_OWN')
/


select case
       when object_type in ('TABLE') then
       'grant select,insert,update,delete on '||owner||'.'||object_name||' to SCOTT_USER;'
       when object_type in ('VIEW','SEQUENCE') then
       'grant select on '||owner||'.'||object_name||' to SCOTT_USER;'
       else
       'grant execute on '||owner||'.'||object_name||' to SCOTT_USER;'
       end
from dba_objects
where owner='SCOTT_OWN' and object_name in (
select object_name from dba_objects where owner='SCOTT_OWN' and object_type in ('TABLE','VIEW','SEQUENCE','PROCEDURE','PACKAGE','FUNCTION')
minus
select distinct table_name from dba_tab_privs where owner='SCOTT_OWN' and grantee='CSX_SCOTT_USER_ROLE')
/
spool off
set feedback on pages 24 lines 140