{"id":73,"date":"2014-11-03T19:40:15","date_gmt":"2014-11-03T18:40:15","guid":{"rendered":"http:\/\/anjo.pt\/wp\/keyword-oracle\/?p=73"},"modified":"2015-11-17T20:27:22","modified_gmt":"2015-11-17T19:27:22","slug":"oracle-12-1-proxy-only-connect-user-property","status":"publish","type":"post","link":"https:\/\/anjo.pt\/keyword-oracle\/2014\/11\/03\/oracle-12-1-proxy-only-connect-user-property\/","title":{"rendered":"Oracle 12.1 &#8211; Proxy only connect user property"},"content":{"rendered":"<p>This yet undocumented feature allows to define application schemas which can only be accessed through a proxy user. It makes a very useful to assure that no user connects directly to the application schema, even by knowing its password.<\/p>\n<p>Here how it works:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nSQL&gt; CREATE USER app_user IDENTIFIED BY xyz;\r\nUser created.\r\n\r\nSQL&gt; GRANT CREATE SESSION TO app_user;\r\nGrant succeeded.\r\n\r\nSQL&gt; ALTER USER app_user PROXY ONLY CONNECT;\r\nUser altered.\r\n\r\nSQL&gt; CREATE USER personal_user IDENTIFIED BY prx1;\r\nUser created.\r\n\r\nSQL&gt; ALTER USER app_user GRANT CONNECT THROUGH personal_user;\r\nUser altered.\r\n\r\nSQL&gt; CONNECT app_user\/xyz;\r\nERROR:\r\nORA-28058: login is allowed only through a proxy\r\n\r\nSQL&gt; CONNECT personal_user&#x5B;app_user]\/prx1;\r\nConnected.\r\n\r\nSQL&gt; SELECT user FROM dual;\r\nUSER\r\n------------------------------\r\nAPP_USER\r\n<\/pre>\n<p>The information that app_user accepts to be connected only through proxy user can be seen at the new DBA_USERS column PROXY_ONLY_CONNECT.<\/p>\n<p>As usual, the use of undocumented features are not supported by Oracle. The syntax to rollback the change is:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nSQL&gt; ALTER USER app_user CANCEL PROXY ONLY CONNECT;\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>This yet undocumented feature allows to define application schemas which can only be accessed through a proxy user. It makes a very useful to assure that no user connects directly to the application schema, even by knowing its password. Here how it works: SQL&gt; CREATE USER app_user IDENTIFIED BY xyz; User created. SQL&gt; GRANT CREATE [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,7,9],"tags":[],"class_list":{"0":"post-73","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-oracle","7":"category-oracle-12c","8":"category-security","9":"czr-hentry"},"_links":{"self":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/73","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/comments?post=73"}],"version-history":[{"count":3,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/73\/revisions"}],"predecessor-version":[{"id":76,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/73\/revisions\/76"}],"wp:attachment":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/media?parent=73"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/categories?post=73"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/tags?post=73"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}