📄 544.html
字号:
<STYLE type=text/css>
<!--
body,td { font-size:9pt;}
hr { color: #000000; height: 1px}
-->
</STYLE>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" "http://www.w3.org/TR/REC-html40/loose.dtd">
<HTML>
<HEAD><TITLE>精选文章 >> Oracle 专栏 >> 如何产生用户密码过期的警告?</title>
</head>
<body >
<p><IMG SRC="../image/jsp001_middle_logo.gif" WIDTH="180" HEIGHT="60" BORDER=0 ALT=""></p>
<table width=100% bgcolor="#cccccc" align=center cellpadding="2" cellspacing="0" border=1 bordercolorlight="#000000" bordercolordark="#FFFFFF">
<tr bgcolor="#EFF8FF"><td>
<a href=http://www.jsp001.com/list_thread.php?int_attribute=2>精选文章</a>
>> <a href=http://www.jsp001.com/list_thread.php?forumid=20&int_attribute=2>Oracle 专栏</a>
>> 如何产生用户密码过期的警告? [<a href=http://www.jsp001.com/forum/showthread.php?goto=newpost&threadid=544>查看别人的评论</a>]<br>
<hr><p>由 guest 发布于: 2001-02-27 12:28</p><p> </p><p>by Kevin Loney and Rachel Carmichael<br><br>在Oracle8以上版本，DBA可以手动强制一个用户的密码到期，如下：<br>alter user DORA password expire;<br>也可以在密码文件中设置密码有效期长度。这是一个管理密码的好方法 （好象有一点象NT密码过期机制，呵呵），但是有一个问题，密码过期时没有任何提示给用户。本文介绍一个如何产生用户密码过期警告的方法。 <br><br>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. <br><br> <br><br>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.<br><br> <br><br>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.<br><br> <br><br>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.<br><br> <br><br>create profile LIMITED_DURATION<br><br>limit<br><br>PASSWORD_LIFE_TIME 60,<br><br>PASSWORD_GRACE_TIME 10;<br><br> <br><br>You can associate users with the LIMITED_DURATION profile via the alter user command, as shown in the following listing.<br><br> <br><br>alter user DORA profile LIMITED_DURATION;<br><br> <br><br>Once a user has a profile assigned, you can use the pwd_warn.sql script, shown in the following listing, to generate warning messages. <br><br> <br><br> <br><br>set pagesize 0<br><br>column Warn_Msg format A80 word_wrapped<br><br> <br><br>select Username, <br><br>'Your database password is about to expire. You must change it by '<br><br>||Expiry_Date||'. ' <br><br>from DBA_USERS<br><br>where Account_Status = 'OPEN'<br><br>and Expiry_Date is not null<br><br>and Sysdate-Expiry_Date <10<br><br>union<br><br>select Username, <br><br>'Your database password expired on '||Expiry_Date||<br><br>'. You must change it during your next login. '<br><br>from DBA_USERS<br><br>where Account_Status = 'EXPIRED'<br><br>union<br><br>select DBA_USERS.Username, <br><br>'Your grace period for changing your expired database <br><br>password will end on '||Expiry_Date+TO_NUMBER(Limit)||<br><br>'. You must change your database password before then. '<br><br>from DBA_USERS, DBA_PROFILES<br><br>where DBA_USERS.Account_Status = 'EXPIRED(GRACE)'<br><br>and DBA_USERS.Profile = DBA_PROFILES.Profile<br><br>and DBA_PROFILES.Resource_Name = 'PASSWORD_GRACE_TIME'<br><br>and DBA_PROFILES.Limit not in ('UNLIMITED', 'DEFAULT')<br><br>order by 1;<br><br> <br><br> <br><br>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:<br><br> <br><br> <br><br>DORA<br><br>Your database password is about to expire. You must change it by 01-SEP-98.<br><br> <br><br>MARCIA<br><br>Your database password expired on 31-AUG-98. You must change it during your<br><br>next login.<br><br> <br><br> <br><br>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. <br><br>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. <br><br> <br><br> <br><br>Annotations<br><br> <br><br>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:<br><br> <br><br>OPEN The current password has not expired.<br><br>EXPIRED The password has expired, but the user has not since logged in.<br><br>EXPIRED(GRACE) The password has expired and the user has since logged in. The <br><br>user has not yet exceeded the grace period for expired passwords.<br><br> <br><br>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.<br><br> <br><br>select Username, <br><br>'Your database password is about to expire. You must change it by '<br><br>||Expiry_Date||'. ' <br><br>from DBA_USERS<br><br>where Account_Status = 'OPEN'<br><br>and Expiry_Date is not null<br><br>and Sysdate-Expiry_Date <10<br><br> <br><br>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.'<br><br> <br><br>select Username, 'Your database password expired on '||Expiry_Date||<br><br>'. You must change it during your next login. '<br><br>from DBA_USERS<br><br>where Account_Status = 'EXPIRED'<br><br> <br><br>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.<br><br> <br><br>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.<br><br> <br><br>select DBA_USERS.Username, <br><br>'Your grace period for changing your expired database <br><br>password will end on '||Expiry_Date+TO_NUMBER(Limit)||<br><br>'. You must change your database password before then. '<br><br>from DBA_USERS, DBA_PROFILES<br><br>where DBA_USERS.Account_Status = 'EXPIRED(GRACE)'<br><br>and DBA_USERS.Profile = DBA_PROFILES.Profile<br><br>and DBA_PROFILES.Resource_Name = 'PASSWORD_GRACE_TIME'<br><br>and DBA_PROFILES.Limit not in ('UNLIMITED', 'DEFAULT')<br><br> <br><br>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.<br><br> <br><br>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. <br><br> <br><br><br>__________________<br>我就是我，命运由我摆布。</p></td>
</tr>
</table>
<p>
<CENTER><a href="http://www.jsp001.com/forum/newreply.php?action=newreply&threadid=544">点这里对该文章发表评论</a></CENTER>
<p>该文章总得分是 <font color=red>6</font> 分，你认为它对你有帮助吗？
[<a href=javascript:void(0) onclick=window.open("http://www.jsp001.com/forum/codeVote.php?threadid=544&intVote=4","","menubar=no,toolbar=no,location=no,directories=no,status=no,resizable=no,scrollbars=no,width=70,height=40,top=0,left=0")>非常多</a>](<font color=red>1</font>)
[<a href=javascript:void(0) onclick=window.open("http://www.jsp001.com/forum/codeVote.php?threadid=544&intVote=2","","menubar=no,toolbar=no,location=no,directories=no,status=no,resizable=no,scrollbars=no,width=70,height=40,top=0,left=0")>有一些</a>](<font color=red>1</font>)
[<a href=javascript:void(0) onclick=window.open("http://www.jsp001.com/forum/codeVote.php?threadid=544&intVote=1","","menubar=no,toolbar=no,location=no,directories=no,status=no,resizable=no,scrollbars=no,width=70,height=40,top=0,left=0")>无帮助</a>](<font color=red>1</font>)
[<a href=javascript:void(0) onclick=window.open("http://www.jsp001.com/forum/codeVote.php?threadid=544&intVote=-1","","menubar=no,toolbar=no,location=no,directories=no,status=no,resizable=no,scrollbars=no,width=70,height=40,top=0,left=0")>是灌水</a>](<font color=red>1</font>) </p>
<script language="javascript" src="http://www.jsp001.com/include/read_thread_script.php?threadid=544"></script>
<p><CENTER>
Copyright © 2001 - 2009 JSP001.com . All Rights Reserved <P>
<IMG SRC="../image/jsp001_small_logo.gif" WIDTH="85" HEIGHT="30" BORDER=0 ALT="">
</CENTER></p>
</body>
</html>⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -