{"id":855,"date":"2013-06-10T09:38:23","date_gmt":"2013-06-10T08:38:23","guid":{"rendered":"http:\/\/blogs.silicontechnix.com\/?p=855"},"modified":"2013-06-10T09:41:37","modified_gmt":"2013-06-10T08:41:37","slug":"oracle-11g-password-expires","status":"publish","type":"post","link":"https:\/\/blogs.silicontechnix.com\/?p=855","title":{"rendered":"Oracle 11g &#8211; Password expires"},"content":{"rendered":"<p>&nbsp;<\/p>\n<p>Oracle 11g sets by default password expiration. The expired message needs to re-login and reset the old password to new one.<\/p>\n<p>Suppose you have an user with the status &#8220;expired&#8221;:<\/p>\n<p>You can check that by:<!--more--><\/p>\n<pre class=\"brush:sql\"> SQL&gt; select username, account_status, expiry_date, profile from dba_users;<\/pre>\n<pre class=\"brush:sql\"> \r\nUSERNAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ACCOUNT_STATUS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 EXPIRY_DA PROFILE\r\n\r\n-------------------- -------------------- --------- ----------\r\n\r\nSOE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 OPEN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 18-JAN-12 DEFAULT\r\n\r\nBRAVO \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 EXPIRED\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 30-MAY-13 DEFAULT\r\n\r\nSCOTT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 EXPIRED &amp; LOCKED\u00a0\u00a0\u00a0\u00a0 15-JUL-11 DEFAULT\r\n\r\nORACLE_OCM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 EXPIRED &amp; LOCKED\u00a0\u00a0\u00a0\u00a0 05-SEP-10 DEFAULT\r\n\r\nXS$NULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 EXPIRED &amp; LOCKED\u00a0\u00a0\u00a0\u00a0 05-SEP-10 DEFAULT\r\n\r\nMDDATA\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 EXPIRED &amp; LOCKED\u00a0\u00a0\u00a0\u00a0 05-SEP-10 DEFAULT\r\n\r\nDIP\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 EXPIRED &amp; LOCKED\u00a0\u00a0\u00a0\u00a0 05-SEP-10 DEFAULT\r\n\r\nAPEX_PUBLIC_USER\u00a0\u00a0\u00a0\u00a0 EXPIRED &amp; LOCKED\u00a0\u00a0\u00a0\u00a0 05-SEP-10 DEFAULT<\/pre>\n<pre class=\"brush:sql\"> \r\n2) SQL&gt; alter user BRAVO identified by TESTING;<\/pre>\n<p>3) To avoid to have users with &#8220;expired&#8221; password, you have two options:<br \/>\n&#8211; Create a new profile and set your own definitions about dates to expire the passwords<br \/>\n&#8211; Change the default profile<\/p>\n<p>4) Let&#8217;s make an example how to do that:<\/p>\n<p>Checking profiles:<\/p>\n<pre class=\"brush:sql\"> \r\nSQL&gt; SELECT profile, resource_name, limit FROM dba_profiles WHERE profile='DEFAULT';<\/pre>\n<pre class=\"brush:sql\"> \r\nPROFILE\u00a0\u00a0\u00a0 RESOURCE_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LIMIT\r\n\r\n---------- -------------------------------- ----------------------------------------\r\n\r\nDEFAULT\u00a0\u00a0\u00a0 COMPOSITE_LIMIT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 UNLIMITED\r\n\r\nDEFAULT\u00a0\u00a0\u00a0 SESSIONS_PER_USER\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 UNLIMITED\r\n\r\nDEFAULT\u00a0\u00a0\u00a0 CPU_PER_SESSION\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 UNLIMITED\r\n\r\nDEFAULT\u00a0\u00a0\u00a0 CPU_PER_CALL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 UNLIMITED\r\n\r\nDEFAULT\u00a0\u00a0\u00a0 LOGICAL_READS_PER_SESSION\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 UNLIMITED\r\n\r\nDEFAULT\u00a0\u00a0\u00a0 LOGICAL_READS_PER_CALL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 UNLIMITED\r\n\r\nDEFAULT\u00a0\u00a0\u00a0 IDLE_TIME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 UNLIMITED\r\n\r\nDEFAULT\u00a0\u00a0\u00a0 CONNECT_TIME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 UNLIMITED\r\n\r\nDEFAULT\u00a0\u00a0\u00a0 PRIVATE_SGA\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 UNLIMITED\r\n\r\nDEFAULT\u00a0\u00a0\u00a0 FAILED_LOGIN_ATTEMPTS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 10\r\n\r\nDEFAULT\u00a0\u00a0\u00a0 PASSWORD_LIFE_TIME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 180\r\n\r\nPROFILE\u00a0\u00a0\u00a0 RESOURCE_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LIMIT\r\n\r\n---------- -------------------------------- ----------------------------------------\r\n\r\nDEFAULT\u00a0\u00a0\u00a0 PASSWORD_REUSE_TIME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 UNLIMITED\r\n\r\nDEFAULT\u00a0\u00a0\u00a0 PASSWORD_REUSE_MAX\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 UNLIMITED\r\n\r\nDEFAULT\u00a0\u00a0\u00a0 PASSWORD_VERIFY_FUNCTION\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NULL\r\n\r\nDEFAULT\u00a0\u00a0\u00a0 PASSWORD_LOCK_TIME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\r\n\r\nDEFAULT\u00a0\u00a0\u00a0 PASSWORD_GRACE_TIME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 7<\/pre>\n<p>5) Changing the password_life_time for unlimited<\/p>\n<pre class=\"brush:sql\"> \r\nSQL&gt; alter profile default limit password_life_time unlimited;<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>&nbsp; Oracle 11g sets by default password expiration. The expired message needs to re-login and reset the old password to new one. Suppose you have an user with the status &#8220;expired&#8221;: You can check that by:<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_s2mail":"no","jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","enabled":false},"version":2}},"categories":[95],"tags":[388,387,385,386,333],"class_list":["post-855","post","type-post","status-publish","format-standard","hentry","category-system-administration","tag-dba","tag-expired","tag-oracle","tag-password-expired","tag-sql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p12j6H-dN","_links":{"self":[{"href":"https:\/\/blogs.silicontechnix.com\/index.php?rest_route=\/wp\/v2\/posts\/855","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blogs.silicontechnix.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blogs.silicontechnix.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.silicontechnix.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.silicontechnix.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=855"}],"version-history":[{"count":6,"href":"https:\/\/blogs.silicontechnix.com\/index.php?rest_route=\/wp\/v2\/posts\/855\/revisions"}],"predecessor-version":[{"id":860,"href":"https:\/\/blogs.silicontechnix.com\/index.php?rest_route=\/wp\/v2\/posts\/855\/revisions\/860"}],"wp:attachment":[{"href":"https:\/\/blogs.silicontechnix.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=855"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blogs.silicontechnix.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=855"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blogs.silicontechnix.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=855"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}