-- must look at roles from gallas or melanson account
--  
--  sqlplus melanson@d0ofdev1
--  

set linesize 100;
set pagesize 80;
column GRANTOR format a18;
column GRANTEE format a18;
column TABLE_NAME format a18;
column PRIVILEGE format a18;
column TABLE_SCHEMA format a18;

column owner format a18;
column SYNONYM_NAME format a18;
column TABLE_OWNER format a18;
column TABLE_NAME format a18;
column DB_LINK  format a18;

select * from dba_role_privs where grantee='MELANSON';

select * from dba_role_privs where grantee='STRAUSS';


desc all_tab_privs
select * from all_tab_privs where grantee='GALLAS';

select unique GRANTOR from all_tab_privs;
select unique GRANTEE       from all_tab_privs;
select unique TABLE_SCHEMA  from all_tab_privs;
select unique TABLE_NAME    from all_tab_privs;
select unique PRIVILEGE     from all_tab_privs; 
-- ALTER,DEBUG,DELETE,EXECUTE,INSERT,REFERENCES,SELECT,UNDER,UPDATE

select unique GRANTABLE     from all_tab_privs;
-- YES,NO
select unique HIERARCHY     from all_tab_privs;
-- NO

desc all_tab_privs;
--  ----------------------------------------- -------- ----------------------------
--  Name                                      Null?    Type
--  ----------------------------------------- -------- ----------------------------
--  GRANTOR                                   NOT NULL VARCHAR2(30)
--  GRANTEE                                   NOT NULL VARCHAR2(30)
--  TABLE_SCHEMA                              NOT NULL VARCHAR2(30)
--  TABLE_NAME                                NOT NULL VARCHAR2(30)
--  PRIVILEGE                                 NOT NULL VARCHAR2(40)
--  GRANTABLE                                          VARCHAR2(3)
--  HIERARCHY                                          VARCHAR2(3)

select unique grantor from all_tab_privs;
-- ------------------------------
-- GRANTOR
-- ------------------------------
-- OUTLN
-- RELREQ_OWNER
-- SAMINT
-- SPEAKERDB
-- SYS
-- SYSTEM

select GRANTOR,GRANTEE,TABLE_NAME,PRIVILEGE 
	from all_tab_privs 
	where grantor = 'RELREQ_OWNER';
-- ------------------ ------------------ ------------------ ------------------
-- GRANTOR            GRANTEE            TABLE_NAME         PRIVILEGE
-- ------------------ ------------------ ------------------ ------------------
-- RELREQ_OWNER       RR_UPDATE          RR_ID_SEQ          SELECT
-- RELREQ_OWNER       RR_MASTER          RR_ID_SEQ          SELECT
-- RELREQ_OWNER       RR_READ            RELREQS            SELECT
-- RELREQ_OWNER       RR_UPDATE          RELREQS            INSERT
-- RELREQ_OWNER       RR_UPDATE          RELREQS            SELECT
-- RELREQ_OWNER       RR_UPDATE          RELREQS            UPDATE
-- RELREQ_OWNER       RR_MASTER          RELREQS            DELETE
-- RELREQ_OWNER       RR_MASTER          RELREQS            INSERT
-- RELREQ_OWNER       RR_MASTER          RELREQS            SELECT
-- RELREQ_OWNER       RR_MASTER          RELREQS            UPDATE
-- RELREQ_OWNER       RELREQ_USER_ROLE   RELREQS            DELETE
-- RELREQ_OWNER       RELREQ_USER_ROLE   RELREQS            INSERT
-- RELREQ_OWNER       RELREQ_USER_ROLE   RELREQS            SELECT
-- RELREQ_OWNER       RELREQ_USER_ROLE   RELREQS            UPDATE



desc dba_role_privs;
--  ----------------------------------------- -------- ----------------------------
--  Name                                      Null?    Type
--  ----------------------------------------- -------- ----------------------------
--  GRANTEE                                            VARCHAR2(30)
--  GRANTED_ROLE                              NOT NULL VARCHAR2(30)
--  ADMIN_OPTION                                       VARCHAR2(3)
--  DEFAULT_ROLE                                       VARCHAR2(3)

select GRANTEE,GRANTED_ROLE,ADMIN_OPTION,DEFAULT_ROLE 
	from dba_role_privs
	order by GRANTEE;

select GRANTEE,GRANTED_ROLE,ADMIN_OPTION,DEFAULT_ROLE 
	from dba_role_privs
	where GRANTEE like 'REL%'
	order by GRANTEE;

-- ------------------------------ ------------------------------ --- ---
-- GRANTEE                        GRANTED_ROLE                   ADM DEF
-- ------------------------------ ------------------------------ --- ---
-- RELREQ_OWNER                   CONNECT                        NO  YES
-- RELREQ_OWNER                   RESOURCE                       NO  YES
-- RELREQ_USER                    CONNECT                        NO  YES
-- RELREQ_USER                    D0_READ_ONLY                   NO  YES
-- RELREQ_USER                    RELREQ_USER_ROLE               NO  YES
-- REL_UPDATE                     CONNECT                        NO  YES
-- REL_UPDATE                     RR_UPDATE                      NO  YES
-- REL_UPDATE                     SELECT_CATALOG_ROLE            NO  YES

select GRANTEE,GRANTED_ROLE,ADMIN_OPTION,DEFAULT_ROLE 
	from dba_role_privs
	where granted_role like 'SB%';
-- ------------------------------ ------------------------------ --- ---
-- GRANTEE                        GRANTED_ROLE                   ADM DEF
-- ------------------------------ ------------------------------ --- ---
-- STRAUSS                        SB_WRITE                       NO  YES
-- STRAUSS                        SB_INSERT_ALL                  NO  YES
-- D0_READ_ONLY                   SB_READ                        NO  YES
-- SPEAKERDB_READ                 SB_READ                        NO  YES
-- SPEAKERDB_UPDATE               SB_UPDATE_PEOPLE               NO  YES

select GRANTEE,GRANTED_ROLE,ADMIN_OPTION,DEFAULT_ROLE 
	from dba_role_privs
	where granted_role like 'TRIG%';

select GRANTEE,GRANTED_ROLE,ADMIN_OPTION,DEFAULT_ROLE 
	from dba_role_privs
	where granted_role like 'TM%';

pause

select GRANTEE,GRANTED_ROLE,ADMIN_OPTION,DEFAULT_ROLE
	from dba_role_privs
	where grantee='GALLAS';
select GRANTEE,GRANTED_ROLE,ADMIN_OPTION,DEFAULT_ROLE
	from dba_role_privs
	where grantee='SPEAKERDB';
select GRANTEE,GRANTED_ROLE,ADMIN_OPTION,DEFAULT_ROLE
	from dba_role_privs
	where grantee='SPEAKERDB_READ';
select GRANTEE,GRANTED_ROLE,ADMIN_OPTION,DEFAULT_ROLE
	from dba_role_privs
	where grantee='SPEAKERDB_';

select synonym_name,table_owner,table_name
	from all_synonyms where table_owner like '%TRIGGER_OWNER%'
	order by table_name,synonym_name;
pause

select GRANTEE,GRANTED_ROLE,ADMIN_OPTION,DEFAULT_ROLE 
	from dba_role_privs
	where granted_role like 'TRIG%';
pause
-- ================================================================
desc all_synonyms;
--  ----------------------------------------- -------- ----------------------------
--   Name                                      Null?    Type
--  ----------------------------------------- -------- ----------------------------
--  OWNER                                     NOT NULL VARCHAR2(30)
--  SYNONYM_NAME                              NOT NULL VARCHAR2(30)
--  TABLE_OWNER                                        VARCHAR2(30)
--  TABLE_NAME                                NOT NULL VARCHAR2(30)
--  DB_LINK                                            VARCHAR2(128)


select unique owner from all_synonyms;

select owner,SYNONYM_NAME,TABLE_OWNER,TABLE_NAME,DB_LINK 
	from all_synonyms
	where TABLE_OWNER='RELREQ_OWNER';
-- ------------------ ------------------ ------------------ ------------------ ------------------
-- OWNER              SYNONYM_NAME       TABLE_OWNER        TABLE_NAME         DB_LINK
-- ------------------ ------------------ ------------------ ------------------ ------------------
-- PUBLIC             RELREQS            RELREQ_OWNER       RELREQS
-- PUBLIC             RR_ID_SEQ          RELREQ_OWNER       RR_ID_SEQ
-- PUBLIC             RELREQ             RELREQ_OWNER       RELREQS

select SYNONYM_NAME,TABLE_OWNER,TABLE_NAME,DB_LINK 
	from user_synonyms;