{"id":3890,"date":"2021-08-31T16:08:44","date_gmt":"2021-08-31T16:08:44","guid":{"rendered":"https:\/\/nenadnoveljic.com\/blog\/?p=3890"},"modified":"2021-10-29T18:58:16","modified_gmt":"2021-10-29T18:58:16","slug":"apex-data-redaction-conflict","status":"publish","type":"post","link":"https:\/\/nenadnoveljic.com\/blog\/apex-data-redaction-conflict\/","title":{"rendered":"Apex\/Data Redaction Conflict"},"content":{"rendered":"

The Apex 18c and higher may conflict with Data Redaction.<\/p>\n

Data Redaction<\/h1>\n

Mikhail Velikikh described a serious limitation in Data Redaction<\/a>:<\/p>\n

\nIn a CREATE VIEW definition or an inline view, there cannot be any SQL expression that involves a redacted column.<\/p><\/blockquote>\n

I slightly modified Mikhail’s test case:<\/p>\n

create table u.t1 ( first_name varchar2(30), last_name varchar2(30) ) ;\n\nBEGIN\n  DBMS_REDACT.add_policy(\n    object_schema => 'u',\n    object_name => 't1',\n    column_name => 'first_name',\n    policy_name => 'redact_t1',\n    function_type => DBMS_REDACT.full,\n    expression => '1=1'\n  );\nEND;\n\nBEGIN \n  DBMS_REDACT.alter_policy( \n    object_schema => 'u', \n    object_name => 't1', \n    column_name => 'last_name', \n    policy_name => 'redact_t1', \n    function_type => DBMS_REDACT.full \n  ); \nEND;<\/code><\/pre>\n
The following queries, executed by another user, demonstrate what works and what doesn’t:<\/p>\n
SQL> alter session set current_schema = u ;\n\nSession altered.\n\nSQL> select first_name || ' ' || last_name from t1 ;\n\nno rows selected\n\nSQL> select * from ( select first_name || ' ' || last_name from t1 ) ;\n                                                           *\nERROR at line 1:\nORA-28094: SQL construct not supported by data redaction\n\n\nSQL> select * from ( select first_name,  last_name from t1 ) ;\n\nno rows selected<\/code><\/pre>\n
Simply put, the query breaks when it contains a SQL expression – in this case concatenation – on redacted columns in the inner query block (QB).<\/p>\n
You can overcome this limitation by flipping the undocumented parameter “_strict_redaction_semantics” to “false”, but that’s the uncharted territory. Apart from Mikhail’s blog post, I couldn’t find any other document mentioning this parameter.<\/p>\n
Apex<\/h1>\n
The following query works in SQLPlus but it doesn’t when we embed it in a Classic Report in Apex versions 18c or higher:<\/p>\n
select first_name || ' ' || last_name from t1<\/code><\/pre>\n
It’s failing with:<\/p>\n
ORA-28094: SQL construct not supported by data redaction<\/code><\/pre>\n
We can set the trace event to see what was executed:<\/p>\n
alter system set events '28094 trace name errorstack forever, level 1';<\/code><\/pre>\n
This is what Apex actually sent:<\/p>\n
select * from(select a.*,row_number() over (order by null) apx$rownum from(select i.*\n from (select \"NAME\"\nfrom ((select \/*+ qb_name(apex$inner) *\/d.\"NAME\" from (select first_name || ' ' || last_name name from t1\n) d\n )) i\n) i where 1=1\norder by \"NAME\" asc nulls last\n)a\n)where apx$rownum<=:p$_max_rows<\/code><\/pre>\n
Simply put, it embedded our original query in the inner query block (QB). The outer QB looks like it’s underpinning pagination.<\/p>\n
Carsten Czarski informs us<\/a> that pagination indeed underwent major improvements in 18c. Sadly, it also created a conflict with Data Redaction.<\/p>\n
There was a shimmer of hope – the APEX$USE_NO_PAGINATION hint is supposed to revert the pagination to the APEX 5.1 behavior. As it turned out, the hint really removes the pagination, but, unfortunately, still places the original query in the inner QB:<\/p>\n
select --+APEX$USE_NO_PAGINATION\n* from (select i.*\n from (select \"NAME\"\nfrom ((select \/*+ qb_name(apex$inner) *\/d.\"NAME\" from (select first_name || ' ' || last_name name from t1\n) d\n )) i\n) i where 1=1\norder by \"NAME\" asc nulls last<\/code><\/pre>\n
So the old behavior isn’t fully emulated – Apex 5.1 doesn’t wrap the query, at least not for Classical Report:<\/p>\n
select first_name || ' ' || last_name name from t1\n order by 1<\/code><\/pre>\n
Currently, it seems that our only option is to materialize calculated expressions in separate columns. In this example, we would need to store the expression “first_name || ‘ ‘ || last_name” in its own column “name”.<\/p>\n
As I already mentioned, it’s also possible to prevent the Data Redaction restriction with the undocumented parameter, but that’s unsupported for now.<\/p>\n","protected":false},"excerpt":{"rendered":"
Reports containing SQL expressions with redacted columns will break in versions 18c higher. Worth checking before Apex upgrade. Continue Reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"footnotes":""},"categories":[29,36,46,47,45,5],"tags":[],"class_list":["post-3890","post","type-post","status-publish","format-standard","hentry","category-18c","category-19c","category-21c","category-apex","category-data-redaction","category-oracle"],"yoast_head":"\nApex\/Data Redaction Conflict - All-round Database Topics<\/title>\n<meta name=\"description\" content=\"Reports containing SQL expressions with redacted columns will break in versions 18c higher. Worth checking before Apex upgrade.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/nenadnoveljic.com\/blog\/apex-data-redaction-conflict\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Apex\/Data Redaction Conflict - All-round Database Topics\" \/>\n<meta property=\"og:description\" content=\"Reports containing SQL expressions with redacted columns will break in versions 18c higher. Worth checking before Apex upgrade.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/nenadnoveljic.com\/blog\/apex-data-redaction-conflict\/\" \/>\n<meta property=\"og:site_name\" content=\"All-round Database Topics\" \/>\n<meta property=\"article:published_time\" content=\"2021-08-31T16:08:44+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-10-29T18:58:16+00:00\" \/>\n<meta name=\"author\" content=\"Nenad Noveljic\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@NenadNoveljic\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Nenad Noveljic\" \/>\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:\\\/\\\/nenadnoveljic.com\\\/blog\\\/apex-data-redaction-conflict\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/apex-data-redaction-conflict\\\/\"},\"author\":{\"name\":\"Nenad Noveljic\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#\\\/schema\\\/person\\\/51458d9dd86dbbdd19f5add451d44efa\"},\"headline\":\"Apex\\\/Data Redaction Conflict\",\"datePublished\":\"2021-08-31T16:08:44+00:00\",\"dateModified\":\"2021-10-29T18:58:16+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/apex-data-redaction-conflict\\\/\"},\"wordCount\":341,\"commentCount\":0,\"articleSection\":[\"18c\",\"19c\",\"21c\",\"Apex\",\"Data Redaction\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/apex-data-redaction-conflict\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/apex-data-redaction-conflict\\\/\",\"url\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/apex-data-redaction-conflict\\\/\",\"name\":\"Apex\\\/Data Redaction Conflict - All-round Database Topics\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#website\"},\"datePublished\":\"2021-08-31T16:08:44+00:00\",\"dateModified\":\"2021-10-29T18:58:16+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#\\\/schema\\\/person\\\/51458d9dd86dbbdd19f5add451d44efa\"},\"description\":\"Reports containing SQL expressions with redacted columns will break in versions 18c higher. Worth checking before Apex upgrade.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/apex-data-redaction-conflict\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/apex-data-redaction-conflict\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/apex-data-redaction-conflict\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Apex\\\/Data Redaction Conflict\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/\",\"name\":\"All-round Database Topics\",\"description\":\"Nenad Noveljic\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#\\\/schema\\\/person\\\/51458d9dd86dbbdd19f5add451d44efa\",\"name\":\"Nenad Noveljic\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g\",\"caption\":\"Nenad Noveljic\"},\"sameAs\":[\"nenad-noveljic-9b746a6\",\"https:\\\/\\\/x.com\\\/NenadNoveljic\"],\"url\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/author\\\/nenad\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Apex\/Data Redaction Conflict - All-round Database Topics","description":"Reports containing SQL expressions with redacted columns will break in versions 18c higher. Worth checking before Apex upgrade.","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:\/\/nenadnoveljic.com\/blog\/apex-data-redaction-conflict\/","og_locale":"en_US","og_type":"article","og_title":"Apex\/Data Redaction Conflict - All-round Database Topics","og_description":"Reports containing SQL expressions with redacted columns will break in versions 18c higher. Worth checking before Apex upgrade.","og_url":"https:\/\/nenadnoveljic.com\/blog\/apex-data-redaction-conflict\/","og_site_name":"All-round Database Topics","article_published_time":"2021-08-31T16:08:44+00:00","article_modified_time":"2021-10-29T18:58:16+00:00","author":"Nenad Noveljic","twitter_card":"summary_large_image","twitter_creator":"@NenadNoveljic","twitter_misc":{"Written by":"Nenad Noveljic","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/nenadnoveljic.com\/blog\/apex-data-redaction-conflict\/#article","isPartOf":{"@id":"https:\/\/nenadnoveljic.com\/blog\/apex-data-redaction-conflict\/"},"author":{"name":"Nenad Noveljic","@id":"https:\/\/nenadnoveljic.com\/blog\/#\/schema\/person\/51458d9dd86dbbdd19f5add451d44efa"},"headline":"Apex\/Data Redaction Conflict","datePublished":"2021-08-31T16:08:44+00:00","dateModified":"2021-10-29T18:58:16+00:00","mainEntityOfPage":{"@id":"https:\/\/nenadnoveljic.com\/blog\/apex-data-redaction-conflict\/"},"wordCount":341,"commentCount":0,"articleSection":["18c","19c","21c","Apex","Data Redaction","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/nenadnoveljic.com\/blog\/apex-data-redaction-conflict\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/nenadnoveljic.com\/blog\/apex-data-redaction-conflict\/","url":"https:\/\/nenadnoveljic.com\/blog\/apex-data-redaction-conflict\/","name":"Apex\/Data Redaction Conflict - All-round Database Topics","isPartOf":{"@id":"https:\/\/nenadnoveljic.com\/blog\/#website"},"datePublished":"2021-08-31T16:08:44+00:00","dateModified":"2021-10-29T18:58:16+00:00","author":{"@id":"https:\/\/nenadnoveljic.com\/blog\/#\/schema\/person\/51458d9dd86dbbdd19f5add451d44efa"},"description":"Reports containing SQL expressions with redacted columns will break in versions 18c higher. Worth checking before Apex upgrade.","breadcrumb":{"@id":"https:\/\/nenadnoveljic.com\/blog\/apex-data-redaction-conflict\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/nenadnoveljic.com\/blog\/apex-data-redaction-conflict\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/nenadnoveljic.com\/blog\/apex-data-redaction-conflict\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/nenadnoveljic.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Apex\/Data Redaction Conflict"}]},{"@type":"WebSite","@id":"https:\/\/nenadnoveljic.com\/blog\/#website","url":"https:\/\/nenadnoveljic.com\/blog\/","name":"All-round Database Topics","description":"Nenad Noveljic","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/nenadnoveljic.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/nenadnoveljic.com\/blog\/#\/schema\/person\/51458d9dd86dbbdd19f5add451d44efa","name":"Nenad Noveljic","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g","caption":"Nenad Noveljic"},"sameAs":["nenad-noveljic-9b746a6","https:\/\/x.com\/NenadNoveljic"],"url":"https:\/\/nenadnoveljic.com\/blog\/author\/nenad\/"}]}},"_links":{"self":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts\/3890","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/comments?post=3890"}],"version-history":[{"count":1,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts\/3890\/revisions"}],"predecessor-version":[{"id":4022,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts\/3890\/revisions\/4022"}],"wp:attachment":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/media?parent=3890"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/categories?post=3890"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/tags?post=3890"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}