Want to get the list users with select access to v$ synonyms and v$ views
I've to write a sql (DB 11.1) to get the list of users who has select access to v$ synonym and v$ views. I've written the following sqls to do this but they both return the same result and I don't know how to verify it. It will be a great help if you could validate the sqls and let me know if something is wrong. Thanks for the help.
-----
-- v$ views
select 'vview',
substr(SYS_CONNECT_BY_PATH(c, '->'),3,512) path, c
from (select null p, view_name c
from dba_views
where view_name like ('V$%')
union all
select -- users/roles and roles granted
granted_role p,
grantee c
from dba_role_privs
where granted_role != 'DBA'
union all
select -- users/roles with select on DBA views
table_name p, grantee c
from dba_tab_privs
where privilege = 'SELECT'
and table_name like ('V$%'))
where (c = 'PUBLIC' OR c in (select username from dba_users))
AND c NOT IN('MDSYS','DMSYS','CTXSYS','WMSYS','ORDSYS','OLAPSYS','DBSNMP')
start with p is null connect by p = prior c
-----
-- v$ synonyms
select 'vsynonyms',
substr(SYS_CONNECT_BY_PATH(c, '->'),3,512) path, c
from (select null p, SYNONYM_NAME c
from ALL_SYNONYMS
where table_name like ('V$%')
union all
select -- users/roles and roles granted
granted_role p,
grantee c
from dba_role_privs
where granted_role != 'DBA'
union all
select -- users/roles with select on DBA views
table_name p, grantee c
from dba_tab_privs
where privilege = 'SELECT'
and table_name like ('V$%'))
where (c = 'PUBLIC' OR c in (select username from dba_users))
AND c NOT IN('MDSYS','DMSYS','CTXSYS','WMSYS','ORDSYS','OLAPSYS','DBSNMP')
start with p is null connect by p = prior c