{"id":15426,"date":"2020-12-27T19:35:49","date_gmt":"2020-12-27T18:35:49","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/password-rolling-change-before-oracle-21c\/"},"modified":"2020-12-27T19:35:49","modified_gmt":"2020-12-27T18:35:49","slug":"password-rolling-change-before-oracle-21c","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/password-rolling-change-before-oracle-21c\/","title":{"rendered":"Password rolling change before Oracle 21c"},"content":{"rendered":"

By Franck Pachot<\/h2>\n

.
\nYou may have read about Gradual Password Rollover usage from Mouhamadou Diaw<\/a> and about some internals from Rodrigo Jorge<\/a>. But it works only on 21c which is only in the cloud, for the moment, in Autonomous Database and DBaaS (but here I’ve encountered some problems<\/a> apparently because of a bug when using SQL*Net native encryption). But your production is not yet in 21c anyway. However, here is how you can achieve a similar goal in 12c,18c or 19c: be able to connect with two passwords for the time window where you are changing the password in a rolling fashion in the application server configuration.<\/p>\n

Proxy User<\/h3>\n

If your application still connects with the application owner, you do it wrong. Even when it needs to be connected in the application schema by default, and even when you can’t to an “alter session set current_schema” you don’t have to use this user for authentication. And this is really easy with proxy users. Consider the application owner as a schema, not as a user to connect with.<\/p>\n

My application is in schema DEMO and I’ll not use DEMO credentials. You can set an impossible password or, better, in 18c, set no password at all. I’ll use a proxy user authentication to connect to this DEMO user:<\/p>\n

\n19:28:49 DEMO@atp1_tp> grant create session to APP2020 identified by \"2020 was a really Bad Year!\";\nGrant succeeded.\n\n19:28:50 DEMO@atp1_tp> alter user DEMO grant connect through APP2020;\nUser DEMO altered.\n<\/code><\/pre>\n
The APP2020 user is the one I’ll use. I named it 2020 because I want to change the credentials every year and, as I don’t have the gradual rollover password feature, this means changing the user to connect with.<\/p>\n
\n19:28:50 DEMO@atp1_tp> connect APP2020\/\"2020 was a really Bad Year!\"@atp1_tp\nConnected.\n19:28:52 APP2020@atp1_tp> show user\nUSER is \"APP2020\"\n<\/code><\/pre>\n
This user can connect as usual, as it has the CREATE SESSION privilege. There is a way to prevent this and allow PROXY ONLY CONNECT, but this is unfortunately not documented (Miguel Anjo has written about this<\/a>) so better not using it.<\/p>\n
However, the most important is:<\/p>\n
\n19:28:52 APP2020@atp1_tp> connect APP2020[DEMO]\/\"2020 was a really Bad Year!\"@atp1_tp\nConnected.\n\n19:28:53 DEMO@atp1_tp> show user\nUSER is \"DEMO\"\n<\/code><\/pre>\n
With proxy connection, in addition to the proxy user credentials I mention the final user I want to connect to, though this proxy user. Now I’m in the exact same state as if I connected with the DEMO user.<\/p>\n
No authentication<\/h3>\n
\n19:28:54 ADMIN@atp1_tp> alter user DEMO no authentication;\nUser DEMO altered.\n<\/code><\/pre>\n
As we don’t connect through this user anymore (and once I’m sure no application uses it) the best is to set it with NO AUTHENTICATION.<\/p>\n
New proxy user<\/h3>\n
Now that the application uses this APP2020 for months, I want to change the password. I’ll add a new proxy user for that:<\/p>\n
\n19:28:54 ADMIN@atp1_tp> show user\nUSER is \"ADMIN\"\n\n19:28:53 ADMIN@atp1_tp> grant create session to APP2021 identified by \"Best Hopes for 2021 :)\";\nGrant succeeded.\n\n19:28:54 ADMIN@atp1_tp> alter user DEMO grant connect through APP2021;\nUser DEMO altered.\n<\/code><\/pre>\n
Here I have another proxy user that can be used to connect to DEMO, in addition to the existing one<\/p>\n
\n19:28:54 ADMIN@atp1_tp> connect APP2020[DEMO]\/\"2020 was a really Bad Year!\"@atp1_tp\nConnected.\n\n19:28:55 DEMO@atp1_tp> show user\nUSER is \"DEMO\"\n\n19:28:55 DEMO@atp1_tp> connect APP2021[DEMO]\/\"Best Hopes for 2021 :)\"@atp1_tp\nConnected.\n\n19:28:56 DEMO@atp1_tp> show user\nUSER is \"DEMO\"\n<\/code><\/pre>\n
During this time, I can use both credentials. This gives me enough time to change all application server configuration one by one, without any downtime for the application.<\/p>\n
\"\"<\/a><\/p>\n
Lock previous account<\/h3>\n
\n19:30:00 ADMIN@atp1_tp> \n select username,account_status,last_login,password_change_date,proxy_only_connect \n from dba_users where username like 'APP____';\n\n   USERNAME    ACCOUNT_STATUS                                       LAST_LOGIN    PASSWORD_CHANGE_DATE    PROXY_ONLY_CONNECT\n___________ _________________ ________________________________________________ _______________________ _____________________\nAPP2020     OPEN              27-DEC-20 07.28.55.000000000 PM EUROPE\/ZURICH    27-DEC-20               N\nAPP2021     OPEN              27-DEC-20 07.28.56.000000000 PM EUROPE\/ZURICH    27-DEC-20               N\n<\/code><\/pre>\n
After a while, I can validate that the old user is not used anymore. If you have a connection recycling duration in the connection pool (you should) you can rely on last login.<\/p>\n
\n19:30:00 ADMIN@atp1_tp> alter user APP2020 account lock;\nUser APP2020 altered.\n<\/code><\/pre>\n
Before dropping it, just lock the account, easier to keep track of it and unlock it quickly if anyone encounters a problem<\/p>\n
\n19:30:00 ADMIN@atp1_tp> connect APP2020[DEMO]\/\"2020 was a really Bad Year!\"@atp1_tp\nError starting at line : 30 File @ \/home\/opc\/demo\/tmp\/proxy_to_rollover.sql\nIn command -\n  connect ...\nError report -\nConnection Failed\n  USER          = APP2020[DEMO]\n  URL           = jdbc:oracle:thin:@atp1_tp\n  Error Message = ORA-28000: The account is locked.\nCommit\n<\/code><\/pre>\n
If someone tries to connect with the old password, he will know that the user is locked.<\/p>\n
\n19:30:01 @> connect APP2021[DEMO]\/\"Best Hopes for 2021 :)\"@atp1_tp\nConnected.\n19:30:02 DEMO@atp1_tp> show user\nUSER is \"DEMO\"\n<\/code><\/pre>\n
Once the old user locked, only the new one is able to connect, with the new user credentials. As this operation can be done with no application downtime, you can do it frequently. From a security point of view, you must change passwords frequently. For end-user passwords, you can set a lifetime, and grace period. But not for system users as the warning may not be cached. Better change them proactively.<\/p>\n","protected":false},"excerpt":{"rendered":"
By Franck Pachot . You may have read about Gradual Password Rollover usage from Mouhamadou Diaw and about some internals from Rodrigo Jorge. But it works only on 21c which is only in the cloud, for the moment, in Autonomous Database and DBaaS (but here I’ve encountered some problems apparently because of a bug when […]<\/p>\n","protected":false},"author":27,"featured_media":15427,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[59],"tags":[995,96,997,910],"type_dbi":[],"class_list":["post-15426","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-oracle","tag-authentication","tag-oracle","tag-password","tag-proxy"],"acf":[],"yoast_head":"\nPassword rolling change before Oracle 21c - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/password-rolling-change-before-oracle-21c\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Password rolling change before Oracle 21c\" \/>\n<meta property=\"og:description\" content=\"By Franck Pachot . You may have read about Gradual Password Rollover usage from Mouhamadou Diaw and about some internals from Rodrigo Jorge. But it works only on 21c which is only in the cloud, for the moment, in Autonomous Database and DBaaS (but here I’ve encountered some problems apparently because of a bug when […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/password-rolling-change-before-oracle-21c\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2020-12-27T18:35:49+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Screenshot-2020-12-27-201256.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1994\" \/>\n\t<meta property=\"og:image:height\" content=\"608\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/password-rolling-change-before-oracle-21c\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/password-rolling-change-before-oracle-21c\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Password rolling change before Oracle 21c\",\"datePublished\":\"2020-12-27T18:35:49+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/password-rolling-change-before-oracle-21c\/\"},\"wordCount\":607,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/password-rolling-change-before-oracle-21c\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Screenshot-2020-12-27-201256.jpg\",\"keywords\":[\"Authentication\",\"Oracle\",\"Password\",\"Proxy\"],\"articleSection\":[\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/password-rolling-change-before-oracle-21c\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/password-rolling-change-before-oracle-21c\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/password-rolling-change-before-oracle-21c\/\",\"name\":\"Password rolling change before Oracle 21c - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/password-rolling-change-before-oracle-21c\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/password-rolling-change-before-oracle-21c\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Screenshot-2020-12-27-201256.jpg\",\"datePublished\":\"2020-12-27T18:35:49+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/password-rolling-change-before-oracle-21c\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/password-rolling-change-before-oracle-21c\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/password-rolling-change-before-oracle-21c\/#primaryimage\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Screenshot-2020-12-27-201256.jpg\",\"contentUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Screenshot-2020-12-27-201256.jpg\",\"width\":1994,\"height\":608},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/password-rolling-change-before-oracle-21c\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Password rolling change before Oracle 21c\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Password rolling change before Oracle 21c - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/password-rolling-change-before-oracle-21c\/","og_locale":"en_US","og_type":"article","og_title":"Password rolling change before Oracle 21c","og_description":"By Franck Pachot . You may have read about Gradual Password Rollover usage from Mouhamadou Diaw and about some internals from Rodrigo Jorge. But it works only on 21c which is only in the cloud, for the moment, in Autonomous Database and DBaaS (but here I’ve encountered some problems apparently because of a bug when […]","og_url":"https:\/\/www.dbi-services.com\/blog\/password-rolling-change-before-oracle-21c\/","og_site_name":"dbi Blog","article_published_time":"2020-12-27T18:35:49+00:00","og_image":[{"width":1994,"height":608,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Screenshot-2020-12-27-201256.jpg","type":"image\/jpeg"}],"author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/password-rolling-change-before-oracle-21c\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/password-rolling-change-before-oracle-21c\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Password rolling change before Oracle 21c","datePublished":"2020-12-27T18:35:49+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/password-rolling-change-before-oracle-21c\/"},"wordCount":607,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/password-rolling-change-before-oracle-21c\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Screenshot-2020-12-27-201256.jpg","keywords":["Authentication","Oracle","Password","Proxy"],"articleSection":["Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/password-rolling-change-before-oracle-21c\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/password-rolling-change-before-oracle-21c\/","url":"https:\/\/www.dbi-services.com\/blog\/password-rolling-change-before-oracle-21c\/","name":"Password rolling change before Oracle 21c - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/password-rolling-change-before-oracle-21c\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/password-rolling-change-before-oracle-21c\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Screenshot-2020-12-27-201256.jpg","datePublished":"2020-12-27T18:35:49+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/password-rolling-change-before-oracle-21c\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/password-rolling-change-before-oracle-21c\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/password-rolling-change-before-oracle-21c\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Screenshot-2020-12-27-201256.jpg","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Screenshot-2020-12-27-201256.jpg","width":1994,"height":608},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/password-rolling-change-before-oracle-21c\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Password rolling change before Oracle 21c"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/15426","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=15426"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/15426\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/15427"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=15426"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=15426"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=15426"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=15426"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}