{"id":9458,"date":"2019-05-28T09:01:45","date_gmt":"2019-05-28T09:01:45","guid":{"rendered":"https:\/\/ittutorial.org\/?p=9458"},"modified":"2020-08-27T20:01:03","modified_gmt":"2020-08-27T20:01:03","slug":"data-redaction-dbms_redact-in-oracle-database-2","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/data-redaction-dbms_redact-in-oracle-database-2\/","title":{"rendered":"Data Redaction ( dbms_redact ) in Oracle Database -2"},"content":{"rendered":"

Hi,<\/p>\n

I will continue to explain\u00a0Data Redaction in Oracle Database in this article.<\/p>\n

\"\"<\/p>\n

<\/p>\n\n

 <\/p>\n

If you don’t know what is the Data Redaction, you should read following article.<\/p>\n

https:\/\/ittutorial.org\/data-redaction-in-oracle-database-1\/<\/p>\n

 <\/p>\n

 <\/p>\n

Unauthorized users see the same data differently and Redacted, therefore Oracle Data Redaction is a very powerful feature. Because companies have a lot of problems due to security weaknesses nowadays.<\/p>\n

 <\/p>\n

For example; Call centers call you and ask your mother maiden name’s only some characters for Security. Because Call Center Staff shouldn’t also see private data and they should also see as Redacted like * and x character<\/p>\n

 <\/p>\n

\"\"<\/p>\n

 <\/p>\n

 <\/p>\n

Let’s continue to make new examples to understand this subject very well.<\/p>\n

Table name and its column definition are like following.<\/p>\n

 <\/p>\n

Kart_bilgileri–> Card info table<\/p>\n

Musteri_id —> Customer id<\/p>\n

kart_no —> Card no<\/p>\n

kart_string —> card no string type<\/p>\n

biti\u015f_tarihi —> Expiry date<\/p>\n

guvenlik_no —> Security Code or no<\/p>\n

 <\/p>\n

 <\/p>\n

CREATE TABLE mehmet.kart_bilgileri (\r\nid NUMBER NOT NULL,\r\nmusteri_id NUMBER NOT NULL,\r\nkart_no NUMBER NOT NULL,\r\nkart_string VARCHAR2(19) NOT NULL,\r\nbitis_tarihi DATE NOT NULL,\r\nguvenlik_no NUMBER NOT NULL,\r\ngecerlilik_tarihi DATE,\r\nCONSTRAINT mehmet.kart_bilgileri_pk PRIMARY KEY (id)\r\n);\r\n\r\nINSERT INTO mehmet.kart_bilgileri VALUES (1, 1000, 2013201320132013, '2013-2013-2013-2013', TRUNC(ADD_MONTHS(SYSDATE,12)), 123, NULL);\r\nINSERT INTO mehmet.kart_bilgileri VALUES (2, 1001, 1989198919891989, '1989-1989-1989-1989', TRUNC(ADD_MONTHS(SYSDATE,12)), 234, NULL);\r\nINSERT INTO mehmet.kart_bilgileri VALUES (3, 1002, 2018201820182018, '2018-2018-2018-2018', TRUNC(ADD_MONTHS(SYSDATE,12)), 345, NULL);\r\nINSERT INTO mehmet.kart_bilgileri VALUES (4, 1003, 1453145314531453, '1453-1453-1453-1453', TRUNC(ADD_MONTHS(SYSDATE,12)), 456, NULL);\r\nINSERT INTO mehmet.kart_bilgileri VALUES (5, 1004, 5710571057105710, '5710-5710-5710-5710', TRUNC(ADD_MONTHS(SYSDATE,12)), 567, NULL);\r\nCOMMIT;\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n<\/pre>\n
When query the related table, result will be like following.<\/p>\n
\"\"<\/p>\n
 <\/p>\n
Let’s go to perform Full redaction as follows in the Kart_no ( card no ) column.<\/p>\n
 <\/p>\n
BEGIN\r\nDBMS_REDACT.add_policy(\r\nobject_schema => 'mehmet',\r\nobject_name => 'mehmet.kart_bilgileri',\r\ncolumn_name => 'kart_no',\r\npolicy_name => 'redact_kart_info',\r\nfunction_type => DBMS_REDACT.full,\r\nexpression => '1=1'\r\n);\r\nEND;\r\n\/\r\n\r\n<\/pre>\n
When query the table with Mehmet user, Data seems properly not different or Redacted.<\/p>\n
\"\"<\/p>\n
 <\/p>\n
But When query the table with Salih user, Data seems\u00a0 different or Redacted like following.<\/p>\n
\"\"<\/p>\n
 <\/p>\n
 <\/p>\n
Then let’s go to perform Partial redaction as follows in the Kart_no ( card no ) column.<\/p>\n
 <\/p>\n
BEGIN\r\nDBMS_REDACT.alter_policy (\r\nobject_schema => 'mehmet',\r\nobject_name => 'mehmet.kart_bilgileri',\r\npolicy_name => 'redact_kart_info',\r\naction => DBMS_REDACT.modify_column,\r\ncolumn_name => 'kart_no',\r\nfunction_type => DBMS_REDACT.partial,\r\nfunction_parameters => '1,1,12'\r\n);\r\nEND;\r\n\/\r\n\r\n\r\n<\/pre>\n
When query the table with Mehmet user, Data seems properly not different or Redacted.<\/p>\n
\"\"<\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n\n
 <\/p>\n
But When query the table with Salih user, Data seems\u00a0 different or Redacted like following.<\/p>\n
\"\"<\/p>\n
 <\/p>\n
We can perform Partial redaction as follows in the Kart_string ( card no string type ) column with # character like following.<\/p>\n
 <\/p>\n
BEGIN\r\nDBMS_REDACT.alter_policy (\r\nobject_schema => 'mehmet',\r\nobject_name => 'mehmet.kart_bilgileri',\r\npolicy_name => 'redact_kart_info',\r\naction => DBMS_REDACT.add_column,\r\ncolumn_name => 'kart_string',\r\nfunction_type => DBMS_REDACT.partial,\r\nfunction_parameters => 'VVVVFVVVVFVVVVFVVVV,VVVV-VVVV-VVVV-VVVV,#,1,12'\r\n);\r\nEND;\r\n\/<\/pre>\n
 <\/p>\n
 <\/p>\n
When query the table with Mehmet user, Data seems properly not different or Redacted.<\/p>\n
\"\"<\/p>\n
 <\/p>\n
 <\/p>\n
But When query the table with Salih user, Data seems\u00a0 different or Redacted like following.<\/p>\n
\"\"<\/p>\n
 <\/p>\n
 <\/p>\n
or If you want to see Random redaction example, you can do it with following code to change guvenlik_no ( security code ) column<\/p>\n
BEGIN\r\nDBMS_REDACT.alter_policy (\r\nobject_schema => 'mehmet',\r\nobject_name => 'kart_bilgileri',\r\npolicy_name => 'redact_card_info',\r\naction => DBMS_REDACT.add_column,\r\ncolumn_name => 'guvenlik_no',\r\nfunction_type => DBMS_REDACT.RANDOM\r\n);\r\nEND;\r\n\/<\/pre>\n
 <\/p>\n\n
 <\/p>\n
When query the table with Mehmet user, Data seems properly not different or Redacted.<\/p>\n
\"\"<\/p>\n
 <\/p>\n
But When query the table with Salih user, Data seems\u00a0 different or Redacted like following.<\/p>\n
\"\"<\/p>\n
 <\/p>\n
 <\/p>\n
Don’t forget that in order to use this feature, your license must be Enterprise Edition and you must also have an Advanced Security Option license.<\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
Do you want to learn Oracle Database for Beginners, then read the following articles.<\/span><\/h5>\n
https:\/\/ittutorial.org\/oracle-database-19c-tutorials-for-beginners\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
Hi, I will continue to explain\u00a0Data Redaction in Oracle Database in this article.<\/p>\n","protected":false},"author":1,"featured_media":4074,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[1994,5046],"tags":[4588,449,4586,12798,4584,4589,12797,4591,480,12799,508,4596,579,12800,4593,4585,1031,4595,12801,4594,1120,4590,1165,1166,1199,1203,1205,4592,1266,1267,1345,4587],"class_list":["post-9458","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-oracle","category-security","tag-configure-data-redaction-on-oracle","tag-data-redaction","tag-data-redaction-dbms_redact-in-oracle-database","tag-data-redaction-in-oracle-12c-dba-blogs","tag-data-redaction-in-oracle-database","tag-data-redaction-in-oracle-database-12c-flaws-or-security-gaps","tag-data-redaction-meaning","tag-data-redaction-vs-data-masking","tag-dba-danismanlik","tag-dbms_redact","tag-deveci","tag-encryption-and-redaction","tag-exadata-danismanlik","tag-grant-exempt-redaction-policy-to-user","tag-grants-missing-on-redaction-policies-and-redaction-columns","tag-introduction-to-oracle-data-redaction","tag-mehmet-salih-deveci","tag-ora-28094","tag-ora-28074-the-redact_partial_input_format-field-of-the-redaction-parameters-is-not-valid","tag-ora-28081-insufficient-privileges-the-command-references-a-redacted-object","tag-oracle","tag-oracle-12c-data-redaction-example","tag-oracle-advanced-security-data-redaction","tag-oracle-advanced-security-option","tag-oracle-danismanlik","tag-oracle-data-masking","tag-oracle-data-redaction","tag-oracle-data-redaction-white-paper","tag-oracle-egitim","tag-oracle-egitimi","tag-oracle-support","tag-redacting-data-from-the-oracle-database"],"aioseo_notices":[],"jetpack_featured_media_url":"https:\/\/ittutorial.org\/wp-content\/uploads\/2014\/10\/data-redaction2.png","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/9458","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/comments?post=9458"}],"version-history":[{"count":4,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/9458\/revisions"}],"predecessor-version":[{"id":18249,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/9458\/revisions\/18249"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media\/4074"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=9458"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=9458"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=9458"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}