{"id":7280,"date":"2016-03-10T21:36:38","date_gmt":"2016-03-10T20:36:38","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/any-reason-to-change-the-c-common-user-prefix\/"},"modified":"2016-03-10T21:36:38","modified_gmt":"2016-03-10T20:36:38","slug":"any-reason-to-change-the-c-common-user-prefix","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/any-reason-to-change-the-c-common-user-prefix\/","title":{"rendered":"Any reason to change the C## common user prefix?"},"content":{"rendered":"<h2>By Franck Pachot<\/h2>\n<p>.<br \/>\nIn 12.1.0.1 the common user prefix was set to C## by an underscore parameter. In 12.1.0.2 it&#8217;s a documented parameter and you can change it. But is there any reason for that?<br \/>\n<!--more--><br \/>\nI can think of a reason. Let&#8217;s say I want to use external authentication for my common users<br \/>\nSQL&gt; show parameter prefix<\/p>\n<pre><code>&nbsp;\nNAME                                 TYPE        VALUE\n------------------------------------ ----------- ------------------------------\ncommon_user_prefix                   string      C##\nos_authent_prefix                    string      ops$<\/code><\/pre>\n<p>I want to use OPS$ users which are identified externally. For example, the user OPS$ORACLE can connect without password from the linux user oracle. If I still want to do that in multitenant, I can define the common user prefix to OPS$:<\/p>\n<pre><code>SQL&gt; alter system set common_user_prefix='ops$' scope=spfile;\nSystem altered.\n&nbsp;\nSQL&gt; startup force\nORACLE instance started.\n...\nSQL&gt; create user OPS$ORACLE identified externally;\nUser created.\n&amp;nbsp\nSQL&gt; grant dba to OPS$ORACLE container=all;\nGrant succeeded.<\/code><\/pre>\n<p>Then from OS user oracle:<\/p>\n<pre><code>\nSQL&gt; host id\nuid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),495(vboxsf),54322(dba),54323(oper) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023\n&nbsp;\nSQL&gt; connect \/\nConnected.\nSQL&gt; show user\nUSER is \"OPS$ORACLE\"\n<\/code><\/pre>\n<p>This is exactly what I wanted. A common user identified externally.<\/p>\n<p>Of course, it makes no sense to connect though service because external authentication is local only:<\/p>\n<pre><code>SQL&gt; connect \/@\/\/localhost\/PDB\nERROR:\nORA-01017: invalid username\/password; logon denied\n&nbsp;\nWarning: You are no longer connected to ORACLE.<\/code><\/pre>\n<p>So external authentication makes sense only for common users.<\/p>\n<p>In that case, rather that changing the common_user_prefix, it should be better to keep C## and change the os_authent_prefix to C##. The reason is that even when you change the common_user_prefix, the C## is still forbidden, so you have two prefixes that can fail with ORA-65094:<\/p>\n<pre><code>\nSQL&gt; show parameter common_user_prefix\n&nbsp;\nNAME                                 TYPE        VALUE\n------------------------------------ ----------- ------------------------------\ncommon_user_prefix                   string      ops$\n&nbsp;\nSQL&gt; create user c##test identified by oracle;\ncreate user c##test identified by oracle\n            *\nERROR at line 1:\nORA-65094: invalid local user or role name\n<\/code><\/pre>\n<p>So rather let&#8217;s reset the common user prefix to it&#8217;s default C## because this is hardcoded to prevent local users starting with it, and change the external identification prefix.<\/p>\n<pre><code>\nSQL&gt; alter system reset common_user_prefix;\nSystem altered.\nSQL&gt; alter system set os_authent_prefix ='C##' scope=spfile;\nSystem altered.\nSQL&gt; startup force\n...\n<\/code><\/pre>\n<p>Of course, then you have to create a C##ORACLE instead of OPS$ORACLE:<\/p>\n<pre><code>\ncreate user C##ORACLE identified externally container=all;\nUser created.\ngrant DBA to C##ORACLE container=all;\nGrant succeeded.\n<\/code><\/pre>\n<p>and you can connect without password<\/p>\n<pre><code>\nSQL&gt; connect \/\nConnected.\nSQL&gt; show user\nUSER is \"C##ORACLE\"\n<\/code><\/pre>\n<p>Note: this is not SYSDBA connection here. I used oracle user for the example, but this has nothing to do with being in the dba group.<\/p>\n<p>This is an easy way to have all administrators connect with their names. You create linux accounts for them and oracle accounts identified externally. Easy to connect to servers with ssh keys and then to instance. Passwordless and with named users. Remember, SYSDBA is only for things that you cannot do without SYSDBA.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>By Franck Pachot . In 12.1.0.1 the common user prefix was set to C## by an underscore parameter. In 12.1.0.2 it&#8217;s a documented parameter and you can change it. But is there any reason for that?<\/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-7280","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>Any reason to change the C## common user prefix? - 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\/any-reason-to-change-the-c-common-user-prefix\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Any reason to change the C## common user prefix?\" \/>\n<meta property=\"og:description\" content=\"By Franck Pachot . In 12.1.0.1 the common user prefix was set to C## by an underscore parameter. In 12.1.0.2 it&#8217;s a documented parameter and you can change it. But is there any reason for that?\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/any-reason-to-change-the-c-common-user-prefix\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2016-03-10T20:36: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\/any-reason-to-change-the-c-common-user-prefix\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/any-reason-to-change-the-c-common-user-prefix\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Any reason to change the C## common user prefix?\",\"datePublished\":\"2016-03-10T20:36:38+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/any-reason-to-change-the-c-common-user-prefix\/\"},\"wordCount\":333,\"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\/any-reason-to-change-the-c-common-user-prefix\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/any-reason-to-change-the-c-common-user-prefix\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/any-reason-to-change-the-c-common-user-prefix\/\",\"name\":\"Any reason to change the C## common user prefix? - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2016-03-10T20:36:38+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/any-reason-to-change-the-c-common-user-prefix\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/any-reason-to-change-the-c-common-user-prefix\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/any-reason-to-change-the-c-common-user-prefix\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Any reason to change the C## common user prefix?\"}]},{\"@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":"Any reason to change the C## common user prefix? - 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\/any-reason-to-change-the-c-common-user-prefix\/","og_locale":"en_US","og_type":"article","og_title":"Any reason to change the C## common user prefix?","og_description":"By Franck Pachot . In 12.1.0.1 the common user prefix was set to C## by an underscore parameter. In 12.1.0.2 it&#8217;s a documented parameter and you can change it. But is there any reason for that?","og_url":"https:\/\/www.dbi-services.com\/blog\/any-reason-to-change-the-c-common-user-prefix\/","og_site_name":"dbi Blog","article_published_time":"2016-03-10T20:36: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\/any-reason-to-change-the-c-common-user-prefix\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/any-reason-to-change-the-c-common-user-prefix\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Any reason to change the C## common user prefix?","datePublished":"2016-03-10T20:36:38+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/any-reason-to-change-the-c-common-user-prefix\/"},"wordCount":333,"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\/any-reason-to-change-the-c-common-user-prefix\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/any-reason-to-change-the-c-common-user-prefix\/","url":"https:\/\/www.dbi-services.com\/blog\/any-reason-to-change-the-c-common-user-prefix\/","name":"Any reason to change the C## common user prefix? - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2016-03-10T20:36:38+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/any-reason-to-change-the-c-common-user-prefix\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/any-reason-to-change-the-c-common-user-prefix\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/any-reason-to-change-the-c-common-user-prefix\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Any reason to change the C## common user prefix?"}]},{"@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\/7280","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=7280"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/7280\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=7280"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=7280"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=7280"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=7280"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}