-- must look at roles from gallas or melanson account
--  
--  sqlplus melanson@d0ofdev1
--  
-- ##################################################################
-- E. Gallas Feb 17, 2006
-- look at account assigned to a specific role
-- (this example Linda needs MUON_PDT_MAINTAIN Role
--
select * from dba_role_privs where GRANTED_ROLE='MUON_PDT_MAINTAIN'; 

select * from dba_role_privs where GRANTED_ROLE like '%REQ%';

desc db_roles;


-- ##################################################################
set heading off;
set linesize 120;
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 a20;
column DB_LINK  format a18;

column GRANTEE         format a25;
column GRANTED_ROLE    format a20;
column ADMIN_OPTION    format a12;
column DEFAULT_ROLE    format a12;

column INDEX_NAME    format a30;
column INDEX_TYPE    format a12;
column TABLESPACE_NAME    format a12;

select * from dba_role_privs where grantee='MELANSON';
GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
MELANSON                       CONNECT                        NO  YES
MELANSON                       RESOURCE                       NO  YES
MELANSON                       RR_MASTER                      NO  YES
MELANSON                       D0_READ_ONLY                   NO  YES

select * from dba_role_privs where grantee='STRAUSS';
GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
STRAUSS                        CONNECT                        NO  YES
STRAUSS                        RESOURCE                       NO  YES
STRAUSS                        SB_WRITE                       NO  YES
STRAUSS                        D0_READ_ONLY                   NO  YES
STRAUSS                        SB_INSERT_ALL                  NO  YES


desc all_tab_privs
 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 * from all_tab_privs where grantee='GALLAS';
no rows selected

select unique TABLE_NAME    from all_tab_privs where TABLE_NAME like '%REQ%';

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%';
GRANTEE              GRANTED_ROLE         ADMIN_OPTION DEFAULT_ROLE
-------------------- -------------------- ------------ ------------
BILLL                TRIGGERDB_READ       NO           YES
DIEHL                TRIGGERDB_READ       NO           YES
TOOLE                TRIGGERDB_READ       NO           YES
GALLAS               TRIGGERDB_MASTER     NO           YES
D0_READ_ONLY         TRIGGERDB_READ       NO           YES
TRIGGER_READ         TRIGGERDB_READ       NO           YES
TRIGGER_INSERT       TRIGGERDB_INSERT     NO           YES
TRIGGER_DBSERVER     TRIGGERDB_DBSERVER   NO           YES

select GRANTEE,GRANTED_ROLE,ADMIN_OPTION,DEFAULT_ROLE 
	from dba_role_privs
	where granted_role like 'TM%';
GRANTEE              GRANTED_ROLE         ADMIN_OPTION DEFAULT_ROLE
-------------------- -------------------- ------------ ------------
BILLL                TM_PERMANENT         NO           YES
DIEHL                TM_PERMANENT         NO           YES
JESIK                TM_PERMANENT         NO           YES
TOOLE                TM_PERMANENT         NO           YES
BLEVAN               TM_PERMANENT         NO           YES
GALLAS               TM_PERMANENT         NO           YES
TWYATT               TM_PERMANENT         NO           YES



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;


-----------------------------------------------------------------
-- trying to find luminosity application definitions:
select unique username from all_users;

select unique username from all_users where username like '%LUM%';
-- found user LUMINOSITY

desc all_tables;
select unique owner from all_tables;

select OWNER,TABLE_NAME,TABLESPACE_NAME from all_tables where OWNER='LUMINOSITY';

LUMINOSITY         BY_TICKS             LUM_DATA
LUMINOSITY         FACTORS              LUM_DATA
LUMINOSITY         GRP_STATUS           LUM_DATA
LUMINOSITY         INS_LUM_VIEW         LUM_DATA
LUMINOSITY         LBNS                 LUM_DATA
LUMINOSITY         PRD_FILE_CHKS        LUM_DATA
LUMINOSITY         RANGED_FACTORS       LUM_DATA
LUMINOSITY         SCALER_TYPES         LUM_DATA
LUMINOSITY         STATUS_MASK_EXPLAIN  LUM_DATA
LUMINOSITY         STREAMS              LUM_DATA
LUMINOSITY         TRANSITION_RECORDS   LUM_DATA
LUMINOSITY         TRIGGERS             LUM_DATA
LUMINOSITY         TRIG_LUM_VIEW        LUM_DATA

13 rows selected.

desc all_indexes;
select unique owner from all_indexes; 
select OWNER,INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,TABLESPACE_NAME from all_indexes
	where  OWNER='LUMINOSITY';

OWNER              INDEX_NAME                     INDEX_TYPE   TABLE_OWNER        TABLE_NAME           TABLESPACE_N
------------------ ------------------------------ ------------ ------------------ -------------------- ------------
LUMINOSITY         BY_TICKS_LBN_FKIDX             NORMAL       LUMINOSITY         BY_TICKS             LUM_DATA
LUMINOSITY         BY_TICKS_PK                    NORMAL       LUMINOSITY         BY_TICKS             LUM_DATA
LUMINOSITY         BY_TICKS_SCALER_ID_FKIDX       NORMAL       LUMINOSITY         BY_TICKS             LUM_DATA
LUMINOSITY         FACTORS_PK                     NORMAL       LUMINOSITY         FACTORS              LUM_DATA
LUMINOSITY         GRP_STATUS_LBN_FKIDX           NORMAL       LUMINOSITY         GRP_STATUS           LUM_DATA
LUMINOSITY         GRP_STATUS_PK                  NORMAL       LUMINOSITY         GRP_STATUS           LUM_DATA
LUMINOSITY         LBNS_PK                        NORMAL       LUMINOSITY         LBNS                 LUM_DATA
LUMINOSITY         PRD_FILE_CHKS_PK               NORMAL       LUMINOSITY         PRD_FILE_CHKS        LUM_DATA
LUMINOSITY         PRD_FILE_CHKS_RN_SN_LBN_FKIDX  NORMAL       LUMINOSITY         PRD_FILE_CHKS        LUM_DATA
LUMINOSITY         RANGED_FACTORS_BEGIN_LBN_FKIDX NORMAL       LUMINOSITY         RANGED_FACTORS       LUM_DATA
LUMINOSITY         RANGED_FACTORS_END_LBN_FKIDX   NORMAL       LUMINOSITY         RANGED_FACTORS       LUM_DATA
LUMINOSITY         RANGED_FACTORS_PK              NORMAL       LUMINOSITY         RANGED_FACTORS       LUM_DATA
LUMINOSITY         SCALER_TYPES_PK                NORMAL       LUMINOSITY         SCALER_TYPES         LUM_DATA
LUMINOSITY         STATUS_MASK_EXPLAIN_PK         NORMAL       LUMINOSITY         STATUS_MASK_EXPLAIN  LUM_DATA
LUMINOSITY         STREAMS_LBN_FKIDX              NORMAL       LUMINOSITY         STREAMS              LUM_DATA
LUMINOSITY         STREAMS_PK                     NORMAL       LUMINOSITY         STREAMS              LUM_DATA
LUMINOSITY         TRANSITION_RECORDS_LBN_FKIDX   NORMAL       LUMINOSITY         TRANSITION_RECORDS   LUM_DATA
LUMINOSITY         TRANS_RECS_PK                  NORMAL       LUMINOSITY         TRANSITION_RECORDS   LUM_DATA
LUMINOSITY         TRIGGERS_LBN_FKIDX             NORMAL       LUMINOSITY         TRIGGERS             LUM_DATA
LUMINOSITY         TRIGGERS_PK                    NORMAL       LUMINOSITY         TRIGGERS             LUM_DATA

20 rows selected.

select synonym_name 
  from all_synonyms where table_owner like 'LUMINOSITY'
  order by synonym_name;

-- 0

select view_name,owner from all_views where upper(owner) like '%LUM%';

VIEW_NAME                      OWNER
------------------------------ ------------------
BYTICKS_PIVOT                  LUMINOSITY

desc all_constraints; 
select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from all_constraints
	where owner='LUMINOSITY';

-- 66 constraints

desc all_triggers;
select OWNER TRIGGER_NAME,TRIGGER_TYPE,TRIGGERING_EVENT,TABLE_OWNER,TABLE_NAME from all_triggers
	where table_owner='LUMINOSITY';

select * from dba_role_privs where grantee='JEREMYS';

desc all_tab_privs_made;
select unique OWNER from  all_tab_privs_made;
select GRANTEE, OWNER, TABLE_NAME, GRANTOR, PRIVILEGE, GRANTABLE, HIERARCHY 
from  all_tab_privs_made;

-- 0

desc role_tab_privs;

 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 ROLE                                                              NOT NULL VARCHAR2(30)
 OWNER                                                             NOT NULL VARCHAR2(30)
 TABLE_NAME                                                        NOT NULL VARCHAR2(30)
 COLUMN_NAME                                                                VARCHAR2(30)
 PRIVILEGE                                                         NOT NULL VARCHAR2(40)
 GRANTABLE                                                                  VARCHAR2(3)

SQL> select unique owner from role_tab_privs;

desc table_privileges;

select TABLE_NAME from all_tables where OWNER='LUMINOSITY';
select count(*) from luminosity.BY_TICKS;
select count(*) from luminosity.FACTORS;
select count(*) from luminosity.GRP_STATUS;
select count(*) from luminosity.INS_LUM_VIEW;
select count(*) from luminosity.LBNS;
select count(*) from luminosity.PRD_FILE_CHKS;
select count(*) from luminosity.RANGED_FACTORS;
select count(*) from luminosity.SCALER_TYPES;
select count(*) from luminosity.STATUS_MASK_EXPLAIN;
select count(*) from luminosity.STREAMS;
select count(*) from luminosity.TRANSITION_RECORDS;
select count(*) from luminosity.TRIGGERS;
select count(*) from luminosity.TRIG_LUM_VIEW;

select TABLE_NAME from all_tables where OWNER='JEREMYS';
select count(*) from JEREMYS.BY_TICKS;
select count(*) from JEREMYS.FACTORS;
select count(*) from JEREMYS.GRP_STATUS;
select count(*) from JEREMYS.INS_LUM_VIEW;
select count(*) from JEREMYS.LBNS;
select count(*) from JEREMYS.PRD_FILE_CHKS;
select count(*) from JEREMYS.RANGED_FACTORS;
select count(*) from JEREMYS.SCALER_TYPES;
select count(*) from JEREMYS.STATUS_MASK_EXPLAIN;
select count(*) from JEREMYS.STREAMS;
select count(*) from JEREMYS.TRANSITION_RECORDS;
select count(*) from JEREMYS.TRIGGERS;
select count(*) from JEREMYS.TRIG_LUM_VIEW;
