{"id":51,"date":"2013-05-05T13:20:00","date_gmt":"2013-05-05T13:20:00","guid":{"rendered":"\/colin\/morethanfour\/post\/Exploring-AUTHID-with-an-eye-to-partitioning-client-data"},"modified":"2013-05-05T13:20:00","modified_gmt":"2013-05-05T13:20:00","slug":"exploring-authid-with-an-eye-to-partitioning-client-data","status":"publish","type":"post","link":"https:\/\/spacefold.com\/colin\/morethanfour\/2013\/05\/05\/exploring-authid-with-an-eye-to-partitioning-client-data\/","title":{"rendered":"Exploring AUTHID with an eye to partitioning client data"},"content":{"rendered":"<p><strong>Problem<\/strong><\/p>\n<p>Is it possible to design a multi-tenant architecture in an Oracle DB instance, such that different customers&#8217; data is transparently partitioned whilst keeping common code and common tables in a single location?<\/p>\n<p><strong>Possible Strategy<\/strong><\/p>\n<p>Consider the following architecture:<\/p>\n<p> <img decoding=\"async\" src=\"http:\/\/www.spacefold.com\/colin\/morethanfour\/wp-content\/uploads\/2013%2f5%2fAUTHID_1.PNG\" alt=\"\" \/><\/p>\n<p>Here we have two schemas in an Oracle database instance:<\/p>\n<ul>\n<li>U_Common contains common tables and stored procedures, packages, etc.<\/li>\n<li>U_Cust_1 contains customer-specific tables, with private data. <\/li>\n<li>Client processes acting on behalf of Customer_1 are connecting to the U_Cust_1 schema. <\/li>\n<\/ul>\n<p>(You may assume we also have U_Cust_2, etc.)<\/p>\n<p>Assume that all access is performed through stored procedures or similar code objects.<br \/>In order for sessions connecting to U_Cust_1 to execute specific procedures in U_Common, they need:<\/p>\n<ul>\n<li>EXECUTE permissions on the objects in U_Common;<\/li>\n<li>Either:<br \/>o    explicit namespace prefixes, e.g. U_Common.DoSomething()<br \/>o    a synonym object referencing the code object in U_Common <\/li>\n<\/ul>\n<p>In order to have common code objects that reference both common tables and customer tables, we need to create &#8220;template&#8221; versions of the tables in the U_Common schema:<\/p>\n<p> <img decoding=\"async\" src=\"http:\/\/www.spacefold.com\/colin\/morethanfour\/wp-content\/uploads\/2013%2f5%2fAUTHID_2.PNG\" alt=\"\" \/><\/p>\n<p>However, when U_Cust_1 invokes the procedure in U_Common, the session can&#8217;t &#8220;see&#8221; the customer-specific version of the table, because the code is executing in the context of U_Common. It will see the template table data (or lack of it &#8211; presumably there are no rows in the template tables).<\/p>\n<p>This can be addressed by adjusting the AUTHID pragma when the code objects are defined:<\/p>\n<p>Default:<\/p>\n<pre>  create or replace package U_Common [authid definer] is ..<\/pre>\n<p>Change to:<\/p>\n<pre>  create or replace package U_Common<span style=\"background-color: #ffff99;\"> authid current_user<\/span> is ..<\/pre>\n<p>According to the documentation, this AUTHID clause instructs Oracle on whether to run the code with the invoker&#8217;s rights, or the owner&#8217;s rights. (the default is DEFINER, or owner.)<\/p>\n<p> <img decoding=\"async\" src=\"http:\/\/www.spacefold.com\/colin\/morethanfour\/wp-content\/uploads\/2013%2f5%2fAUTHID_3.PNG\" alt=\"\" \/><\/p>\n<p>In practice, this means that when U_Cust_1 executes the code object defined as AUTHID CURRENT_USER,  the customer tables in U_Cust_1 are in scope, and are accessed instead of the template tables in the U_Common schema.<\/p>\n<p>Two interesting observations about executing code defined as AUTHID CURRENT_USER:<\/p>\n<ul>\n<li>the code does NOT see the Common tables. Any table in U_Common is &#8220;out of scope&#8221;;<\/li>\n<li>the code can see other procedures and packages in U_Common, including those defined as AUTHID DEFINER (default);<\/li>\n<li>when the code makes a nested call to those other procedures, the tables in U_Common are now visible!<\/li>\n<\/ul>\n<p> Here&#8217;s a sequence diagram of the call stack:<\/p>\n<p> <img decoding=\"async\" src=\"http:\/\/www.spacefold.com\/colin\/morethanfour\/wp-content\/uploads\/2013%2f5%2fAUTHID_4.PNG\" alt=\"\" \/><\/p>\n<p>This means that everything will work just fine, so long as the code objects are classified into &#8220;programs that access common data&#8221; and &#8220;programs that access customer data&#8221;.<\/p>\n<p><strong>Concerning Views<\/strong><\/p>\n<p>Views don&rsquo;t work so well. <\/p>\n<p>You can define a view in U_Common that joins a Common table with a Customer table, but any U_Common procedure that is authid current_user will not be able to &#8220;see&#8221; the view from the context of U_Cust_1.<\/p>\n<p>You can try this:<\/p>\n<pre>grant SELECT on View to U_Cust_1<\/pre>\n<pre>create synonym in U_Cust_1 for the U_Common.View<\/pre>\n<p>But the result set will be entirely pulled from the U_Common schema tables.<\/p>\n<p>So this technique probably isn&#8217;t going to be used to create a transparently customer-partitioned application architecture. But it is still worth understanding how AUTHID works.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Problem Is it possible to design a multi-tenant architecture in an Oracle DB instance, such that different customers&#8217; data is transparently partitioned whilst keeping common code and common tables in a single location? Possible Strategy Consider the following architecture: Here we have two schemas in an Oracle database instance: U_Common contains common tables and stored [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[12],"tags":[],"class_list":["post-51","post","type-post","status-publish","format-standard","hentry","category-oracle","post-preview"],"_links":{"self":[{"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/posts\/51","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/comments?post=51"}],"version-history":[{"count":0,"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/posts\/51\/revisions"}],"wp:attachment":[{"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/media?parent=51"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/categories?post=51"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/tags?post=51"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}