精选文章
>> Oracle 专栏
>> 如何产生用户密码过期的警告?
由 guest 发布于: 2001-02-27 12:28
by Kevin Loney and Rachel Carmichael
在Oracle8以上版本,DBA可以手动强制一个用户的密码到期,如下:
alter user DORA password expire;
也可以在密码文件中设置密码有效期长度。这是一个管理密码的好方法 (好象有一点象NT密码过期机制,呵呵),但是有一个问题,密码过期时没有任何提示给用户。本文介绍一个如何产生用户密码过期警告的方法。
A user's password may also expire based on the parameters set in the user's profile. As of ORACLE8, profiles can contain settings for the number of days a password can be used before it expires (its "lifetime"). You can also specify the length of the grace period a user has to change his or her password after the password has expired.
NOTE: The script in this article covers only the password expiration parameters of the create profile command. You can also use profiles to establish criteria for password complexity and resource limits.
When the user's password expires, the user will need to change his or her ORACLE password. The potential problem with this implementation is that there is no proactive warning given to the user. The user is only notified of the password expiration after the password expiration date has been reached. Although this accomplishes the system's needs--forcing user password changes--it may not meet the user's needs. Rather than tell the users to change their password after it expires, you can warn the users before their passwords expire via the pwd_warn.sql script provided in this article.
You can set the maximum duration of a password via the password_life_time parameter of the create profile and alter profile commands. A second parameter, password_grace_time, limits the "grace period"--the duration of time after the first post-expiration login that the user can continue to use the same password. For example, the following create profile command creates a profile named LIMITED_DURATION. Users of this profile will have to change their passwords every 60 days. After their first login following password expiration, users will have ten days to change their passwords.
create profile LIMITED_DURATION
limit
PASSWORD_LIFE_TIME 60,
PASSWORD_GRACE_TIME 10;
You can associate users with the LIMITED_DURATION profile via the alter user command, as shown in the following listing.
alter user DORA profile LIMITED_DURATION;
Once a user has a profile assigned, you can use the pwd_warn.sql script, shown in the following listing, to generate warning messages.
set pagesize 0
column Warn_Msg format A80 word_wrapped
select Username,
'Your database password is about to expire. You must change it by '
||Expiry_Date||'. '
from DBA_USERS
where Account_Status = 'OPEN'
and Expiry_Date is not null
and Sysdate-Expiry_Date <10
union
select Username,
'Your database password expired on '||Expiry_Date||
'. You must change it during your next login. '
from DBA_USERS
where Account_Status = 'EXPIRED'
union
select DBA_USERS.Username,
'Your grace period for changing your expired database
password will end on '||Expiry_Date+TO_NUMBER(Limit)||
'. You must change your database password before then. '
from DBA_USERS, DBA_PROFILES
where DBA_USERS.Account_Status = 'EXPIRED(GRACE)'
and DBA_USERS.Profile = DBA_PROFILES.Profile
and DBA_PROFILES.Resource_Name = 'PASSWORD_GRACE_TIME'
and DBA_PROFILES.Limit not in ('UNLIMITED', 'DEFAULT')
order by 1;
By default, the pwd_warn.sql script generates output which you can spool to a file. You may wish to run the three unioned queries that constitute pwd_warn.sql separately if that simplifies your user notification process. Sample output is shown in the following listing:
DORA
Your database password is about to expire. You must change it by 01-SEP-98.
MARCIA
Your database password expired on 31-AUG-98. You must change it during your
next login.
You can process the output of the pwd_warn.sql script via command scripts at the operating system level. For example, you could use call external C programs to process the file via the external library capabilities introduced in ORACLE8.
Since such programs usually involve C and OCI calls, they are beyond the scope of this article; see the demo files provided under the /plsql/demo subdirectory under the ORACLE software home directory. The demo files are named extproc.sql and extproc.c.
Annotations
The pwd_warn.sql script is driven by the possible values of the Account_Status column in the DBA_USERS data dictionary view. The status for each user will be one of the following values:
OPEN The current password has not expired.
EXPIRED The password has expired, but the user has not since logged in.
EXPIRED(GRACE) The password has expired and the user has since logged in. The
user has not yet exceeded the grace period for expired passwords.
If your password has not expired, or if you do not have a profile that enforces password expiration, then your account status is 'OPEN.' If the Account_Status value is 'OPEN,' then the first part of the pwd_warn.sql script checks for a value in the Expiry_Date column of DBA_USERS. The Expiry_Date column records the expiration date of a user's current password; if no password expiration is set, then Expiry_Date will be NULL. The first part of pwd_warn.sql, shown in the following listing, generates a warning if a user's Expiry_Date is within ten days of the current system date.
select Username,
'Your database password is about to expire. You must change it by '
||Expiry_Date||'. '
from DBA_USERS
where Account_Status = 'OPEN'
and Expiry_Date is not null
and Sysdate-Expiry_Date <10
If your account has expired, then your account status can be either 'EXPIRED' or 'EXPIRED(GRACE).' If your password has expired but you have not logged in since the password expired, you will have an account status of 'EXPIRED.' The grace period, if defined, does not start until the first time you log in after the password expiration. The second part of the unioned pwd_warn.sql query, shown in the following listing, creates a warning message for users with an Account_Status value of 'EXPIRED.'
select Username, 'Your database password expired on '||Expiry_Date||
'. You must change it during your next login. '
from DBA_USERS
where Account_Status = 'EXPIRED'
If you have logged in since your password expired, but did not change your password, then you are using the grace period defined for your profile. The password_grace_time setting for the profile sets the number of days during which you can continue to login after your first post-expiration login.
The third part of the pwd_warn.sql script, shown in the following listing, generates a warning message for users who are using the grace period. If the password_grace_time value has been set, then its value is added to the Expiry_Date to determine the end date of the grace time. If the user did not log in on the password expiration date, then the calculated date may differ from the actual expiration date of the password.
select DBA_USERS.Username,
'Your grace period for changing your expired database
password will end on '||Expiry_Date+TO_NUMBER(Limit)||
'. You must change your database password before then. '
from DBA_USERS, DBA_PROFILES
where DBA_USERS.Account_Status = 'EXPIRED(GRACE)'
and DBA_USERS.Profile = DBA_PROFILES.Profile
and DBA_PROFILES.Resource_Name = 'PASSWORD_GRACE_TIME'
and DBA_PROFILES.Limit not in ('UNLIMITED', 'DEFAULT')
There are three different possible values for the password_grace_time parameter: a value (such as 10), 'UNLIMITED,' and 'DEFAULT.' It is not helpful to set a password_life_time value while leaving the password_grace_time setting at 'UNLIMITED' since the user would never be required to change his or her password. If you set password_life_time, you should also set password_grace_time. Once your account status has changed to 'EXPIRED' or 'EXPIRED(GRACE),' changing the profile's parameters does not affect your status - you will still need to change your password.
If you integrate the pwd_warn.sql script with an operating system program to mail the results to your users, then you will be able to effectively use the password expiration features of ORACLE8. You can force users to periodically change their passwords to values that meet your standards for complexity, and you can warn them before their passwords expire. As a result, the DBA will be able to spend far less time resetting users' expired passwords and more time managing the database.
__________________
我就是我,命运由我摆布。
|