{"id":11897,"date":"2022-10-30T12:56:32","date_gmt":"2022-10-30T11:56:32","guid":{"rendered":"https:\/\/www.salvis.com\/blog\/?p=11897"},"modified":"2023-11-08T18:04:24","modified_gmt":"2023-11-08T17:04:24","slug":"optimizer_secure_view_merging-and-plsql_declarations","status":"publish","type":"post","link":"https:\/\/www.salvis.com\/blog\/2022\/10\/30\/optimizer_secure_view_merging-and-plsql_declarations\/","title":{"rendered":"optimizer_secure_view_merging and plsql_declarations"},"content":{"rendered":"\n

The Original Problem<\/h2>\n\n\n\n

A customer is currently upgrading some Oracle databases from 11.2 to 19c. One query was extremely slow on the new test system and my job was to find out why. The root cause was that the database parameter optimizer_secure_view_merging<\/a> was set to a different value. In 19c true<\/code> and false<\/code> in 11.2. This led to a different and in fact bad execution plan in 19c.<\/p>\n\n\n\n

Now the question was, should the customer keep the default value of optimizer_secure_view_merging<\/code> in 19c and rewrite the slow query or change the parameter to false<\/code> as in 11.2 to get a good performance without a code change?<\/p>\n\n\n\n

What About the opt_param<\/code> Hint?<\/h2>\n\n\n\n

Actually, the first thing I tried was the opt_param('optimizer_secure_view_merging','false')<\/code> hint. Unfortunately, this does not work in 19c. It’s a known bug 28504113. Fixed in 23c. However, I can’t really recommend waiting for 23c, right?<\/p>\n\n\n\n

What About the merge view<\/code> Privilege?<\/h2>\n\n\n\n

The merge any view<\/a> privilege is a good option for highly privileged users and roles. But it should not be granted lightly to any ordinary role or user.<\/p>\n\n\n\n

The merge view<\/a> privilege can be granted per view to a user or role. This has a similar scope as a hint in the subquery of a view without having to change the code. In fact, it is an excellent option to override the optimizer_secure_view_merging<\/code> setting for a view. We could grant merge view on <owner>.<view_name> to public<\/code> to mimic the scope of a hint in the subquery of a view.<\/p>\n\n\n\n

However, the customer uses a metadata-driven approach to generate the grants for end-user roles as part of the application. And it would require a change of the application to handle this exceptional case. Of course, this grant can easily be hard coded for the view in question, but this is something the customer would like to avoid.<\/p>\n\n\n\n

Christian Antognini’s Recommendation<\/h2>\n\n\n\n

Chris explains optimizer_secure_view_merging<\/code> on pages 289 to 291 in Troubleshooting Oracle Performance, 2nd Edition<\/a>. On page 291 he writes the following:<\/p>\n\n\n\n

\n

If you\u2019re neither using views nor VPD for security purposes, I advise you to set the optimizer_secure_view_merging<\/code> initialization parameter to FALSE<\/code>.<\/p>\n<\/blockquote>\n\n\n\n

In my case, the customer uses views and protects them with Virtual Private Database policies. According to Chris, the customer should keep the default value true<\/code> for optimizer_secure_view_merging<\/code>. A sound advice.<\/p>\n\n\n\n

What Security Risk Are We Talking About?<\/h2>\n\n\n\n

Troubleshooting Oracle Performance, 2nd Edition<\/a> comes with an allfiles.zip<\/a> file. It contains a script optimizer_secure_view_merging.sql<\/code> in the folder chapter09<\/code>. Chris used this script to explain the impact of optimizer_secure_view_merging<\/code> in his book. I reused this script here with minor changes.<\/p>\n\n\n\n

Let’s connect as user sys<\/code> and create a database user u1<\/code> for the application data and code and a user u2<\/code> as connect user (with passwords which work in Autonomous Databases). We also disable optimizer_secure_view_merging<\/code>.<\/p>\n\n\n\n

1) Setup as sys<\/span><\/path><\/path><\/svg><\/span>
create<\/span> <\/span>user<\/span> <\/span>u1<\/span> identified <\/span>by<\/span> <\/span>"AppOwner2022"<\/span>    <\/span>default<\/span> tablespace users quota <\/span>unlimited<\/span> <\/span>on<\/span> users;<\/span><\/span>\ncreate<\/span> <\/span>user<\/span> <\/span>u2<\/span> identified <\/span>by<\/span> <\/span>"ConnectUser2022"<\/span> <\/span>default<\/span> tablespace users quota <\/span>unlimited<\/span> <\/span>on<\/span> users;<\/span><\/span>\n<\/span>\ngrant<\/span> <\/span>create<\/span> <\/span>session<\/span>, <\/span>create<\/span> <\/span>table<\/span>, <\/span>create<\/span> <\/span>procedure<\/span>, <\/span>create<\/span> view, <\/span>create<\/span> public <\/span>synonym<\/span> <\/span>to<\/span> u1;<\/span><\/span>\ngrant<\/span> <\/span>create<\/span> <\/span>session<\/span>, <\/span>create<\/span> <\/span>procedure<\/span> <\/span>to<\/span> u2;<\/span><\/span>\n<\/span>\nalter<\/span> <\/span>system<\/span> <\/span>set<\/span> optimizer_secure_view_merging=false scope=memory;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Now we connect as user u1<\/code> and create a table t<\/code> with 6 rows. and a function f<\/code> to filter rows in the view v<\/code>.<\/p>\n\n\n\n
2) Setup as u1<\/span><\/path><\/path><\/svg><\/span>
create<\/span> <\/span>table<\/span> <\/span>t<\/span> (<\/span><\/span>\n  id    <\/span>number<\/span>(<\/span>10<\/span>) <\/span>primary key<\/span>,<\/span><\/span>\n  class <\/span>number<\/span>(<\/span>10<\/span>),<\/span><\/span>\n  pad   <\/span>varchar2<\/span>(<\/span>10<\/span>)<\/span><\/span>\n);<\/span><\/span>\n<\/span>\nexecute<\/span> dbms_random.seed(<\/span>0<\/span>)<\/span><\/span>\n<\/span>\ninsert into<\/span> t (id, class, pad)<\/span><\/span>\nselect<\/span> rownum, mod(rownum, <\/span>3<\/span>), dbms_random.string(<\/span>'a'<\/span>, <\/span>10<\/span>)<\/span><\/span>\n  <\/span>from<\/span> dual<\/span><\/span>\nconnect<\/span> <\/span>by<\/span> <\/span>level<\/span> <= <\/span>6<\/span>;<\/span><\/span>\n<\/span>\nexecute<\/span> dbms_stats.gather_table_stats(user, <\/span>'t'<\/span>)<\/span><\/span>\n<\/span>\ncreate or replace<\/span> <\/span>function<\/span> <\/span>f<\/span>(in_class <\/span>in<\/span> <\/span>number<\/span>) <\/span>return<\/span> <\/span>number<\/span> <\/span>as<\/span><\/span>\nbegin<\/span><\/span>\n   <\/span>if<\/span> in_class = <\/span>1<\/span> <\/span>then<\/span><\/span>\n      <\/span>return<\/span> <\/span>1<\/span>;<\/span><\/span>\n   <\/span>else<\/span><\/span>\n      <\/span>return<\/span> <\/span>0<\/span>;<\/span><\/span>\n   <\/span>end<\/span> <\/span>if<\/span>;<\/span><\/span>\nend<\/span>;<\/span><\/span>\n\/<\/span><\/span>\n<\/span>\ncreate or replace<\/span> <\/span>view<\/span> <\/span>v<\/span> <\/span>as<\/span><\/span>\n   <\/span>select<\/span> *<\/span><\/span>\n     <\/span>from<\/span> t<\/span><\/span>\n    <\/span>where<\/span> f(class) = <\/span>1<\/span>;<\/span><\/span>\n<\/span>\ngrant<\/span> <\/span>select<\/span> <\/span>on<\/span> v <\/span>to<\/span> u2;<\/span><\/span>\n<\/span>\ncreate<\/span> <\/span>or<\/span> <\/span>replace<\/span> public <\/span>synonym<\/span> v <\/span>for<\/span> u1.v;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Let’s connect as user u2<\/code> to query the view.<\/p>\n\n\n\n
3) Query view v as u2<\/span><\/path><\/path><\/svg><\/span>
select<\/span> id, pad<\/span><\/span>\n  <\/span>from<\/span> v<\/span><\/span>\n <\/span>where<\/span> id <\/span>between<\/span> <\/span>1<\/span> <\/span>and<\/span> <\/span>5<\/span>;<\/span><\/span>\n<\/span>\n        ID PAD       <\/span><\/span>\n---------- ----------<\/span><\/span>\n         <\/span>1<\/span> DrMLTDXxxq<\/span><\/span>\n         <\/span>4<\/span> AszBGEUGEL<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Only two of five rows are returned due to the where clause in the view. So far so good.<\/p>\n\n\n\n
The user u2<\/code> has the right to create own functions. And that is a security risk. Why? Because the user can write a spy<\/code> function like in the next example:<\/p>\n\n\n\n
4) Accessing protected data as u2<\/span><\/path><\/path><\/svg><\/span>
create or replace<\/span> procedure u1.suppress_plsql_delcarations_handler(<\/span><\/span>\n   in_object_owner <\/span>in<\/span> <\/span>varchar2<\/span>,<\/span><\/span>\n   in_object_name  <\/span>in<\/span> <\/span>varchar2<\/span>,<\/span><\/span>\n   in_policy_name  <\/span>in<\/span> <\/span>varchar2<\/span><\/span>\n) <\/span>is<\/span><\/span>\nbegin<\/span><\/span>\n   <\/span>if<\/span> regexp_like(<\/span>sys_context<\/span>(<\/span>'userenv'<\/span>, <\/span>'current_sql'<\/span>), <\/span>'with[^fp]+(function|procedure)'<\/span>, <\/span>'i'<\/span>) <\/span>then<\/span><\/span>\n      <\/span>raise_application_error<\/span>(-<\/span>20942<\/span>, <\/span>'plsql_declarations are not allowed in the with_clause. See policy '<\/span><\/span>\n         || in_policy_name<\/span><\/span>\n         || <\/span>' for '<\/span><\/span>\n         || in_object_owner<\/span><\/span>\n         || <\/span>'.'<\/span><\/span>\n         || in_object_name);<\/span><\/span>\n   <\/span>end if<\/span>;<\/span><\/span>\nend<\/span>;<\/span><\/span>\n\/<\/span><\/span>\n<\/span>\nbegin<\/span><\/span>\n   <\/span>dbms_fga.<\/span>add_policy<\/span>(<\/span><\/span>\n      object_schema   => <\/span>'U1'<\/span>,<\/span><\/span>\n      object_name     => <\/span>'V'<\/span>,<\/span><\/span>\n      policy_name     => <\/span>'SUPPRESS_PLSQL_DELCARATIONS_POLICY'<\/span>,<\/span><\/span>\n      handler_schema  => <\/span>'U1'<\/span>,<\/span><\/span>\n      handler_module  => <\/span>'SUPPRESS_PLSQL_DELCARATIONS_HANDLER'<\/span>,<\/span><\/span>\n      statement_types => <\/span>'SELECT,INSERT,UPDATE,DELETE'<\/span><\/span>\n   );<\/span><\/span>\nend<\/span>;<\/span><\/span>\n\/<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
You have to register a policy for each view. This can be easily generated. However, you should consider that such a policy has an impact on performance. Therefore, you should define such a policy only when needed.<\/p>\n\n\n\n
\n\n\n\n
Updated on 2022-11-03, updated workaround regarding comments before plsql_declarations<\/code> and support for all DML statements.<\/em><\/p>\n\n\n\n
Updated on 2023-03-13, added reference to bug 34777606 (not visible), fixed in 23c, no information regarding backport yet.<\/em><\/p>\n\n\n\n
Updated on 2023-03-27,  “Bug 34255928 – INCORRECT SECURE VIEW MERGING FOR A QUERY WITH A FUNCTION DEFINED IN ITS WITH CLAUSE” fixed in 23c, backport possible as a one-off patch via SR. No plans to include it in an RU for 19c.<\/em><\/p>\n\n\n\n
Updated on 2023-04-05: Tested successfully in 23c, Developer-Release Version 23.2.0.0.0<\/span><\/em><\/p>\n","protected":false},"excerpt":{"rendered":"
The Original Problem A customer is currently upgrading some Oracle databases from 11.2 to 19c. One query was extremely slow on the new test system and my job was to find out why. The root cause was that the database parameter optimizer_secure_view_merging was set to a different value. In 19c true and [\u2026]<\/span><\/p>\n","protected":false},"author":1,"featured_media":11910,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[127,109,13,85],"class_list":["post-11897","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-oracle","tag-autonomous-database","tag-pinkdb","tag-plsql","tag-sql"],"yoast_head":"\noptimizer_secure_view_merging and plsql_declarations - Philipp Salvisberg's Blog<\/title>\n<meta name=\"description\" content=\"You may feel safe when optimizer_secure_view_merging is enabled. Still, you can gain access to protected data via plsql_declarations.\" \/>\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.salvis.com\/blog\/2022\/10\/30\/optimizer_secure_view_merging-and-plsql_declarations\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"optimizer_secure_view_merging and plsql_declarations - Philipp Salvisberg's Blog\" \/>\n<meta property=\"og:description\" content=\"You may feel safe when optimizer_secure_view_merging is enabled. Still, you can gain access to protected data via plsql_declarations.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.salvis.com\/blog\/2022\/10\/30\/optimizer_secure_view_merging-and-plsql_declarations\/\" \/>\n<meta property=\"og:site_name\" content=\"Philipp Salvisberg's Blog\" \/>\n<meta property=\"article:published_time\" content=\"2022-10-30T11:56:32+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-11-08T17:04:24+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2022\/10\/cyber-security-digital-2820828.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1200\" \/>\n\t<meta property=\"og:image:height\" content=\"1200\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Philipp Salvisberg\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@phsalvisberg\" \/>\n<meta name=\"twitter:site\" content=\"@phsalvisberg\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Philipp Salvisberg\" \/>\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.salvis.com\\\/blog\\\/2022\\\/10\\\/30\\\/optimizer_secure_view_merging-and-plsql_declarations\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2022\\\/10\\\/30\\\/optimizer_secure_view_merging-and-plsql_declarations\\\/\"},\"author\":{\"name\":\"Philipp Salvisberg\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\"},\"headline\":\"optimizer_secure_view_merging and plsql_declarations\",\"datePublished\":\"2022-10-30T11:56:32+00:00\",\"dateModified\":\"2023-11-08T17:04:24+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2022\\\/10\\\/30\\\/optimizer_secure_view_merging-and-plsql_declarations\\\/\"},\"wordCount\":1105,\"commentCount\":3,\"publisher\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\"},\"image\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2022\\\/10\\\/30\\\/optimizer_secure_view_merging-and-plsql_declarations\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/10\\\/cyber-security-digital-2820828.jpg\",\"keywords\":[\"Autonomous Database\",\"PinkDB\",\"PL\\\/SQL\",\"SQL\"],\"articleSection\":[\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2022\\\/10\\\/30\\\/optimizer_secure_view_merging-and-plsql_declarations\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2022\\\/10\\\/30\\\/optimizer_secure_view_merging-and-plsql_declarations\\\/\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2022\\\/10\\\/30\\\/optimizer_secure_view_merging-and-plsql_declarations\\\/\",\"name\":\"optimizer_secure_view_merging and plsql_declarations - Philipp Salvisberg's Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2022\\\/10\\\/30\\\/optimizer_secure_view_merging-and-plsql_declarations\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2022\\\/10\\\/30\\\/optimizer_secure_view_merging-and-plsql_declarations\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/10\\\/cyber-security-digital-2820828.jpg\",\"datePublished\":\"2022-10-30T11:56:32+00:00\",\"dateModified\":\"2023-11-08T17:04:24+00:00\",\"description\":\"You may feel safe when optimizer_secure_view_merging is enabled. Still, you can gain access to protected data via plsql_declarations.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2022\\\/10\\\/30\\\/optimizer_secure_view_merging-and-plsql_declarations\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2022\\\/10\\\/30\\\/optimizer_secure_view_merging-and-plsql_declarations\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2022\\\/10\\\/30\\\/optimizer_secure_view_merging-and-plsql_declarations\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/10\\\/cyber-security-digital-2820828.jpg\",\"contentUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/10\\\/cyber-security-digital-2820828.jpg\",\"width\":1200,\"height\":1200,\"caption\":\"optimizer_secure_view_merging=true\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2022\\\/10\\\/30\\\/optimizer_secure_view_merging-and-plsql_declarations\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"optimizer_secure_view_merging and plsql_declarations\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/\",\"name\":\"Philipp Salvisberg's Blog\",\"description\":\"Database-centric development\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\",\"name\":\"Philipp Salvisberg\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\",\"contentUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\",\"width\":400,\"height\":400,\"caption\":\"Philipp Salvisberg\"},\"logo\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\"},\"sameAs\":[\"http:\\\/\\\/www.salvis.com\\\/\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"optimizer_secure_view_merging and plsql_declarations - Philipp Salvisberg's Blog","description":"You may feel safe when optimizer_secure_view_merging is enabled. Still, you can gain access to protected data via plsql_declarations.","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.salvis.com\/blog\/2022\/10\/30\/optimizer_secure_view_merging-and-plsql_declarations\/","og_locale":"en_US","og_type":"article","og_title":"optimizer_secure_view_merging and plsql_declarations - Philipp Salvisberg's Blog","og_description":"You may feel safe when optimizer_secure_view_merging is enabled. Still, you can gain access to protected data via plsql_declarations.","og_url":"https:\/\/www.salvis.com\/blog\/2022\/10\/30\/optimizer_secure_view_merging-and-plsql_declarations\/","og_site_name":"Philipp Salvisberg's Blog","article_published_time":"2022-10-30T11:56:32+00:00","article_modified_time":"2023-11-08T17:04:24+00:00","og_image":[{"width":1200,"height":1200,"url":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2022\/10\/cyber-security-digital-2820828.jpg","type":"image\/jpeg"}],"author":"Philipp Salvisberg","twitter_card":"summary_large_image","twitter_creator":"@phsalvisberg","twitter_site":"@phsalvisberg","twitter_misc":{"Written by":"Philipp Salvisberg","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.salvis.com\/blog\/2022\/10\/30\/optimizer_secure_view_merging-and-plsql_declarations\/#article","isPartOf":{"@id":"https:\/\/www.salvis.com\/blog\/2022\/10\/30\/optimizer_secure_view_merging-and-plsql_declarations\/"},"author":{"name":"Philipp Salvisberg","@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515"},"headline":"optimizer_secure_view_merging and plsql_declarations","datePublished":"2022-10-30T11:56:32+00:00","dateModified":"2023-11-08T17:04:24+00:00","mainEntityOfPage":{"@id":"https:\/\/www.salvis.com\/blog\/2022\/10\/30\/optimizer_secure_view_merging-and-plsql_declarations\/"},"wordCount":1105,"commentCount":3,"publisher":{"@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515"},"image":{"@id":"https:\/\/www.salvis.com\/blog\/2022\/10\/30\/optimizer_secure_view_merging-and-plsql_declarations\/#primaryimage"},"thumbnailUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2022\/10\/cyber-security-digital-2820828.jpg","keywords":["Autonomous Database","PinkDB","PL\/SQL","SQL"],"articleSection":["Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.salvis.com\/blog\/2022\/10\/30\/optimizer_secure_view_merging-and-plsql_declarations\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.salvis.com\/blog\/2022\/10\/30\/optimizer_secure_view_merging-and-plsql_declarations\/","url":"https:\/\/www.salvis.com\/blog\/2022\/10\/30\/optimizer_secure_view_merging-and-plsql_declarations\/","name":"optimizer_secure_view_merging and plsql_declarations - Philipp Salvisberg's Blog","isPartOf":{"@id":"https:\/\/www.salvis.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.salvis.com\/blog\/2022\/10\/30\/optimizer_secure_view_merging-and-plsql_declarations\/#primaryimage"},"image":{"@id":"https:\/\/www.salvis.com\/blog\/2022\/10\/30\/optimizer_secure_view_merging-and-plsql_declarations\/#primaryimage"},"thumbnailUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2022\/10\/cyber-security-digital-2820828.jpg","datePublished":"2022-10-30T11:56:32+00:00","dateModified":"2023-11-08T17:04:24+00:00","description":"You may feel safe when optimizer_secure_view_merging is enabled. Still, you can gain access to protected data via plsql_declarations.","breadcrumb":{"@id":"https:\/\/www.salvis.com\/blog\/2022\/10\/30\/optimizer_secure_view_merging-and-plsql_declarations\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.salvis.com\/blog\/2022\/10\/30\/optimizer_secure_view_merging-and-plsql_declarations\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.salvis.com\/blog\/2022\/10\/30\/optimizer_secure_view_merging-and-plsql_declarations\/#primaryimage","url":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2022\/10\/cyber-security-digital-2820828.jpg","contentUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2022\/10\/cyber-security-digital-2820828.jpg","width":1200,"height":1200,"caption":"optimizer_secure_view_merging=true"},{"@type":"BreadcrumbList","@id":"https:\/\/www.salvis.com\/blog\/2022\/10\/30\/optimizer_secure_view_merging-and-plsql_declarations\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.salvis.com\/blog\/"},{"@type":"ListItem","position":2,"name":"optimizer_secure_view_merging and plsql_declarations"}]},{"@type":"WebSite","@id":"https:\/\/www.salvis.com\/blog\/#website","url":"https:\/\/www.salvis.com\/blog\/","name":"Philipp Salvisberg's Blog","description":"Database-centric development","publisher":{"@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.salvis.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515","name":"Philipp Salvisberg","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg","url":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg","contentUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg","width":400,"height":400,"caption":"Philipp Salvisberg"},"logo":{"@id":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg"},"sameAs":["http:\/\/www.salvis.com\/"]}]}},"_links":{"self":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts\/11897","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/comments?post=11897"}],"version-history":[{"count":56,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts\/11897\/revisions"}],"predecessor-version":[{"id":12728,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts\/11897\/revisions\/12728"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/media\/11910"}],"wp:attachment":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/media?parent=11897"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/categories?post=11897"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/tags?post=11897"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
Updated on 2022-11-02, documented workaround via DBMS_FGA<\/a>.<\/em><\/p>\n\n\n\n