0 I created a data **Redaction policy** for a field EMAIL in a Person Table in an ORACLE Data Base 12c. In the expression statment of the Redaction Policy is the exception for the **Data base owner user**: This user can access the EMAIL field without redaction. It works perfectly :) This field with redaction (EMAIL) is involved in a insert-select statmement in a trigger, so when the trigger is executed I get the error ORA-28081: Insufficient privileges - the command references a redacted object To avoid this error, I moved the insert-select statement to a Stored Procedure that, by default, has Definer Rights, and the Definer is the **Data base owner user**. But I still have the ORA-28081 error. Why this do not solve the error if the procedure is executed with the rights of the Data base owner user and the redaction has exception for this user?

Here is the code to reproduce the issue: CREATE TABLE [dbo].[EmployeeDataMasking]( [RowId] [int] IDENTITY(1,1) NOT NULL, [EmployeeId] [int] NULL, [LastName] [varchar](50) MASKED WITH (FUNCTION = 'partial(2, "XXXX", 2)') NOT NULL, [FirstName] [varchar](50) MASKED WITH (FUNCTION = 'partial(2, "XXXX", 2)') NOT NULL, CONSTRAINT [PK_EmployeeDataMasking] PRIMARY KEY CLUSTERED ( [RowId] ASC )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY], ) ON [PRIMARY] GO Insert Into dbo.EmployeeDataMasking (EmployeeId, LastName, FirstName) VALUES( 1,'Smithsonian','Daniel'),( 2,'Templeton','Ronald') -- Partial data masking works correctly here Select EmployeeId, LastName, FirstName From dbo.EmployeeDataMasking -- Partial data masking does not work correctly here. Full masking is used. Select EmployeeId, ISNULL(LastName,'') as LastName, ISNULL(FirstName,'') as FirstName From dbo.EmployeeDataMasking It appears, when using ISNULL, the partial masking is being converted to a full (default) mask. Is this the expected behavior? Update: I discovered that COALESCE does not exhibit this problem. These queries return the expected results: Select EmployeeId, COALESCE(LastName,'') as LastName, COALESCE(FirstName,'') as FirstName From dbo.EmployeeDataMasking Using Azure SQL Database

I am working for a medical private practice, and we are planning to install an appointment booking system via our webpage. The appointment system is a PHP application storing its records in an SQL database that can be on the webserver itself or on a remote server. Due to the use case, the database will contain very sensitive personal information* that must be kept secret from the public. Hardening access to the database is one point, but I guess that an important entry hole to the data would be the PHP application itself. Is there a recommended way to isolate the sensible data from public access? I could imagine a system such as the following: *public* -> access via PHP app on publicly accessible server -> *SQL database with masked sensitive entries* *private SQL database with full entries* *employees* In that way, even if the PHP application would expose data to public, the sensitive data would be protected. Also, gaining access to the publicly available SQL database itself would be less critical. Mapping between the two databases would be through a unique hash-code for each of the entries. Effectively, this is just a pseudonymization and not an anonymization. But still, it is an additional layer of security. I just don't know whether there is a ready-made way for such an implementation. Basically, any change in the *SQL database with masked sensitive entries* should trigger a sync with a subsequent masking (since customer entries made from *public* need to be masked). Is there a better implementation? I would be very pleased for any suggestion. --- \* Patients would enter sensitive data in the public database, and we need this data to do our job (we need names, contact, and some medical information). But as soon as this data is entered, it should be transferred to the private database and be made unavailable in the public database. But still, I don't want to fuzz with the PHP application which is pre-made. Masking the data in the public database (e.g., with *s) would make the PHP application just work as usual

I'd like to able to mask data for all users including SA's. I'm not trying to guard against malicious intent, but rather, stop people unintentionally seeing data they shouldn't. Dynamic Data Masking seemed like a good solution but, from what I've read, it doesn't apply to admin users. Is there a good alternative? This is for a database that has not yet been created. I'll be importing data from other data sources. Our default version\edition is SQL 2017 Enterprise. Azure PaaS is also a possibility.

I am trying to create an index on a view that references a table with a masked column (SQL Server 2016). The masked column is not the only one in that table, and it's not used in the view. create unique clustered index [IX_Name] on dbo.vw_ViewName(SomeUniqueId) I get this error: > The index on view 'dbo.vw_ViewName' cannot be created > because the view is referencing table 'dbo.TableName' with masked > columns. In another environment, where masking is not enabled, the index is created successfully. I looked through about four pages of Google results and haven't found any sensible description of the error. I would appreciate any knowledge transfer on the error and why it is not possible to create such index. Here is some SQL to reproduce the issue: drop view if exists dbo.vw_Aggregate drop table if exists dbo.MainTable, dbo.SecondaryTable go create table dbo.MainTable ( MainTableId uniqueidentifier primary key, SomeExternalId uniqueidentifier, SecondaryTableId uniqueidentifier ) go create table dbo.SecondaryTable ( SecondaryTableId uniqueidentifier primary key, CreatedOn datetime, Amount decimal(19, 8), -- the below column produces error, -- if commented out - there is no error [Description] nvarchar(max) masked with (function = 'default()'), Dummy int ) go create view dbo.vw_Aggregate with schemabinding as select AggregateId = m.MainTableId, m.SomeExternalId, s.CreatedOn, s.Amount from dbo.MainTable m inner join dbo.SecondaryTable s on s.SecondaryTableId = m.SecondaryTableId go create unique clustered index [IX_dbo.vw_Aggregate(AggregateId)] on dbo.vw_Aggregate(AggregateId) go

In Db2 v11.5.7 on Linux I have simple table: create table admin.patient_data ( patientid int not null primary key, patient_name varchar(10), illness varchar(15), doctor_name varchar(10) ); insert into admin.patient_data values (1, 'Alice', 'illness A', 'DOCTOR1'); insert into admin.patient_data values (2, 'Bob', 'illness B', 'DOCTOR2'); select * from admin.patient_data; Result: PATIENTID PATIENT_NAME ILLNESS DOCTOR_NAME ----------- ------------ --------------- ---------- 1 Alice illness A DOCTOR1 2 Bob illness B DOCTOR2 Column ILLNESS is sensitive data. I would like to allow this column to be displayed only if column DOCTOR_NAME matches Db2 logged-in user. create mask admin.patient_data on admin.patient_data for column illness return case when doctor_name = USER then illness else 'Masked data' end enable; alter table admin.patient_data activate column access control; Now user DOCTOR1 connects to database and checks the data with: select * from admin.patient_data it is returned: PATIENTID PATIENT_NAME ILLNESS DOCTOR_NAME ----------- ------------ --------------- ----------- 1 Alice illness A DOCTOR1 2 Bob Masked data DOCTOR2 In row PATIENTID=2 column ILLNESS is expected to be masked. But doctor is educated, so he/she knows all the illnesses and now target "illness B" db2 "select * from admin.patient_data where illness = 'illness B'" and it gets: PATIENTID PATIENT_NAME ILLNESS DOCTOR_NAME ----------- ------------ --------------- ----------- 2 Bob Masked data DOCTOR2 Field ILLNESS is still marked as expected, but now because of where conditional DOCTOR1 knows that patient Bob has "illness B". I expected that end-users could filter by values they are returned with where condition. That is: "illness A" and "Marked data". Is there some simple solution to prevent this? I expect to have no record returned for last select statement.

I need to send a database backup to a vendor for an upgrade, and somehow need to mask several columns containing PII. Was looking into static data masking, but this seems to change the data permanently. Dynamic data masking seems better as it hides it as it is accessed by users without unmask permissions. But once this database is backed up and sent off for the vendor to restore, could the vendor not grant themselves higher level permissions to see the data anyway? They will likely need db_owner anyway to make all the structural changes needed for the upgrade. Is there a better (hopefully low-effort) way to mask certain columns without permanently altering data for a backup?

We used to have multiple environments for applications such as Production, Staging, UAT, Dev, and Sandbox. It is a regular task for the DBA to refresh the lower (staging, test, UAT etc.) environments with a backup of the production database backup. Sometimes we need to share the database backup with the external vendors as well. How to Apply Static Data Masking With Replication of Databases , i.e for every change the replication database to to apply mask and save masked records in staging, test, UAT etc. Is there any tools or process or methods available ?

I was trying to understand the difference between encryption and masking Below statement says that real data replaced and gone! >Masking protects your data by transforming it into a readable format that’s useless to anyone who steals it. The actual data is replaced by fictional information. There is no encryption solution to revert the data to its original state. The real data was replaced and is gone forever. What does it mean? If real data are gone, then how do I use it in my system? **For example:** I might masked EMAILID and my system wants to do some logic based on the email ID, then how do I compare the email ID in code? Basically, if I am not using the sensitive data, why do I store it in my database in the first place?

You can reproduce the issue here: CREATE TABLE [dbo].[EmployeeDataMasking]( [RowId] [int] IDENTITY(1,1) NOT NULL, [EmployeeId] [int] NULL, [LastName] [varchar](50) MASKED WITH (FUNCTION = 'partial(2, "XXXX", 2)') NOT NULL, [FirstName] [varchar](50) MASKED WITH (FUNCTION = 'partial(2, "XXXX", 2)') NOT NULL, CONSTRAINT [PK_EmployeeDataMasking] PRIMARY KEY CLUSTERED ( [RowId] ASC )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY], ) ON [PRIMARY] GO Insert Into dbo.EmployeeDataMasking (EmployeeId, LastName, FirstName) VALUES( 1,'Smithsonian','Daniel'),( 2,'Templeton','Ronald') Select EmployeeId, LastName, FirstName, LastName + ', ' + FirstName From dbo.EmployeeDataMasking Notice the LastName and FirstName fields are partially masked (as expected). However, the combined name field contains the default mask. I don't know if this is considered a bug. However, I would think the combined field would retain the mask of the two fields it comprises. At least that's what I would prefer, since I don't know how to provide a mask for the combined field.

Recently we have implemented dynamic data masking in SQL Server 2019 database in order to hide sensitive information from developers. However, for testing purposes I'd like them to see close-to-real values, so my mask looks like this:

CREATE TABLE fin.Salaries
(
    TargetMonth    DATE            NOT NULL,
    Login          VARCHAR(100)    NOT NULL,
    Department     VARCHAR(100)    NOT NULL,
    AmountUSD      MONEY           MASKED WITH (FUNCTION = 'random(500, 5000)') NOT NULL,
    CONSTRAINT PK_Salaries PRIMARY KEY CLUSTERED (TargetMonth, Login, Department)
)

It works fine for simple queries, however aggregate functions seem to always return zero values. I haven't found anything on this in the documentation, so the question is whether this is an intended behavior and is there any way to get non-zero values from aggregated data masked column?

SELECT   TargetMonth,
         AVG(AmountUSD) -- Returns all zeroes
FROM     fin.Salaries
GROUP BY TargetMonth

We want to provide developers in our organization masked data from production to help troubleshoot production issues. What would be the best way to approach it? I've read this article https://learn.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-ver15 But it seems to take a different approach from what I've envisioned. My idea was to have a separate database server that is replicated from the production database, and somehow mask the data during replication so that the real data will never reach the replicated database server. That way we don't have to have special security considerations about how to store and treat the replicated database server. Is it a reasonable approach?

I am currently working on a project to mask and de-identify data and wanted to pass my findings through a sanity check (you guys). Is it better to create a new view which houses masked data and limits source table access to admins, or is it better to create a trigger / stored procedure to apply the masking realtime after query results have been fetched? With the view approach I would have to make some code changes, as the code base is accessing tables through a traditional ORM. But this would mask data regardless of access (app, cli, after a snapshot). Is it possible through triggers / stored procedures to intercept a request against a sensitive table, and apply the mask before returning the results? This would avoid having to make any code changes. Or would it be better to implement a hybrid solution, to add a trigger on a sensitive table, save the data as given, but then apply a stored procedure to store the same (but masked and de-identified) data in the view. Thanks in advance.

Can anyone help me explain on what basis 'Data Migration Assessment' features recommendation suggests for Dynamic Data Masking and displays certain objects/tables columns for masking. On what basis DMA identifies those columns for vulnerabilities. Is there any set algorithm for identification or is it simply based on the name of the column? Please comment in case of any more details or clarification required for the question.

I am using Oracle 11g R2 on Linux, and I have multiple tables in my database that has got columns that needs to be masked. I have tried the OEM to mask the data as mentioned in the below hyperlink, it worked fine. http://www.oracle.com/webfolder/technetwork/tutorials/obe/em/emgc10205/data_masking/datamask.htm But I couldn't find any blog that gives manual ways to mask data in oracle, like executing packages, functions and so on via SQLPLUS prompt. Could someone please suggest me how else can this be achieved

I installed SSMS version 18 preview 6 and am using it with SQL Server 2017. I want to test the Static Data Masking feature, but the option does not appear.

I would like to redact data with regex in oracle database. I know the procedure but I get unpredictable results. I use the following script to apply redaction to a certain column with card numbers.

`
BEGIN
DBMS_REDACT.ADD_POLICY ( 
   object_schema          => 'my_schema',
   object_name            => 'my_table',
   column_name            => 'card_number',
   policy_name            => 'redact_my_table', 
   function_type          => DBMS_REDACT.REGEXP,
   function_parameters    => null,
   regexp_pattern         => '(............)(.+)(........)',--DBMS_REDACT.RE_PATTERN_CC_L6_T4,
   regexp_replace_string  => '\1****\3', --dbms_redact.re_redact_ccn, --DBMS_REDACT.RE_REDACT_CC_MIDDLE_DIGITS,
   regexp_position        => DBMS_REDACT.RE_BEGINNING,
   regexp_occurrence      => DBMS_REDACT.RE_FIRST,
   regexp_match_parameter => 'i',
   policy_description     => 'Regular expressions to redact credit card numbers',
   expression             => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''my_schema'''
   );
END;
/

` Expected result would be first 6 and last 4 unmasked like 123456****1234, but I get this instead: From the documentation I would suppose the below script would work by default, but it performs a full redaction instead of just masking some chars. I get a whitespace per row.

`
BEGIN
DBMS_REDACT.ADD_POLICY ( 
   object_schema          => 'my_schema',
   object_name            => 'my_table',
   column_name            => 'card_number',
   policy_name            => 'redact_my_table', 
   function_type          => DBMS_REDACT.REGEXP,
   function_parameters    => null,
   regexp_pattern         => DBMS_REDACT.RE_PATTERN_CC_L6_T4,
   regexp_replace_string  => DBMS_REDACT.RE_REDACT_CC_MIDDLE_DIGITS,
   regexp_position        => DBMS_REDACT.RE_BEGINNING,
   regexp_occurrence      => DBMS_REDACT.RE_FIRST,
   regexp_match_parameter => 'i',
   policy_description     => 'Regular expressions to redact credit card numbers',
   expression             => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''my_schema'''
   );
END;
/

` Database used is Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 This are the constants in DBMS_REDACT package:

`
RE_PATTERN_CC_L6_T4        CONSTANT   VARCHAR2(33) := '(\d\d\d\d\d\d)(\d\d\d*)(\d\d\d\d)';
RE_REDACT_CC_MIDDLE_DIGITS CONSTANT   VARCHAR2(10) := '\1XXXXXX\3';

` If i put the \d in regex patter i always get full redaction. Using dot i managed to get the scrambled data from the image. If anyone could try here is also the drop statement:

` 
exec dbms_redact.drop_policy('my_schema','my_table','redact_my_table');

` Any ideas what am I doing wrong?

Does anyone have any SQL code to automatically generate words (all letter, no number). Is there any inline SQL to perform this on a column? We are trying to obfuscate first/last name and other word columns in table. [This answer](https://stackoverflow.com/questions/2152204/tsql-pseudo-random-text-generator) currently does not work on column, because it generates the same result within a column. select top (@stringlength) char(abs(checksum(newid())) % 26 + ascii('A')) from sys.all_objects for xml path('') I tried this, but functions are slow, inline queries can be faster, feel free to improve or rewrite below CREATE VIEW [dbo].[RandomValueVw] AS SELECT RAND() as random_value CREATE function dbo.RandomWordGenerate(@length int) returns varchar(50) as begin DECLARE @text nvarchar(255), @i int SET @i = 0 SET @text = '' WHILE (@i < @length) BEGIN SET @text = @text + CHAR((select * from [dbo].[RandomValueVw]) * 26 + 65) SET @i = @i + 1 END return @text end

Does anyone have any SQL code to automatically generate randomized birth dates, where the date of birth is less than today? Please add Date of Birth Range parameters, eg: from 18 to 70 years old. Is there any inline SQL or function to do this? We are trying to obfuscate our column [Date of Birth]. Thanks,

Does anyone have code; looking for a random number function generator. I would just supply with a variable length @NumberLength = 50, etc. It can create numbers up to 50 digits, and store in varchar. (Bigint does not store this high) I am using Floor Rand, Floor, NewId(), still not receiving answer, that exceeds 12 digits. If anyone has a solution, that would be great. I need to randomize a column in table. Thank you, Note: Needs to be in a function, functions cannot accept NewID or rand() for some reason, so I sometimes utilize views. If random number is used again, no issue, we can have little bit repetition.