Hi
I am running this SQL here to query DBA_PROFILES
set linesize 200
set pagesize 1000
col profile format a30
col resource_name format a40
col limit format a40
select profile, resource_name, limit
from dba_profiles
where resource_type = 'PASSWORD'
order by profile, resource_name
/
The output is as below
PROFILE RESOURCE_NAME LIMIT
------------------------------ ---------------------------------------- ----------------------------------------
DEFAULT FAILED_LOGIN_ATTEMPTS UNLIMITED
DEFAULT INACTIVE_ACCOUNT_TIME UNLIMITED
DEFAULT PASSWORD_GRACE_TIME UNLIMITED
DEFAULT PASSWORD_LIFE_TIME UNLIMITED
DEFAULT PASSWORD_LOCK_TIME .0001
DEFAULT PASSWORD_REUSE_MAX UNLIMITED
DEFAULT PASSWORD_REUSE_TIME UNLIMITED
DEFAULT PASSWORD_ROLLOVER_TIME 0
DEFAULT PASSWORD_VERIFY_FUNCTION NULL
ABC_SYS_SYSTEM_PROFILE FAILED_LOGIN_ATTEMPTS 4
ABC_SYS_SYSTEM_PROFILE INACTIVE_ACCOUNT_TIME DEFAULT
ABC_SYS_SYSTEM_PROFILE PASSWORD_GRACE_TIME 5
ABC_SYS_SYSTEM_PROFILE PASSWORD_LIFE_TIME UNLIMITED
ABC_SYS_SYSTEM_PROFILE PASSWORD_LOCK_TIME .0001
ABC_SYS_SYSTEM_PROFILE PASSWORD_REUSE_MAX 4
ABC_SYS_SYSTEM_PROFILE PASSWORD_REUSE_TIME UNLIMITED
ABC_SYS_SYSTEM_PROFILE PASSWORD_ROLLOVER_TIME DEFAULT
ABC_SYS_SYSTEM_PROFILE PASSWORD_VERIFY_FUNCTION PASSWORD_VERIFICATION
TEST_SYS FAILED_LOGIN_ATTEMPTS 4
TEST_SYS INACTIVE_ACCOUNT_TIME DEFAULT
TEST_SYS PASSWORD_GRACE_TIME 5
TEST_SYS PASSWORD_LIFE_TIME 90
TEST_SYS PASSWORD_LOCK_TIME UNLIMITED
TEST_SYS PASSWORD_REUSE_MAX 4
TEST_SYS PASSWORD_REUSE_TIME UNLIMITED
TEST_SYS PASSWORD_ROLLOVER_TIME DEFAULT
TEST_SYS PASSWORD_VERIFY_FUNCTION ABC_PASSWORD_VERIFY_FUNC
ORA_STIG_PROFILE FAILED_LOGIN_ATTEMPTS 3
ORA_STIG_PROFILE INACTIVE_ACCOUNT_TIME 35
ORA_STIG_PROFILE PASSWORD_GRACE_TIME 5
ORA_STIG_PROFILE PASSWORD_LIFE_TIME 60
ORA_STIG_PROFILE PASSWORD_LOCK_TIME UNLIMITED
ORA_STIG_PROFILE PASSWORD_REUSE_MAX 10
ORA_STIG_PROFILE PASSWORD_REUSE_TIME 365
ORA_STIG_PROFILE PASSWORD_ROLLOVER_TIME DEFAULT
ORA_STIG_PROFILE PASSWORD_VERIFY_FUNCTION ORA12C_STIG_VERIFY_FUNCTION
I would like to have the output like below instead for readability especially if you maybe just want to compare 2 or 3 profiles
RESOURCE_NAME DEFAULT ABC_SYS_SYSTEM_PROFILE TEST_SYS ORA_STIG_PROFILE
------------------------------ ---------------------------------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
FAILED_LOGIN_ATTEMPTS UNLIMITED 4 4 3
INACTIVE_ACCOUNT_TIME UNLIMITED DEFAULT DEFAULT 35
PASSWORD_GRACE_TIME UNLIMITED 5 5 5
PASSWORD_LIFE_TIME UNLIMITED UNLIMITED 90 60
PASSWORD_LOCK_TIME .0001 .0001 UNLIMITED UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED 4 4 10
PASSWORD_REUSE_TIME UNLIMITED UNLIMITED UNLIMITED 365
PASSWORD_ROLLOVER_TIME 0 DEFAULT DEFAULT DEFAULT
PASSWORD_VERIFY_FUNCTION NULL PASSWORD_VERIFICATION ABC_PASSWORD_VERIFY_FUNC ORA12C_STIG_VERIFY_FUNCTION
Anyone know of any SQL / PL-SQL trick to achieve the desired output?
Any advice will be much appreciated. Thanks in advance.