Hello,
I have this query in which I would need an extra column stating if they have the 'DBA' role assigned. Is this possible?
Basically a merge of these 2 queries
column grantee format a20
--select instance_name from v$instance;
SELECT grantee
FROM dba_role_privs
WHERE granted_role='DBA'
and grantee not in ('SYSTEM','SYS' );
--------------------------------------------------------------------------
/* Formatted on 05/07/2018 2:24:19 PM (QP5 v5.185.11230.41888) */
COLUMN username FORMAT a25
COLUMN account_status FORMAT a20
COLUMN default_tablespace FORMAT a20
COLUMN profile FORMAT a20
COLUMN last_login FORMAT a25
COLUMN database FORMAT a10
SELECT SYSDATE AS report_date,
SYS_CONTEXT ('userenv', 'db_name') AS database,
--'' AS STATUS,
username,
created,
account_status,
default_tablespace,
profile,
NVL (TO_CHAR (last_login, 'mm/dd/yy hh24:mi'),
'PWDFILE auth or LOCKED')
last_login,
CASE
WHEN account_status = 'LOCKED' THEN 'INVESTIGATE'
WHEN username LIKE 'SR%' THEN 'Service Account'
WHEN account_status LIKE 'EXPIRED%GRACE%' THEN 'INVESTIGATE'
ELSE 'OK'
END
status
FROM dba_users
WHERE username NOT IN
('SYS',
'SYSTEM',
'OUTLN',
'DBSNMP',
'SCOTT',
'DB_CONTROL',
'OPS$ORACLE',
'XXX')
--and account_status in ('OPEN','EXPIRED(GRACE)','LOCKED')
ORDER BY account_status DESC;
---------------------------------------------------------------------------------------------
Thank you !