{"id":11828,"date":"2018-10-20T14:00:13","date_gmt":"2018-10-20T12:00:13","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/schema-only-account-with-oracle-18-3\/"},"modified":"2018-10-20T14:00:13","modified_gmt":"2018-10-20T12:00:13","slug":"schema-only-account-with-oracle-18-3","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/schema-only-account-with-oracle-18-3\/","title":{"rendered":"Schema only account with Oracle 18.3"},"content":{"rendered":"<p>With Oracle 18.3, we have the possibility to create schemas without a password. Effectively in a perfect world, we should not be able to connect to application schemas. For security reasons it is a good thing that nobody can connect directly to the application schema.<\/p>\n<p>A good way is to use proxy connections, in fact connect as app_user but using the psi_user password for example:<\/p>\n<p>Let\u2019s create a user named app_user:<\/p>\n<pre class=\"brush: sql; gutter: false; first-line: 1\">SQL&gt; connect sys@pdb as sysdba\nEnter password: \nConnected.\n\nSQL&gt; create user app_user identified by app_user\n  2  quota unlimited on users;\n\nUser created.\n\nSQL&gt; grant create session , create table to app_user;\n\nGrant succeeded.\n\n<\/pre>\n<p>Let\u2019s create a proxy user named psi_user:<\/p>\n<pre class=\"brush: sql; gutter: false; first-line: 1\">SQL&gt; create user psi_user identified by psi_user;\n\nUser created.\n\nSQL&gt; grant create session to psi_user;\n\nGrant succeeded.<\/pre>\n<pre class=\"brush: sql; gutter: false; first-line: 1\">We allow the proxy connection to the app_user:\n\n<\/pre>\n<pre class=\"brush: sql; gutter: false; first-line: 1\">SQL&gt; alter user app_user grant connect through psi_user;\n\nUser altered.<\/pre>\n<p>Now we can connect via the proxy user using the following syntax:<\/p>\n<pre class=\"brush: sql; gutter: false; first-line: 1\">SQL&gt; connect psi_user[app_user]\/psi_user@pdb \nConnected.<\/pre>\n<p>We can see we are connected as user app_user but using the psi_user password:<\/p>\n<pre class=\"brush: sql; gutter: false; first-line: 1\">SQL&gt; select sys_context('USERENV','SESSION_USER') as session_user,\nsys_context('USERENV','SESSION_SCHEMA') as session_schema,\nsys_context('USERENV','PROXY_USER') as proxy,\nuser\nfrom dual;\n\nSESSION_USER\tSESSION_SCHEMA\t        PROXY\t\tUSER\nAPP_USER\tAPP_USER\t\tPSI_USER\tAPP_USER<\/pre>\n<p>But there is a problem, if the app_user is locked the proxy connection does not work anymore:<\/p>\n<pre class=\"brush: sql; gutter: false; first-line: 1\">SQL&gt; connect sys@pdb as sysdba\nEnter password: \nConnected.\nSQL&gt; alter user app_user account lock;\n\nUser altered.\n\nSQL&gt; connect psi_user[app_user]\/psi_user@pdb\nERROR:\nORA-28000: The account is locked.\n\nWarning: You are no longer connected to ORACLE.<\/pre>\n<p>The good solution is to use the schema only Oracle 18c new feature:<\/p>\n<p>We drop the old accounts:<\/p>\n<pre class=\"brush: sql; gutter: false; first-line: 1\">SQL&gt; connect sys@pdb as sysdba\nEnter password: \nConnected.\nSQL&gt; drop user psi_user cascade;\n\nUser dropped.\n\nSQL&gt; drop user app_user cascade;\n\nUser dropped.<\/pre>\n<p>And we recreate them in the following way, we first create the schema owner with no authentication:<\/p>\n<pre class=\"brush: sql; gutter: false; first-line: 1\">SQL&gt; create user app_user no authentication\n  2  quota unlimited on users;\n\nUser created.\n\nSQL&gt; grant create session , create table to app_user;\n\nGrant succeeded.<\/pre>\n<pre class=\"brush: sql; gutter: false; first-line: 1\">We create the proxy user as before:<\/pre>\n<pre class=\"brush: sql; gutter: false; first-line: 1\">SQL&gt; create user psi_user identified by psi_user;<\/pre>\n<p>We allow the proxy user to connect to the app_user:<\/p>\n<pre class=\"brush: sql; gutter: false; first-line: 1\">SQL&gt; alter user app_user grant connect through psi_user;\n\nUser altered.<\/pre>\n<p>We now can connect via psi_user:<\/p>\n<pre class=\"brush: sql; gutter: false; first-line: 1\">SQL&gt; connect psi_user[app_user]\/psi_user@pdb\nConnected.<\/pre>\n<p>And as the app_user has been created in no authentication, you receive the classical ORA-01017 error when you try to connect directly with the app_user account:<\/p>\n<pre class=\"brush: sql; gutter: false; first-line: 1\">SQL&gt; connect app_user\/app_user@pdb\nERROR:\nORA-01017: invalid username\/password; logon denied\n\nWarning: You are no longer connected to ORACLE.<\/pre>\n<p>Using no authentication is a good protection, but you cannot grant system privileges to such users:<\/p>\n<pre class=\"brush: sql; gutter: false; first-line: 1\">SQL&gt; grant sysdba to app_user;\ngrant sysdba to app_user\n*\nERROR at line 1:\nORA-40366: Administrative privilege cannot be granted to this user.<\/pre>\n<p>We can try to alter the app_user with a password and grant it to sysdba but it does not work:<\/p>\n<pre class=\"brush: sql; gutter: false; first-line: 1\">SQL&gt; alter user app_user identified by password;\n\nUser altered.\n\nSQL&gt; grant sysdba to app_user;\n\nGrant succeeded.\n\nSQL&gt; alter user app_user no authentication;\nalter user app_user no authentication\n*\nERROR at line 1:\nORA-40367: An Administrative user cannot be altered to have no authentication\ntype.\n\nSQL&gt; revoke sysdba from app_user;\n\nRevoke succeeded.\n\nSQL&gt; alter user app_user no authentication;\n\nUser altered.<\/pre>\n<p>To understand correctly the behavior, I made the following test:<\/p>\n<pre class=\"brush: sql; gutter: false; first-line: 1\">SQL&gt; connect sys@pdb as sysdba\nEnter password: \nConnected.<\/pre>\n<pre class=\"brush: sql; gutter: false; first-line: 1\">I remove the no authentication:\n\n<\/pre>\n<pre class=\"brush: sql; gutter: false; first-line: 1\">SQL&gt; alter user app_user identified by app_user;\n\nUser altered.<\/pre>\n<p>Now I can connect on the app_user schema, I create a table and insert some values:<\/p>\n<pre class=\"brush: sql; gutter: false; first-line: 1\">SQL&gt; connect app_user\/app_user@pdb\nConnected.\nSQL&gt; create table employe (name varchar2(10));\n\nTable created.\n\nSQL&gt; insert into employe values('Larry');\n\n1 row created.\n\nSQL&gt; commit;\n\nCommit complete.<\/pre>\n<p>I reset the app_user to no authentication:<\/p>\n<pre class=\"brush: sql; gutter: false; first-line: 1\">SQL&gt; connect sys@pdb as sysdba\nEnter password: \nConnected.\nSQL&gt; alter user app_user no authentication;\n\nUser altered.<\/pre>\n<p>I connect with the proxy user, I can display the employe table content:<\/p>\n<pre class=\"brush: sql; gutter: false; first-line: 1\">SQL&gt; connect psi_user[app_user]\/psi_user@pdb\nConnected.\nSQL&gt; select * from employe;\n\nNAME\n----------\nLarry<\/pre>\n<p>The table belongs to the app_user schema:<\/p>\n<pre class=\"brush: sql; gutter: false; first-line: 1\">SQL&gt; select object_name, object_type, owner from all_objects where object_name ='EMPLOYE';\n\nOBJECT_NAME\tOBJECT_TYPE\tOWNER\nEMPLOYE\t\tTABLE\t\tAPP_USER<\/pre>\n<pre class=\"brush: sql; gutter: false; first-line: 1\">SQL&gt; insert into employe values ('Bill');\n\n1 row created.\n\nSQL&gt; commit; \n\nCommit complete.\n\nSQL&gt; select * from employe;\n\nNAME\n----------\nLarry\nBill<\/pre>\n<p>What is the behavior in the audit trail ?<\/p>\n<p>We create an audit policy to detect any table creation:<\/p>\n<pre class=\"brush: sql; gutter: false; first-line: 1\">SQL&gt; create audit policy psi_user_audit_policy\n  2  privileges create table\n  3  when 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''APP_USER'''\n  4  evaluate per session\n  5 container=current\n\nAudit policy created.\n\nSQL&gt; audit policy psi_user_audit_policy whenever successful;\n\nAudit succeeded.<\/pre>\n<pre class=\"brush: sql; gutter: false; first-line: 1\">If now we have a look at the unified_audit_trail view:\n\n<\/pre>\n<pre class=\"brush: sql; gutter: false; first-line: 1\">SQL&gt; select event_timestamp, dbusername, dbproxy_username from unified_audit_trail where object_name = 'SALARY' and action_name = 'CREATE TABLE'\n\nEVENT_TIMESTAMP\t\tDBUSERNAME\tDBPROXY_USERNAME\n16-OCT-18 03.40.49\tAPP_USER\tPSI_USER<\/pre>\n<p>We can identify clearly the proxy user in the audit trail.<\/p>\n<p>Conclusion:<\/p>\n<p>The schema only accounts is an interesting new feature. In resume we can create a schema named app_user and set the authentication to NONE, the consequence is that you cannot be logged in. We can create a proxy account named psi_user which connects through app_user and we can create tables , views \u2026 to this app_user schema.<\/p>\n<pre class=\"brush: sql; gutter: false; first-line: 1\">\n\n\n\n\n\n\n\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>With Oracle 18.3, we have the possibility to create schemas without a password. Effectively in a perfect world, we should not be able to connect to application schemas. For security reasons it is a good thing that nobody can connect directly to the application schema. A good way is to use proxy connections, in fact [&hellip;]<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[1184],"type_dbi":[],"class_list":["post-11828","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-oracle-18c"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Schema only account with Oracle 18.3 - 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\/schema-only-account-with-oracle-18-3\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Schema only account with Oracle 18.3\" \/>\n<meta property=\"og:description\" content=\"With Oracle 18.3, we have the possibility to create schemas without a password. Effectively in a perfect world, we should not be able to connect to application schemas. For security reasons it is a good thing that nobody can connect directly to the application schema. A good way is to use proxy connections, in fact [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/schema-only-account-with-oracle-18-3\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2018-10-20T12:00:13+00:00\" \/>\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\/schema-only-account-with-oracle-18-3\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/schema-only-account-with-oracle-18-3\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Schema only account with Oracle 18.3\",\"datePublished\":\"2018-10-20T12:00:13+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/schema-only-account-with-oracle-18-3\/\"},\"wordCount\":397,\"commentCount\":0,\"keywords\":[\"Oracle 18c\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/schema-only-account-with-oracle-18-3\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/schema-only-account-with-oracle-18-3\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/schema-only-account-with-oracle-18-3\/\",\"name\":\"Schema only account with Oracle 18.3 - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2018-10-20T12:00:13+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/schema-only-account-with-oracle-18-3\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/schema-only-account-with-oracle-18-3\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/schema-only-account-with-oracle-18-3\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Schema only account with Oracle 18.3\"}]},{\"@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":"Schema only account with Oracle 18.3 - 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\/schema-only-account-with-oracle-18-3\/","og_locale":"en_US","og_type":"article","og_title":"Schema only account with Oracle 18.3","og_description":"With Oracle 18.3, we have the possibility to create schemas without a password. Effectively in a perfect world, we should not be able to connect to application schemas. For security reasons it is a good thing that nobody can connect directly to the application schema. A good way is to use proxy connections, in fact [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/schema-only-account-with-oracle-18-3\/","og_site_name":"dbi Blog","article_published_time":"2018-10-20T12:00:13+00:00","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\/schema-only-account-with-oracle-18-3\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/schema-only-account-with-oracle-18-3\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Schema only account with Oracle 18.3","datePublished":"2018-10-20T12:00:13+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/schema-only-account-with-oracle-18-3\/"},"wordCount":397,"commentCount":0,"keywords":["Oracle 18c"],"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/schema-only-account-with-oracle-18-3\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/schema-only-account-with-oracle-18-3\/","url":"https:\/\/www.dbi-services.com\/blog\/schema-only-account-with-oracle-18-3\/","name":"Schema only account with Oracle 18.3 - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2018-10-20T12:00:13+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/schema-only-account-with-oracle-18-3\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/schema-only-account-with-oracle-18-3\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/schema-only-account-with-oracle-18-3\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Schema only account with Oracle 18.3"}]},{"@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\/11828","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=11828"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/11828\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=11828"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=11828"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=11828"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=11828"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}