SET TERMOUT ON
SET ECHO OFF
SET VERIFY OFF
SET HEADING OFF
set linesize 300
set pagesize 1000
 
ACCEPT Server PROMPT 'Enter a DATABASE alias/connection string (MAGICASH): ' DEFAULT MAGICASH
ACCEPT SYSpsw PROMPT 'Enter SYS PASSWORD: ' DEFAULT NCTSYS
 

CONNECT SYS/&SYSpsw@&Server AS SYSDBA




spool rol_user_get_ostal.sql


--  Создание ролей

-- Create the role 

select 'create role ' || GRANTED_ROLE || ';' "1"
  from (select distinct (GRANTED_ROLE)
          from sys.dba_role_privs
         where grantee in
               (SELECT t.USERNAME
                  FROM DBA_USERS t
                 WHERE ACCOUNT_STATUS = 'OPEN'
                   and DEFAULT_TABLESPACE not in ('SYSTEM', 'SYSAUX'))
           and GRANTED_ROLE not in
               ('CONNECT', 'DBA', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE')
         order by GRANTED_ROLE asc);

-- Grant/Revoke object privileges 

select 'grant ' || p.privilege || ' on ' || p.table_name || ' to ' ||
       p.grantee || ';' "1"
  from sys.all_tab_privs p
 where p.grantee in
       (select distinct (GRANTED_ROLE)
          from sys.dba_role_privs
         where grantee in
               (SELECT t.USERNAME
                  FROM DBA_USERS t
                 WHERE ACCOUNT_STATUS = 'OPEN'
                   and DEFAULT_TABLESPACE not in ('SYSTEM', 'SYSAUX'))
           and GRANTED_ROLE not in
               ('CONNECT', 'DBA', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE'))
 order by p.grantee, p.table_schema, p.table_name;

-- Grant/Revoke role privileges 

select 'grant ' || GRANTED_ROLE || ' to ' || GRANTEE || ' ' ||
       decode(ADMIN_OPTION, 'YES', ' with admin option') || ';' "1"
  from sys.dba_role_privs
 where grantee in
       (select distinct (GRANTED_ROLE)
          from sys.dba_role_privs
         where grantee in
               (SELECT t.USERNAME
                  FROM DBA_USERS t
                 WHERE ACCOUNT_STATUS = 'OPEN'
                   and DEFAULT_TABLESPACE not in ('SYSTEM', 'SYSAUX'))
           and GRANTED_ROLE not in
               ('CONNECT', 'DBA', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE'))
 order by granted_role;

-- Grant/Revoke system privileges 

select 'grant ' || PRIVILEGE || ' to ' || GRANTEE || ' ' ||
       decode(ADMIN_OPTION, 'YES', ' with admin option') || ';' "1" 
  from sys.dba_sys_privs
 where grantee in
       (select distinct (GRANTED_ROLE)
          from sys.dba_role_privs
         where grantee in
               (SELECT t.USERNAME
                  FROM DBA_USERS t
                 WHERE ACCOUNT_STATUS = 'OPEN'
                   and DEFAULT_TABLESPACE not in ('SYSTEM', 'SYSAUX'))
           and GRANTED_ROLE not in
               ('CONNECT', 'DBA', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE'))
 order by GRANTEE, privilege;

---  Выборка и создание пользователей  кроме MAGICASH5
-- Create the user

SELECT 'create user ' || t.USERNAME || ' identified by ' || t.USERNAME ||
       ' default tablespace ' || DEFAULT_TABLESPACE ||
       ' temporary tablespace ' || TEMPORARY_TABLESPACE || '  profile ' ||
       PROFILE || ';' "1"
  FROM DBA_USERS t
 WHERE ACCOUNT_STATUS = 'OPEN'
   and DEFAULT_TABLESPACE not in ('SYSTEM', 'SYSAUX')
   and t.USERNAME != 'MAGICASH5'
 order by t.USERNAME asc;

---  Создание пользователя MAGICASH5
-- Create the user

SELECT 'create user ' || t.USERNAME || ' identified by ' || 'NCTMAGICASH5' ||
       ' default tablespace ' || DEFAULT_TABLESPACE ||
       ' temporary tablespace ' || TEMPORARY_TABLESPACE || '  profile ' ||
       PROFILE || ';' "1"
  FROM DBA_USERS t
 WHERE ACCOUNT_STATUS = 'OPEN'
   and DEFAULT_TABLESPACE not in ('SYSTEM', 'SYSAUX')
   and t.USERNAME = 'MAGICASH5'
 order by t.USERNAME asc;

---  Присвоение привилегий на объекты пользователей   
--- Grant/Revoke object privileges

select 'grant ' || PRIVILEGE || ' on ' || TABLE_SCHEMA || '.' || TABLE_NAME ||
       ' to ' || GRANTEE || ' ' ||
       decode(GRANTABLE, 'YES', ' with grant option') || ';' "1"
  from sys.all_tab_privs p
 where p.grantee in
       (SELECT t.USERNAME
          FROM DBA_USERS t
         WHERE ACCOUNT_STATUS = 'OPEN'
           and DEFAULT_TABLESPACE not in ('SYSTEM', 'SYSAUX'))
--and GRANTEE = 'MAGICASH5'
 order by GRANTEE asc, TABLE_NAME asc;

---  Присвоение ролей
-- Grant/Revoke role privileges

select 'grant ' || GRANTED_ROLE || ' to ' || GRANTEE || ' ' ||
       decode(ADMIN_OPTION, 'YES', ' with admin option') || ';' "1"
  from sys.dba_role_privs
 where grantee in
       (SELECT t.USERNAME
          FROM DBA_USERS t
         WHERE ACCOUNT_STATUS = 'OPEN'
           and DEFAULT_TABLESPACE not in ('SYSTEM', 'SYSAUX'))
 order by GRANTEE asc, GRANTED_ROLE asc;

---  Присвоение системных привилегий
--- Grant/Revoke system privileges

select  'grant ' || privilege || ' to ' || GRANTEE || ' ' ||
       decode(ADMIN_OPTION, 'YES', ' with admin option') || ';' "1"
  from sys.dba_sys_privs
 where grantee in
       (SELECT t.USERNAME
          FROM DBA_USERS t
         WHERE ACCOUNT_STATUS = 'OPEN'
           and DEFAULT_TABLESPACE not in ('SYSTEM', 'SYSAUX'))
 order by GRANTEE asc, privilege asc;



spool off
exit



SET TERMOUT ON
SET ECHO ON
EXIT
