Hi,
I need a query to find a list of users in a database with rights other than select. Similar to this which doesn't work. It leaves some users out.
SELECT DISTINCT
DTP.GRANTEE,
DRP.ADMIN_OPTION,
USR.account_status
FROM
DBA_TAB_PRIVS DTP,
DBA_ROLE_PRIVS DRP,
dba_users USR
WHERE 1=1
AND dtp.grantee = DRP.GRANTEE(+)
AND drp.grantee = usr.username
and dtp.grantee=usr.username(+)
and dtp.grantee in (
select username from dba_users
where account_status = 'OPEN')
AND PRIVILEGE <> 'SELECT'
order by grantee
I have this for EBS, which now that i look at this, probably isn't correct either.
SELECT DISTINCT
DTP.GRANTEE,
USR.DESCRIPTION,
DRP.ADMIN_OPTION,
USR.ENABLED_FLAG
FROM
DBA_TAB_PRIVS DTP,
DBA_ROLE_PRIVS DRP,
APPS.FND_ORACLE_USERID USR
WHERE 1=1
AND dtp.grantee = DRP.GRANTEE(+)
AND drp.grantee = USR.ORACLE_USERNAME(+)
AND PRIVILEGE <> 'SELECT';