{"id":37,"date":"2009-02-16T10:59:03","date_gmt":"2009-02-16T10:59:03","guid":{"rendered":"https:\/\/www.spotonoracle.com\/?p=37"},"modified":"2016-05-10T11:00:54","modified_gmt":"2016-05-10T11:00:54","slug":"definer-vs-invoker-rights-check-out-sys_context","status":"publish","type":"post","link":"https:\/\/www.spotonoracle.com\/?p=37","title":{"rendered":"Definer vs. invoker rights \u2013 check out SYS_CONTEXT"},"content":{"rendered":"<p><strong>The introduction<\/strong><br \/>\nThis is showing the difference between definer and invoker rights in terms of current user and current schema &#8211; what context is the code running in.<\/p>\n<p><strong>The setup<\/strong><br \/>\nIn schema JOHN create two stored procedures one with definer rights the other with invoker rights both printing the same SYS_CONTEXT parameters.<\/p>\n<pre class=\"brush: sql; collapse: false; title: ; wrap-lines: false; notranslate\" title=\"\">\r\nSQL&gt; create or replace procedure print_user_info_def\r\nis\r\nbegin\r\n  dbms_output.put_line('USER: '||user);\r\n  dbms_output.put_line('SESSION_USER: '||sys_context('userenv', 'session_user'));\r\n  dbms_output.put_line('CURRENT_USER: '||sys_context('userenv', 'current_user'));\r\n  dbms_output.put_line('CURRENT_SCHEMA: '||sys_context('userenv', 'current_schema'));\r\nend print_user_info_def;\r\n\/\r\n<\/pre>\n<pre class=\"brush: sql; collapse: false; title: ; wrap-lines: false; notranslate\" title=\"\">\r\nSQL&gt; create or replace procedure print_user_info_inv\r\nauthid current_user\r\nis\r\nbegin\r\n  dbms_output.put_line('USER: '||user);\r\n  dbms_output.put_line('SESSION_USER: '||sys_context('userenv', 'session_user'));\r\n  dbms_output.put_line('CURRENT_USER: '||sys_context('userenv', 'current_user'));\r\n  dbms_output.put_line('CURRENT_SCHEMA: '||sys_context('userenv', 'current_schema'));\r\nend print_user_info_inv;\r\n\/\r\n<\/pre>\n<p><strong>The result<\/strong><br \/>\nLog-in with user JANE and run the two stored procedures owned by JOHN<\/p>\n<pre class=\"brush: sql; collapse: false; title: ; wrap-lines: false; notranslate\" title=\"\">\r\nSQL&gt; exec john.print_user_info_def\r\nUSER: JANE\r\nSESSION_USER: JANE\r\nCURRENT_USER: JOHN\r\nCURRENT_SCHEMA: JOHN\r\n<\/pre>\n<pre class=\"brush: sql; collapse: false; title: ; wrap-lines: false; notranslate\" title=\"\">\r\nSQL&gt; exec john.print_user_info_inv\r\nUSER: JANE\r\nSESSION_USER: JANE\r\nCURRENT_USER: JANE\r\nCURRENT_SCHEMA: JANE\r\n<\/pre>\n<p>Foot note: All tests have been run on a 10g 10.2.0.4 database.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>The introduction This is showing the difference between definer and invoker rights in terms of current user and current schema &#8211; what context is the code running in. The setup In schema JOHN create two stored procedures one with definer rights the other with invoker rights both printing the same SYS_CONTEXT parameters. SQL&gt; create or [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[],"class_list":["post-37","post","type-post","status-publish","format-standard","hentry","category-plsql"],"_links":{"self":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/37","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=37"}],"version-history":[{"count":2,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/37\/revisions"}],"predecessor-version":[{"id":40,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/37\/revisions\/40"}],"wp:attachment":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=37"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=37"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=37"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}