오라클 DB 유저 상태 , 패스워드 만료일 체크 스크립트

haisins

7년 ago

prompt Database User Password Expired Check
prompt _______________________________________________________
prompt

col ACCOUNT_STATUS for a18
col LIMIT for a15
col LAST_PWD_CHG_DT for a20
col EXPIRED_PWD_DT for a20
 
select u.USERNAME,u.ACCOUNT_STATUS,u.LOCK_DATE,u.EXPIRY_DATE,u.CREATED,u1.ptime as "LAST_PWD_CHG_DT", P.LIMIT ,
 decode ( P.LIMIT , 'UNLIMITED','9999/12/31' , 'DEFAULT', to_char(u1.ptime+180,'YYYY/MM/DD' ) , to_char(u1.ptime+to_number(P.LIMIT),'YYYY/MM/DD' )  ) as "EXPIRED_PWD_DT"
 from dba_users u,sys.user$ u1 , dba_profiles p
 where u.username=u1.name
and u.profile = p.profile
and username not in ('SYSAUX','SYSMAN','XDB','HR','ODM','OUTLN','OE','SH','PM','IX','WK_TEST','PERFSTAT','DBSNMP','OLAPSYS','QS_CS','QS_CB','QS_CBADM','QS_OS','QS_WS','QS','QS_ADM','ODM_MTR' ,'WKPROXY','QS_ES','ANONYMOUS','WKSYS','WMSYS','APEX_030200','APEX_PUBLIC_USER','APPQOSSYS','BI','CTXSYS','DIP','EXFSYS','FLOWS_FILES','MDDATA','MDSYS','MGMT_VIEW','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OWBSYS','OWBSYS_AUDIT','SCOTT','SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','XS$NULL')
and p.RESOURCE_NAME='PASSWORD_LIFE_TIME'
/

prompt Database User Password Expired Check

prompt _______________________________________________________

prompt

col ACCOUNT_STATUS for a18

col LIMIT for a15

col LAST_PWD_CHG_DT for a20

col EXPIRED_PWD_DT for a20

select u.USERNAME,u.ACCOUNT_STATUS,u.LOCK_DATE,u.EXPIRY_DATE,u.CREATED,u1.ptime as "LAST_PWD_CHG_DT", P.LIMIT ,

decode ( P.LIMIT , 'UNLIMITED','9999/12/31' , 'DEFAULT', to_char(u1.ptime+180,'YYYY/MM/DD' ) , to_char(u1.ptime+to_number(P.LIMIT),'YYYY/MM/DD' ) ) as "EXPIRED_PWD_DT"

from dba_users u,sys.user$ u1 , dba_profiles p

where u.username=u1.name

and u.profile = p.profile

and username not in ('SYSAUX','SYSMAN','XDB','HR','ODM','OUTLN','OE','SH','PM','IX','WK_TEST','PERFSTAT','DBSNMP','OLAPSYS','QS_CS','QS_CB','QS_CBADM','QS_OS','QS_WS','QS','QS_ADM','ODM_MTR' ,'WKPROXY','QS_ES','ANONYMOUS','WKSYS','WMSYS','APEX_030200','APEX_PUBLIC_USER','APPQOSSYS','BI','CTXSYS','DIP','EXFSYS','FLOWS_FILES','MDDATA','MDSYS','MGMT_VIEW','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OWBSYS','OWBSYS_AUDIT','SCOTT','SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','XS$NULL')

and p.RESOURCE_NAME='PASSWORD_LIFE_TIME'