{"id":7395,"date":"2016-03-16T10:51:38","date_gmt":"2016-03-16T09:51:38","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/the-privileges-to-connect-to-a-container\/"},"modified":"2016-03-16T10:51:38","modified_gmt":"2016-03-16T09:51:38","slug":"the-privileges-to-connect-to-a-container","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/the-privileges-to-connect-to-a-container\/","title":{"rendered":"The privileges to connect to a container"},"content":{"rendered":"<h2>By Franck Pachot<\/h2>\n<p>.<br \/>\nIn Multitenant, there are two ways to connect to a pluggable database. This means that if you want to prevent access to a pluggable database you need to revoke two privileges.<br \/>\n<!--more--><\/p>\n<h3>Privilege Analysis<\/h3>\n<p>Rather than trying with different combination of privileges there is a clever way to do it in 12c with privilege analysis (remember it&#8217;s an option).<br \/>\nThe idea is to grant all privileges:<\/p>\n<pre><code>\nSQL&gt; create user C##USER1 identified by oracle container=all;\nUser created.\nSQL&gt; grant DBA to C##USER1 container=all;\nGrant succeeded.\n<\/code><\/pre>\n<p>and then start privilege capture to know which privileges are actually used:<\/p>\n<pre><code>\nSQL&gt; exec dbms_privilege_capture.create_capture (name=&gt;'demo',type =&gt;dbms_privilege_capture.g_role,roles=&gt;role_name_list('DBA'));\nPL\/SQL procedure successfully completed.\nSQL&gt; exec dbms_privilege_capture.enable_capture (name=&gt;'demo');\nPL\/SQL procedure successfully completed.\n<\/code><\/pre>\n<p>Note that privilege capture is done per container, so I activate it also in my pluggable database:<\/p>\n<pre><code>\nSQL&gt; alter session set container=PDB001;\nSession altered.\nSQL&gt; exec dbms_privilege_capture.create_capture (name=&gt;'demo',type =&gt;dbms_privilege_capture.g_role,roles=&gt;role_name_list('DBA'));\nPL\/SQL procedure successfully completed.\nSQL&gt; exec dbms_privilege_capture.enable_capture (name=&gt;'demo');\nPL\/SQL procedure successfully completed.\n<\/code><\/pre>\n<p>At the end of each operation, I will get the result running this from each container:<\/p>\n<pre><code>\nSQL&gt; exec dbms_privilege_capture.disable_capture(name=&gt;'demo');\nPL\/SQL procedure successfully completed.\nSQL&gt; exec dbms_privilege_capture.generate_result(name=&gt;'demo');\nPL\/SQL procedure successfully completed.\nSQL&gt; select con_id,username,sys_priv from cdb_used_sysprivs_path;\n<\/code><\/pre>\n<h3>CONNECT<\/h3>\n<p>The first way to connect to a pluggable database is to connect directly to its service:<\/p>\n<pre><code>\nSQL&gt; connect C##USER1@\/\/localhost\/PDB001.pachot.oraclecloud.internal\nConnected.\n<\/code><\/pre>\n<p>And, as you expected, the privilege used is the CREATE SESSION in the pluggable database (PDB001 is CON_ID=4) and no privilege at all is needed in CDB$ROOT:<\/p>\n<pre><code>\n    CON_ID USERNAME   SYS_PRIV\n---------- ---------- ----------------------------------------\n         4 C##USER1   CREATE SESSION\n<\/code><\/pre>\n<h3>SET CONTAINER<\/h3>\n<p>The second way available to common users only is to connect to CDB$ROOT and then switch to the pluggable database container:<\/p>\n<pre><code>\nSQL&gt; connect C##USER1\nConnected.\nSQL&gt; alter session set container=PDB001;\nSession altered.\n<\/code><\/pre>\n<p>Of course, we see the CREATE SESSION when connecting to the CDB$ROOT&gt;<\/p>\n<pre><code>\n    CON_ID USERNAME   SYS_PRIV\n---------- ---------- ----------------------------------------\n         1 C##USER1   CREATE SESSION\n<\/code><\/pre>\n<p>and here is the SET CONTAINER privilege which is used on the pluggable database only.<\/p>\n<pre><code>\n    CON_ID USERNAME   SYS_PRIV\n---------- ---------- ----------------------------------------\n         4 C##USER1   SET CONTAINER\n<\/code><\/pre>\n<h3>Conclusion<\/h3>\n<p>If you want to prevent access to a pluggable database from a common user, you need to revoke both SET CONTAINER and CREATE SESSION in that container. Same idea if you want to set some session environment through logon trigger, you may also do it in an AFTER SET CONTAINER trigger.<br \/>\nNow, do you think that revoking system privileges is sufficient to prevent C##USER1 from seeing PDB001 data? You should remember that there is another way to see what happens in another container, through the V$ or CDB_ views. Not all data, but lot of information are exposed. The ALTER USER &#8230; SET CONTAINER_DATA is there to control that. But that&#8217;s for a future post.<br \/>\nRemember that a common user can be very powerful in a public or private cloud. You can control what he has access to, but then think of all ways of access.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>By Franck Pachot . In Multitenant, there are two ways to connect to a pluggable database. This means that if you want to prevent access to a pluggable database you need to revoke two privileges.<\/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":[220,64,96,209,66,223],"type_dbi":[],"class_list":["post-7395","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-cdb","tag-multitenant","tag-oracle","tag-oracle-12c","tag-pdb","tag-pluggable-databases"],"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>The privileges to connect to a container - 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\/the-privileges-to-connect-to-a-container\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"The privileges to connect to a container\" \/>\n<meta property=\"og:description\" content=\"By Franck Pachot . In Multitenant, there are two ways to connect to a pluggable database. This means that if you want to prevent access to a pluggable database you need to revoke two privileges.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/the-privileges-to-connect-to-a-container\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2016-03-16T09:51:38+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=\"3 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\/the-privileges-to-connect-to-a-container\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/the-privileges-to-connect-to-a-container\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"The privileges to connect to a container\",\"datePublished\":\"2016-03-16T09:51:38+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/the-privileges-to-connect-to-a-container\/\"},\"wordCount\":369,\"commentCount\":0,\"keywords\":[\"CDB\",\"multitenant\",\"Oracle\",\"Oracle 12c\",\"PDB\",\"Pluggable Databases\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/the-privileges-to-connect-to-a-container\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/the-privileges-to-connect-to-a-container\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/the-privileges-to-connect-to-a-container\/\",\"name\":\"The privileges to connect to a container - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2016-03-16T09:51:38+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/the-privileges-to-connect-to-a-container\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/the-privileges-to-connect-to-a-container\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/the-privileges-to-connect-to-a-container\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"The privileges to connect to a container\"}]},{\"@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":"The privileges to connect to a container - 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\/the-privileges-to-connect-to-a-container\/","og_locale":"en_US","og_type":"article","og_title":"The privileges to connect to a container","og_description":"By Franck Pachot . In Multitenant, there are two ways to connect to a pluggable database. This means that if you want to prevent access to a pluggable database you need to revoke two privileges.","og_url":"https:\/\/www.dbi-services.com\/blog\/the-privileges-to-connect-to-a-container\/","og_site_name":"dbi Blog","article_published_time":"2016-03-16T09:51:38+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/the-privileges-to-connect-to-a-container\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/the-privileges-to-connect-to-a-container\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"The privileges to connect to a container","datePublished":"2016-03-16T09:51:38+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/the-privileges-to-connect-to-a-container\/"},"wordCount":369,"commentCount":0,"keywords":["CDB","multitenant","Oracle","Oracle 12c","PDB","Pluggable Databases"],"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/the-privileges-to-connect-to-a-container\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/the-privileges-to-connect-to-a-container\/","url":"https:\/\/www.dbi-services.com\/blog\/the-privileges-to-connect-to-a-container\/","name":"The privileges to connect to a container - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2016-03-16T09:51:38+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/the-privileges-to-connect-to-a-container\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/the-privileges-to-connect-to-a-container\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/the-privileges-to-connect-to-a-container\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"The privileges to connect to a container"}]},{"@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\/7395","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=7395"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/7395\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=7395"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=7395"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=7395"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=7395"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}