{"id":1798,"date":"2018-05-08T15:51:55","date_gmt":"2018-05-08T15:51:55","guid":{"rendered":"http:\/\/nenadnoveljic.com\/blog\/?p=1798"},"modified":"2021-08-31T16:10:17","modified_gmt":"2021-08-31T16:10:17","slug":"data-redaction-virtual-columns","status":"publish","type":"post","link":"https:\/\/nenadnoveljic.com\/blog\/data-redaction-virtual-columns\/","title":{"rendered":"Data Redaction and Virtual Columns"},"content":{"rendered":"

Prior to 12.2, data redaction was unsafe if used in conjunction with virtual columns. In fact, it was possible to select virtual column data even for the virtual columns based on a redacted column. Fortunately, this security breach was fixed in 12.2. Now, the error “ORA-28081: Insufficient privileges – the command references a redacted object” gets raised when selecting from a redacted table.<\/p>\n

Ivica Arsov described this behaviour in his blog post<\/a>.<\/p>\n

However, the current implementation seems to impose some unexpected restrictions. I mean the error is being generated even if we select a column which is not redacted, on condition that a virtual column which is derived from a redacted column is defined in the table. Let me demonstrate this point with a simple test case which I ran on a 12.2.0.1.180116 database.<\/p>\n

Firstly, I’m going to create the table O.T containing the virtual column N3 which depends on N1. It’s worth noting that the column N1 is redacted for everyone.<\/p>\n

create user o identified by Password$111 ;\ngrant create session,resource, unlimited tablespace to o ;\ncreate table o.t (\n  n1 number, n2 number, n3 number generated always as (n1+1) virtual \n) ;\n\nBEGIN\nDBMS_REDACT.add_policy(\n  object_schema => 'O',\n  object_name => 'T',\n  column_name => 'N1',\n  policy_name => 'redact_pol',\n  function_type => DBMS_REDACT.full,\n  expression => '1=1'\n);\nEND;\n\/\n\ncreate user u identified by Password$111 ;\ngrant create session to u ;\ngrant select on o.t to u ;\n<\/code><\/pre>\n
Then, I’m going to select the column N2 which is neither redacted nor virtual nor referenced by any virtual column.<\/p>\n
connect u\/Password$111\nselect n2 from o.t ;\n\nERROR at line 1:\nORA-28081: Insufficient privileges - the command references a redacted object.\n<\/code><\/pre>\n
As you can see, the error was raised to protect the data, although we didn’t try to query any redacted information. In other words, a defined virtual column derived from a redacted column will unnecessarily cause the error, regardless of whether we reference it in the select command.<\/p>\n
Consequently, the select statement above will also keep failing as long as there are some extended statistics on a redacted column.<\/p>\n","protected":false},"excerpt":{"rendered":"
Data Redaction limitation in Oracle 12.2 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":[22,45,5],"tags":[],"class_list":["post-1798","post","type-post","status-publish","format-standard","hentry","category-12-2","category-data-redaction","category-oracle"],"yoast_head":"\nData Redaction and Virtual Columns - All-round Database Topics<\/title>\n<meta name=\"description\" content=\"Data Redaction limitation in Oracle 12.2\" \/>\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\/data-redaction-virtual-columns\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Data Redaction and Virtual Columns - All-round Database Topics\" \/>\n<meta property=\"og:description\" content=\"Data Redaction limitation in Oracle 12.2\" \/>\n<meta property=\"og:url\" content=\"https:\/\/nenadnoveljic.com\/blog\/data-redaction-virtual-columns\/\" \/>\n<meta property=\"og:site_name\" content=\"All-round Database Topics\" \/>\n<meta property=\"article:published_time\" content=\"2018-05-08T15:51:55+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-08-31T16:10:17+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=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/data-redaction-virtual-columns\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/data-redaction-virtual-columns\\\/\"},\"author\":{\"name\":\"Nenad Noveljic\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#\\\/schema\\\/person\\\/51458d9dd86dbbdd19f5add451d44efa\"},\"headline\":\"Data Redaction and Virtual Columns\",\"datePublished\":\"2018-05-08T15:51:55+00:00\",\"dateModified\":\"2021-08-31T16:10:17+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/data-redaction-virtual-columns\\\/\"},\"wordCount\":258,\"commentCount\":0,\"articleSection\":[\"12.2\",\"Data Redaction\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/data-redaction-virtual-columns\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/data-redaction-virtual-columns\\\/\",\"url\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/data-redaction-virtual-columns\\\/\",\"name\":\"Data Redaction and Virtual Columns - All-round Database Topics\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#website\"},\"datePublished\":\"2018-05-08T15:51:55+00:00\",\"dateModified\":\"2021-08-31T16:10:17+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#\\\/schema\\\/person\\\/51458d9dd86dbbdd19f5add451d44efa\"},\"description\":\"Data Redaction limitation in Oracle 12.2\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/data-redaction-virtual-columns\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/data-redaction-virtual-columns\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/data-redaction-virtual-columns\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Data Redaction and Virtual Columns\"}]},{\"@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":"Data Redaction and Virtual Columns - All-round Database Topics","description":"Data Redaction limitation in Oracle 12.2","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\/data-redaction-virtual-columns\/","og_locale":"en_US","og_type":"article","og_title":"Data Redaction and Virtual Columns - All-round Database Topics","og_description":"Data Redaction limitation in Oracle 12.2","og_url":"https:\/\/nenadnoveljic.com\/blog\/data-redaction-virtual-columns\/","og_site_name":"All-round Database Topics","article_published_time":"2018-05-08T15:51:55+00:00","article_modified_time":"2021-08-31T16:10:17+00:00","author":"Nenad Noveljic","twitter_card":"summary_large_image","twitter_creator":"@NenadNoveljic","twitter_misc":{"Written by":"Nenad Noveljic","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/nenadnoveljic.com\/blog\/data-redaction-virtual-columns\/#article","isPartOf":{"@id":"https:\/\/nenadnoveljic.com\/blog\/data-redaction-virtual-columns\/"},"author":{"name":"Nenad Noveljic","@id":"https:\/\/nenadnoveljic.com\/blog\/#\/schema\/person\/51458d9dd86dbbdd19f5add451d44efa"},"headline":"Data Redaction and Virtual Columns","datePublished":"2018-05-08T15:51:55+00:00","dateModified":"2021-08-31T16:10:17+00:00","mainEntityOfPage":{"@id":"https:\/\/nenadnoveljic.com\/blog\/data-redaction-virtual-columns\/"},"wordCount":258,"commentCount":0,"articleSection":["12.2","Data Redaction","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/nenadnoveljic.com\/blog\/data-redaction-virtual-columns\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/nenadnoveljic.com\/blog\/data-redaction-virtual-columns\/","url":"https:\/\/nenadnoveljic.com\/blog\/data-redaction-virtual-columns\/","name":"Data Redaction and Virtual Columns - All-round Database Topics","isPartOf":{"@id":"https:\/\/nenadnoveljic.com\/blog\/#website"},"datePublished":"2018-05-08T15:51:55+00:00","dateModified":"2021-08-31T16:10:17+00:00","author":{"@id":"https:\/\/nenadnoveljic.com\/blog\/#\/schema\/person\/51458d9dd86dbbdd19f5add451d44efa"},"description":"Data Redaction limitation in Oracle 12.2","breadcrumb":{"@id":"https:\/\/nenadnoveljic.com\/blog\/data-redaction-virtual-columns\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/nenadnoveljic.com\/blog\/data-redaction-virtual-columns\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/nenadnoveljic.com\/blog\/data-redaction-virtual-columns\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/nenadnoveljic.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Data Redaction and Virtual Columns"}]},{"@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\/1798","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=1798"}],"version-history":[{"count":1,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts\/1798\/revisions"}],"predecessor-version":[{"id":3896,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts\/1798\/revisions\/3896"}],"wp:attachment":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/media?parent=1798"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/categories?post=1798"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/tags?post=1798"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}