/*    скрипт создания роли          */
--ogsb

connect &env.B/&env.B@ictdev

set pages 0 linesize 999 feedback off heading off trimspool on termout off echo off verify off
spool c:\Temp\ict\main.sql
--select 'set echo off' from dual;
select 'set echo off' from dual;

select 'prompt **************create role &1.**************;' from dual;
select 'connect &env.B/&env.b@ictdev' from dual;
select 'create role &1.;' from dual;

select 'grant create session, connect, resource,create any synonym to &1.;' from dual;

--1 CREATE ROLE
--ogsb
select 'prompt **************grant tab rpivs from &env.b***************' from dual;

select 'grant '||s.privilege||' to '||'&1.'||' ;' from dba_SYS_privs s  WHERE S.grantee = upper('&env.R');

--select * from all_views v where v.view_name like '%PRIV%';
--ogsb
select 'grant '|| s.privilege||' on ' || s.grantor||'.' || s.table_name ||' to '||'&1.'||' ;'   from all_TAB_privs s WHERE S.grantee = upper('&env.R') and s.grantor=upper('&env.B');


--ogsb
select 'prompt ***************CREATE USER &2. ***************' from dual;
select 'CREATE USER &2. ' from dual;

select 'IDENTIFIED by &2. ' from dual;
select 'DEFAULT TABLESPACE ICT_SYSTEM_DATA ' from dual;
select 'TEMPORARY TABLESPACE TEMP ' from dual;

select 'PROFILE DEFAULT ' from dual;
select 'ACCOUNT UNLOCK; ' from dual;
select 'GRANT CONNECT TO &2.; ' from dual;

select 'ALTER USER &2. DEFAULT ROLE ALL; ' from dual;
select 'ALTER USER &2. ' from dual;
  select 'QUOTA UNLIMITED ON ICT_SYSTEM_INDEX ' from dual;
  select 'QUOTA UNLIMITED ON ICT_SYSTEM_DATA ' from dual;
  select 'QUOTA 512M ON ICT_RESTORED_INDEX ' from dual;
  select 'QUOTA 512M ON ICT_RESTORED_DATA; ' from dual;

select 'prompt ***************grant &1. to &2.***************; ' from dual;
select 'grant &1. to &2.; ' from dual;

select 'prompt ***************grant &env.o objects***************' from dual;
select 'connect &env.O/&env.O@ictdev' from dual;
--ogso 

    select 'grant '|| s.privilege||' on ' || s.grantor||'.' || s.table_name ||' to '||'&1.'||' ;'   from all_TAB_privs s
   WHERE S.grantee =upper('&env.R') and s.grantor=upper('&env.O');

--grant SELECT on &env.O.ORACLE_USER to MUST_SECURE_REPORT_ROLE;
--OGSO
select 'REVOKE SELECT on &env.O.ORACLE_USER FROM &1. ;' from dual;
select 'prompt ***************grant &env.r objects***************' from dual;

select 'connect &env.R/&env.r@ictdev' from dual;

--connect &env.r/&env.r
--run under &env.R

select 'grant select,update,insert  on ' || owner||'.'||table_name || ' to '||'&1.'||' ;' from all_tables where owner=upper('&env.R');

select 'prompt ***************CREATE SYNONYM ***************' from dual;
select 'connect &2./&2.@ictdev' from dual;
--создание синонимов запускается из под пользователя ,которому дается роль MUST_SECURE_REPORT_ROLE

--orep

select 'CREATE SYNONYM ' || table_name || '  FOR '||owner||'.'|| table_name||' ;' from all_tables where owner=upper('&env.R');

select 'CREATE SYNONYM ' || s.synonym_name || '  FOR '||s.owner||'.'|| s.synonym_name||' ;' from all_synonyms s where owner=upper('&env.R');
spool off

--@c:\Temp\ict\create_synonym.sql
--exit
--drop role  &1.
--drop user  &2. cascade