{"id":10451,"date":"2017-08-23T05:54:08","date_gmt":"2017-08-23T03:54:08","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/bequeath-connect-to-pdb-set-container-in-logon-trigger\/"},"modified":"2017-08-23T05:54:08","modified_gmt":"2017-08-23T03:54:08","slug":"bequeath-connect-to-pdb-set-container-in-logon-trigger","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/bequeath-connect-to-pdb-set-container-in-logon-trigger\/","title":{"rendered":"Bequeath connect to PDB: set container in logon trigger?"},"content":{"rendered":"

By Franck Pachot<\/h2>\n

.
\nThere are little changes when you go to multitenant architecture and one of them is that you must connect with a service name. You cannot connect directly to a PDB with a beaqueath (aka local) connection. This post is about a workaround you may have in mind: create a common user and set a logon trigger to ‘set container’. I do not<\/u> recommend it and you should really connect with a service. Here is an example.
\n
\nImagine that I have a user connecting with bequeath connection to a non-CDB, using user\/password without a connection string, the database being determined by the ORACLE_SID. And I want to migrate to CDB without changing anything on the client connection configuration side. The best idea would be to use a service, explicitly or implicitly with TWO_TASK or LOCAL. But let’s imagine that you don’t want to change anything on the client side.<\/p>\n

As we can connect only the the CDB$ROOT with a bequeath connection, we have to create a common user. Because the idea is not to change anything on client configuration, and there’s a very little chance that the user starts with C## I’ll start to remove the mandatory prefix for common users.<\/p>\n

\nSQL> show parameter common_user_prefix\n \nNAME                                 TYPE        VALUE\n------------------------------------ ----------- ------------------------------\ncommon_user_prefix                   string\n \nSQL> alter system set common_user_prefix='' scope=spfile;\nSystem altered.\n \nSQL> shutdown immediate\nDatabase closed.\nDatabase dismounted.\nORACLE instance shut down.\nSQL> startup\nORACLE instance started.\n...\n<\/code><\/pre>\n
Then I create my common user:<\/p>\n
\nSQL> create user MYOLDUSER identified by covfefe container=all;\nUser created.\n<\/code><\/pre>\n
This user must be able to connect to the CDB:<\/p>\n
\nSQL> grant create session to MYOLDUSER container=current;\nGrant succeeded.\n<\/code><\/pre>\n
And then I want it to switch immediately to PDB1 using a logon trigger:<\/p>\n
\nSQL> create or replace trigger SET_CONTAINER_AT_LOGON after logon on database\n  2  when (user in ('MYOLDUSER'))\n  3  begin\n  4   execute immediate 'alter session set container=PDB1';\n  5  end;\n  6  \/\nTrigger created.\n<\/code><\/pre>\n
Once on PDB1 this user will have some privileges, and for the example I will grant him a default role:<\/p>\n
\nSQL> alter session set container=PDB1;\nSession altered.\n \nSQL> create role MYROLE;\nRole created.\n \nSQL> grant MYROLE to MYOLDUSER container=current;\nGrant succeeded.\n<\/code><\/pre>\n
The documentation says that When you grant a role to a user, the role is granted as a default role for that user and is therefore enabled immediately upon logon<\/em> so I don’t need to:<\/p>\n
\nSQL> alter user MYOLDUSER default role MYROLE;\nUser altered.\n<\/code><\/pre>\n
But the doc say ‘logon’ and technically I do not logon to PDB1. I just set container. However, if you test it you will see that default roles are set also on ‘set container’. And anyway, we cannot set a role in a procedure, neither with ‘set role’ nor with dbms_session.set_role:<\/p>\n
\nORA-06565: cannot execute SET ROLE from within stored procedure\n<\/code><\/pre>\n
Then, I can now connect locally to the CDB$ROOT with this user:<\/p>\n
\nSQL> connect MYOLDUSER\/covfefe\nConnected.\n<\/code><\/pre>\n
And I’m automatically switched to the PDB1:<\/p>\n
\nSQL> show con_name\n \nCON_NAME\n------------------------------\nPDB1\n<\/code><\/pre>\n
Issue #1: default roles<\/h3>\n
However the default roles are not set:<\/p>\n
\nSQL> select * from session_roles;\n \nno rows selected\n<\/code><\/pre>\n
I have to set the role once connected:<\/p>\n
\nSQL> set role all;\nRole set.\n \nSQL> select * from session_roles;\n \nROLE\n--------------------------------------------------------------------------------\nMYROLE\n<\/code><\/pre>\n
This is probably not what we want when we cannot change anything on the application side. This is considered as a bug (Bug 25081564 : ALTER SESSION SET CONTAINER IN “ON LOGON TRIGGER” IS NOT WORKING) fixed in 18.1 (expected in Q1 2018) and there’s a patch for 12.1 and 12.2 https:\/\/updates.oracle.com\/download\/25081564.html<\/a><\/p>\n
Issue #2: core dump<\/h3>\n
There’s another issue. If you run the same with SQLcl you have a core dump in the client library libclntsh.so on kpuSetContainerNfy<\/p>\n
\nSQLcl: Release 17.2.0 Production on Tue Aug 22 22:00:52 2017\n \nCopyright (c) 1982, 2017, Oracle.  All rights reserved.\n \nSQL> connect MYOLDUSER\/covfefe\n#\n# A fatal error has been detected by the Java Runtime Environment:\n#\n#  SIGSEGV (0xb) at pc=0x00007fcaa172faf6, pid=31242, tid=140510230116096\n#\n# JRE version: Java(TM) SE Runtime Environment (8.0_91-b14) (build 1.8.0_91-b14)\n# Java VM: Java HotSpot(TM) 64-Bit Server VM (25.91-b14 mixed mode linux-amd64 compressed oops)\n# Problematic frame:\n# C  [libclntsh.so.12.1+0x11d8af6]  kpuSetContainerNfy+0x66\n#\n# Core dump written. Default location: \/media\/sf_share\/122\/blogs\/core or core.31242\n<\/code><\/pre>\n
There’s a SR opened for that. This is not a no-go because the context being no change to the client part, then sqlplus will probably be used. However, that’s another point which shows that ‘set container’ in a logon trigger may have some implementation problems.<\/p>\n
Issue #3: security<\/h3>\n
In my opinion, there is a bigger problem here. With sqlplus (or with sqlcl not using local connection) I can connect to the CDB$ROOT and switch to PDB1. But look at all the commands above… where did I grant the ‘set container’ privilege for MYOLDUSER on the PDB1 container? Nowhere. MYOLDUSER has no create session and no set container privileges, but is able to connect to PDB1 thanks to the logon trigger. Of course , the logon trigger is defined by a DBA who knows what he does. But in my opinion, it is not a good idea to bypass the privilege checking.<\/p>\n
So what?<\/h3>\n
With no default role, connecting without the right privilege, the security model is biased here. And disabling the common user prefix will raise other issues one day with plugging operations. Then, in my opinion, this is not a solution to workaround the need to connect with a service. Especially in the context where we run legacy application with no possibility to change the way it connects: you just postpone the problems to bigger ones later.<\/p>\n
The real solution is to connect to a service (and that’s not difficult even when you can’t change the code, with TWO_TASK environment variable).<\/p>\n","protected":false},"excerpt":{"rendered":"
By Franck Pachot . There are little changes when you go to multitenant architecture and one of them is that you must connect with a service name. You cannot connect directly to a PDB with a beaqueath (aka local) connection. This post is about a workaround you may have in mind: create a common user […]<\/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,59],"tags":[220,64,96,209,66,223],"type_dbi":[],"class_list":["post-10451","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-oracle","tag-cdb","tag-multitenant","tag-oracle","tag-oracle-12c","tag-pdb","tag-pluggable-databases"],"acf":[],"yoast_head":"\nBequeath connect to PDB: set container in logon trigger? - 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\/bequeath-connect-to-pdb-set-container-in-logon-trigger\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Bequeath connect to PDB: set container in logon trigger?\" \/>\n<meta property=\"og:description\" content=\"By Franck Pachot . There are little changes when you go to multitenant architecture and one of them is that you must connect with a service name. You cannot connect directly to a PDB with a beaqueath (aka local) connection. This post is about a workaround you may have in mind: create a common user […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/bequeath-connect-to-pdb-set-container-in-logon-trigger\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2017-08-23T03:54:08+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\\\/bequeath-connect-to-pdb-set-container-in-logon-trigger\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/bequeath-connect-to-pdb-set-container-in-logon-trigger\\\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Bequeath connect to PDB: set container in logon trigger?\",\"datePublished\":\"2017-08-23T03:54:08+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/bequeath-connect-to-pdb-set-container-in-logon-trigger\\\/\"},\"wordCount\":734,\"commentCount\":0,\"keywords\":[\"CDB\",\"multitenant\",\"Oracle\",\"Oracle 12c\",\"PDB\",\"Pluggable Databases\"],\"articleSection\":[\"Database Administration & Monitoring\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/bequeath-connect-to-pdb-set-container-in-logon-trigger\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/bequeath-connect-to-pdb-set-container-in-logon-trigger\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/bequeath-connect-to-pdb-set-container-in-logon-trigger\\\/\",\"name\":\"Bequeath connect to PDB: set container in logon trigger? - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2017-08-23T03:54:08+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/bequeath-connect-to-pdb-set-container-in-logon-trigger\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/bequeath-connect-to-pdb-set-container-in-logon-trigger\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/bequeath-connect-to-pdb-set-container-in-logon-trigger\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Bequeath connect to PDB: set container in logon trigger?\"}]},{\"@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":"Bequeath connect to PDB: set container in logon trigger? - 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\/bequeath-connect-to-pdb-set-container-in-logon-trigger\/","og_locale":"en_US","og_type":"article","og_title":"Bequeath connect to PDB: set container in logon trigger?","og_description":"By Franck Pachot . There are little changes when you go to multitenant architecture and one of them is that you must connect with a service name. You cannot connect directly to a PDB with a beaqueath (aka local) connection. This post is about a workaround you may have in mind: create a common user […]","og_url":"https:\/\/www.dbi-services.com\/blog\/bequeath-connect-to-pdb-set-container-in-logon-trigger\/","og_site_name":"dbi Blog","article_published_time":"2017-08-23T03:54:08+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\/bequeath-connect-to-pdb-set-container-in-logon-trigger\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/bequeath-connect-to-pdb-set-container-in-logon-trigger\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Bequeath connect to PDB: set container in logon trigger?","datePublished":"2017-08-23T03:54:08+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/bequeath-connect-to-pdb-set-container-in-logon-trigger\/"},"wordCount":734,"commentCount":0,"keywords":["CDB","multitenant","Oracle","Oracle 12c","PDB","Pluggable Databases"],"articleSection":["Database Administration & Monitoring","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/bequeath-connect-to-pdb-set-container-in-logon-trigger\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/bequeath-connect-to-pdb-set-container-in-logon-trigger\/","url":"https:\/\/www.dbi-services.com\/blog\/bequeath-connect-to-pdb-set-container-in-logon-trigger\/","name":"Bequeath connect to PDB: set container in logon trigger? - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2017-08-23T03:54:08+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/bequeath-connect-to-pdb-set-container-in-logon-trigger\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/bequeath-connect-to-pdb-set-container-in-logon-trigger\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/bequeath-connect-to-pdb-set-container-in-logon-trigger\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Bequeath connect to PDB: set container in logon trigger?"}]},{"@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\/10451","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=10451"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/10451\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=10451"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=10451"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=10451"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=10451"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}