-- Get the database version
select * from v$version;
select * from role_role_privs;
select * from role_tab_privs;
-- Read all global roles present in DB
select role from dba_roles where password_required='GLOBAL';
-- Global roles to local roles mapping
select * from dba_role_privs where grantee in (select role from dba_roles where password_required='GLOBAL');
select * from dba_role_privs where grantee in ('SS_D2DB0156_INTERNAL');
-- Object privileges assigned to the user
desc DBA_TAB_PRIVS;
select * from dba_tab_privs;
select * from dba_tab_privs where grantee='GUSER_SYSDBA_D2DB0156';
select distinct grantee,table_name, privilege from DBA_TAB_PRIVS where grantee in (select granted_role from dba_role_privs where grantee in (select role from dba_roles where password_required='GLOBAL'));
select distinct grantee,table_name, privilege from DBA_TAB_PRIVS where grantee in ('LROLE_EASI_ETL');
-- System Privileges assigned to a user/Role
desc dba_sys_privs;
select * from dba_sys_privs where grantee='NAGS_CONSOLIDATION_SHARED_SCH';
select * from dba_sys_privs where grantee='GROLE_EASI_ETL';
--Grant local role to global role
grant LROLE_EASI_ETL to GROLE_EASI_ETL;
--Grant connect to GUSER_SYSDBA_D2DB0156
grant CONNECT to PROXY_USER_EUSTEST;
grant CREATE TABLE to GLOBAL_EXC_SCHEMA_TEST;
-- Global users/Shared schemas created in DB
select * from dba_users where password='GLOBAL';
select * from dba_users where username='SS_D3DB0156_INTERNAL';
select distinct password from dba_users;
-- Read the roles of the user session
select * from session_roles;
-- Read privileges of the user session
select * from session_privs;
-- Read the schmea user logged into
select sys_context('userenv','db_name'), sys_context('userenv','session_user') from dual;
SELECT sys_context('USERENV', 'CURRENT_SCHEMA') FROM DUAL;
-- Creating exclusive schema
CREATE USER global_exc_schema_test1 IDENTIFIED GLOBALLY;
drop user global_exc_schema_test1;
-- Create proxy user for EUS
CREATE USER proxy_user_eustest identified by abcd1234;
commit;
drop user proxy_user_eustest;
-- Grant connect through enterprise users to proxy user
ALTER USER global_exc_schema_test1 GRANT CONNECT THROUGH ENTERPRISE USERS;
select * from v$version;
select * from role_role_privs;
select * from role_tab_privs;
-- Read all global roles present in DB
select role from dba_roles where password_required='GLOBAL';
-- Global roles to local roles mapping
select * from dba_role_privs where grantee in (select role from dba_roles where password_required='GLOBAL');
select * from dba_role_privs where grantee in ('SS_D2DB0156_INTERNAL');
-- Object privileges assigned to the user
desc DBA_TAB_PRIVS;
select * from dba_tab_privs;
select * from dba_tab_privs where grantee='GUSER_SYSDBA_D2DB0156';
select distinct grantee,table_name, privilege from DBA_TAB_PRIVS where grantee in (select granted_role from dba_role_privs where grantee in (select role from dba_roles where password_required='GLOBAL'));
select distinct grantee,table_name, privilege from DBA_TAB_PRIVS where grantee in ('LROLE_EASI_ETL');
-- System Privileges assigned to a user/Role
desc dba_sys_privs;
select * from dba_sys_privs where grantee='NAGS_CONSOLIDATION_SHARED_SCH';
select * from dba_sys_privs where grantee='GROLE_EASI_ETL';
--Grant local role to global role
grant LROLE_EASI_ETL to GROLE_EASI_ETL;
--Grant connect to GUSER_SYSDBA_D2DB0156
grant CONNECT to PROXY_USER_EUSTEST;
grant CREATE TABLE to GLOBAL_EXC_SCHEMA_TEST;
-- Global users/Shared schemas created in DB
select * from dba_users where password='GLOBAL';
select * from dba_users where username='SS_D3DB0156_INTERNAL';
select distinct password from dba_users;
-- Read the roles of the user session
select * from session_roles;
-- Read privileges of the user session
select * from session_privs;
-- Read the schmea user logged into
select sys_context('userenv','db_name'), sys_context('userenv','session_user') from dual;
SELECT sys_context('USERENV', 'CURRENT_SCHEMA') FROM DUAL;
-- Creating exclusive schema
CREATE USER global_exc_schema_test1 IDENTIFIED GLOBALLY;
drop user global_exc_schema_test1;
-- Create proxy user for EUS
CREATE USER proxy_user_eustest identified by abcd1234;
commit;
drop user proxy_user_eustest;
-- Grant connect through enterprise users to proxy user
ALTER USER global_exc_schema_test1 GRANT CONNECT THROUGH ENTERPRISE USERS;
