Oracle是否有类似SQL Server动态数据掩码的功能？仅所有者/SYS可见真实数据

Absolutely! Oracle offers Oracle Data Redaction—a built-in feature that mirrors the functionality you're using in SQL Server, allowing privileged users (like the table owner or SYS) to view unmasked data while masking sensitive columns for all other users.

Here's a step-by-step guide to implement this:

1. Ensure Required Permissions #

First, you need the EXECUTE privilege on the DBMS_REDACT package to create redaction policies. If you're working as SYS, grant this to the schema owner (or the user creating the policy):

GRANT EXECUTE ON DBMS_REDACT TO your_schema_owner;

2. Create a Test Table (Example) #

Let's use a sample customer table to demonstrate:

CREATE TABLE customers (
  customer_id NUMBER PRIMARY KEY,
  full_name VARCHAR2(100),
  email VARCHAR2(100),
  phone VARCHAR2(20)
);

-- Insert test data
INSERT INTO customers VALUES (1, 'John Doe', 'john.doe@example.com', '555-1234');
INSERT INTO customers VALUES (2, 'Jane Smith', 'jane.smith@example.com', '555-5678');
COMMIT;

3. Create a Redaction Policy #

We'll create a policy that masks the email and phone columns for all users except the table owner and SYS. The DEFAULT redaction function will mask data based on its type:

• Strings are replaced with XXXX
• Numbers are replaced with 0
• Dates are replaced with 01-JAN-00

Run this PL/SQL block to add the policy:

BEGIN
  DBMS_REDACT.ADD_POLICY(
    object_schema    => 'your_schema_owner', -- Replace with your schema name
    object_name      => 'customers',
    column_name      => 'email,phone', -- Comma-separated columns to mask
    policy_name      => 'redact_customer_sensitive_data',
    function_type    => DBMS_REDACT.DEFAULT,
    expression       => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') NOT IN (''SYS'', USER)'
    -- The expression skips redaction for SYS and the current table owner (USER)
  );
END;
/

4. Verify the Policy Works #

• As the table owner or SYS: Query the table, and you'll see the real data:

  SELECT * FROM customers;
  -- Output shows actual email and phone values
  

• As a regular user: Query the table, and the sensitive columns will be masked:

  SELECT * FROM customers;
  -- Output will show email as 'XXXX' and phone as 'XXXX'
  

Bonus: Customize Masking Format #

If you want more control over how data is masked (e.g., show the first 2 characters of an email plus the domain), use the PARTIAL redaction function instead. For example, to mask emails to show jo**@example.com:

BEGIN
  DBMS_REDACT.ADD_POLICY(
    object_schema    => 'your_schema_owner',
    object_name      => 'customers',
    column_name      => 'email',
    policy_name      => 'redact_customer_email_partial',
    function_type    => DBMS_REDACT.PARTIAL,
    function_parameters => 'VVFVV,*,2,1', -- Format: keep first 2 chars, mask middle with *, keep @ and domain
    expression       => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') NOT IN (''SYS'', USER)'
  );
END;
/

Key Notes #

• To remove a redaction policy, use DBMS_REDACT.DROP_POLICY.
• Users with the EXEMPT REDACTION POLICY system privilege will bypass all redaction policies, regardless of the expression.
• Data redaction is applied at query time—no changes are made to the actual stored data.

内容的提问来源于stack exchange，提问作者saicharan