Security- PROFILE CHECK in ORACLE DATABASE

 PROFILE CHECK in ORACLE DATABASE 

Useful script to check the Profile Settings in an Oracle Database .

@profile_check.sql

set linesize 132

set pagesize 999

col username for a23

col dba for a3

col status for a7

col default_tablespace for a20

col profile for a18

col pwd_verify format a20

col plt for a3

col fla for a3

col rum for a3

col pgt for a3

col pwd_lok for a7

with profile_detail as

(

select

dbp.profile,

dbp.resource_name,

decode(dbp.limit,'DEFAULT',def.limit,'NULL','','UNLIMITED','',dbp.limit) limit

from

dba_profiles dbp,

(

select

def.resource_name,

decode(def.limit,'NULL','','UNLIMITED','',def.limit) limit

from

dba_profiles def

where profile = 'DEFAULT'

) def

where 1=1

and dbp.resource_name = def.resource_name

order by 2,1

)

select

dbu.username,

decode(dba.grantee,null,'NO','YES') dba,

replace(

replace(

replace(

replace(dbu.account_status,'LOCKED','LOK'),

'EXPIRED','EXP'),

'(GRACE)','(GR)'),

' & ','&'

) status,

-- dbu.lock_date,

-- dbu.expiry_date,

-- dbu.default_tablespace,

dbu.profile,

pwd.limit "PWD_VERIFY",

plt.limit "PLT",

fla.limit "FLA",

rum.limit "RUM",

pgt.limit "PGT",

plk.limit "PWD_LOK"

from

dba_users dbu,

( select grantee from dba_role_privs where granted_role = 'DBA' ) dba,

profile_detail pwd,

profile_detail plt,

profile_detail fla,

profile_detail rum,

profile_detail pgt,

profile_detail plk

where 1=1

and pwd.profile=dbu.profile

and pwd.resource_name = 'PASSWORD_VERIFY_FUNCTION'

and plt.profile=dbu.profile

and plt.resource_name = 'PASSWORD_LIFE_TIME'

and fla.profile=dbu.profile

and fla.resource_name = 'FAILED_LOGIN_ATTEMPTS'

and rum.profile=dbu.profile

and rum.resource_name = 'PASSWORD_REUSE_MAX'

and pgt.profile=dbu.profile

and pgt.resource_name = 'PASSWORD_GRACE_TIME'

and plk.profile=dbu.profile

and plk.resource_name = 'PASSWORD_LOCK_TIME'

and dbu.username = dba.grantee(+)

order by 1

/