{"id":2021,"date":"2018-12-07T21:36:04","date_gmt":"2018-12-07T20:36:04","guid":{"rendered":"http:\/\/van-maanen.com\/?p=2021"},"modified":"2018-12-07T21:36:04","modified_gmt":"2018-12-07T20:36:04","slug":"masking-in-oracle","status":"publish","type":"post","link":"http:\/\/archief.van-maanen.com\/?p=2021","title":{"rendered":"Masking in Oracle"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Oracle has recently introduced masking as one of the facilities. Masking allows data to be hidden from users \/ or only display part of the content. Other users can still the normal content. Let us take an example. A salary can shown as a random number to some users while others might still see the original content.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">A well known example is a credit card number. If I look at my Amazon account, I see that my credit card is shown as &#8220;Master Card ending in 6211&#8221;. The original content is masked: only part is shown. As the last numbers of my credit card is shown, I know Amazon has my correct credit card number. On the other hand such display is sure; if someone sees my screen, he has no knowledge of my credit card number.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Masking is fundamentally different from encrypting. If my data are masked, my data are either not shown or just partly shown to some users. The data might still be stored in a plain form, leaving them exposed to whomever has access to them. Encrypted data are not exposed; even a dba can not read them if he doesn&#8217;t have the key to decrypt them. Some dba even do not want to have keys to, say, the encrypted passwords.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If one would like to use this Oracle facility, you should has have the newest version of Oracle (12c) and the newest version of SQL developer (version 18 or above).\u00a0<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Next grants are necessary to implement masking:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">grant select on Sys.redaction_policies to C##SCOTT;<br>grant select on Sys.redaction_columns to C##SCOTT;<br>grant execute on dbms_redact to C##SCOTT;<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">If we then log in as C##SCOTT, we can select a table in SQL developer. Right click on the table and choose the option &#8220;redaction&#8221;:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"http:\/\/van-maanen.com\/wp-content\/uploads\/2018\/12\/Untitled-9-9.png\" alt=\"\" class=\"wp-image-2053\"\/><figcaption>setting masking rules<\/figcaption><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">The menu is rather straight forward. One must indicate the scheme, table, column and the masking type:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"http:\/\/van-maanen.com\/wp-content\/uploads\/2018\/12\/Untitled-9-1-1-1.png\" alt=\"\" class=\"wp-image-2054\"\/><figcaption>filling out masking rules<\/figcaption><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">Click on &#8220;apply&#8221; and the rule is set. One may verify this by selecting the data from the table:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"http:\/\/van-maanen.com\/wp-content\/uploads\/2018\/12\/Untitled-9-2-1-1.png\" alt=\"\" class=\"wp-image-2060\"\/><figcaption>data are masked<\/figcaption><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">A final note: we may exempt some users from the masking rule. In above menu, we have exempted c##kijk from the masking rule. C##kijk is created by:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">create user c##kijk identified by \"AA1234!!\";&nbsp;<br>grant connect, resource to C##kijk;<br>grant select on c##redact_user.employees to c##kijk;<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">And if log in as c##kijk, we see unmasked values:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"http:\/\/van-maanen.com\/wp-content\/uploads\/2018\/12\/Untitled-9-3-1-1.png\" alt=\"\" class=\"wp-image-2061\"\/><figcaption>original values<\/figcaption><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>Oracle has recently introduced masking as one of the facilities. Masking allows data to be hidden from users \/ or only display part of the content. Other users can still the normal content. Let us take an example. A salary can shown as a random number to some users while others might still see the [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[],"class_list":["post-2021","post","type-post","status-publish","format-standard","hentry","category-nice-to-know"],"_links":{"self":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/2021","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2021"}],"version-history":[{"count":0,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/2021\/revisions"}],"wp:attachment":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2021"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2021"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2021"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}