Required user permissions

Owned by Dima Urban (Unlicensed)
Last updated: Aug 22, 2024 by Michael Litner
3 min read

Overview

This documentation page is designed as a comprehensive guide detailing the minimum user permissions required to perform Data Anonymization for each supported database type. Given the critical nature of data masking in maintaining data privacy and regulatory compliance, it's essential to understand the specific permissions needed for effective and secure operations.

Audience


This document is intended for Database Administrators, Security Officers, and DevOps personnel responsible for setting up masking operations across various data source environments.

Goal

Implementing the correct user permissions for Data Data Anonymization is a critical risk factor that can significantly impact organizational security, compliance, and operational effectiveness. This documentation aims to mitigate these risks by providing a detailed guide to setting the minimum required permissions for each supported database type. Additionally, this document includes sample code snippets for granting any missed permissions. Adhering to these guidelines ensures that your data masking operations are secure, compliant, and operationally efficient.

 

Database types

 

Database type

Minimum user permissions

How to grant permissions, sample

Database type

Minimum user permissions

How to grant permissions, sample

1

Oracle

For masking user's own schema:

Roles:

  • Connect

  • Resource

Grant data dictionary view:

  • SELECT sys.v_$instance

  • SELECT sys.dba_constraints

  • SELECT sys.dba_tables

  • SELECT sys.dba_cons_columns

  • SELECT sys.dba_ind_columns

  • SELECT sys.dba_indexes

Permissions to mask tables from other schemas:

Grant table:

  • ALTER ANY TRIGGER

  • CREATE ANY INDEX

  • DROP ANY INDEX

  • ALTER ANY INDEX

  • SELECT,UPDATE,ALTER SCHEMA...TABLE...

grant connect, resource to masking_db_grants;

 

 

 

grant select on sys.v_$instance to masking_db_grants;
grant select on sys.dba_constraints to masking_db_grants;
grant select on sys.dba_tables to masking_db_grants;
grant select on sys.dba_cons_columns to masking_db_grants;
grant select on sys.dba_ind_columns to masking_db_grants;
grant select on sys.dba_indexes to masking_db_grants;

 

 

grant alter any trigger to masking_db_grants;
grant create any index to masking_db_grants;
grant drop any index to masking_db_grants;
grant alter any index to masking_db_grants;

grant select,update,alter on masking_db_grants2.tab01 to masking_db_grants;
grant select,update,alter on masking_db_grants2.tab02 to masking_db_grants;
grant select,update,alter on masking_db_grants2.tab03 to masking_db_grants;

2

MySQL

Instance level grants:

  • process

Database level grants:

  • create

  • drop

  • index

  • select

  • insert

Table level grants:

  • alter

  • reference

  • update

grant process on *.* to user_test_grants;

 

 

 

grant create,drop,index,insert,select on test_grants.* to user_test_grants;

 

 

 

grant alter,references,update on test_grants.TAB01 to user_test_grants;
grant alter,references,update on test_grants.TAB02 to user_test_grant;
grant alter,references,update on test_grants.TAB03 to user_test_grants;

3

PostgreSQL

Masking application often does "alter table" commands.
But Postgres does not have separate grants for "ALTER TABLE" command.
Therefore, to mask a database, a user who is the owner of the database or a user with superuser right (superuser) is required.
The second way is to give rights as owner to the user doing masking.

 

grant connect on database test_grants to user_test_grants;

grant owner_test_grants to user_test_grants;

4

DB2 LUW

Shema level grants:

Grant:

  • CREATEIN

  • DROPIN

  • ALTERIN

 

Table level grants:

Grant:

  • SELECT

  • UPDATE

  • ALTER

  • REFERENCES

  • INDEX

 

GRANT CREATEIN,DROPIN,ALTERIN ON SCHEMA TEST_GRANTS TO USER_MASKING;

 

 

 

 

 

GRANT ALTER,SELECT,UPDATE,REFERENCES,INDEX ON TEST_GRANTS.TAB01 TO USER_MASKING;
GRANT ALTER,SELECT,UPDATE,REFERENCES,INDEX ON TEST_GRANTS.TAB02 TO USER_MASKING;
GRANT ALTER,SELECT,UPDATE,REFERENCES,INDEX ON TEST_GRANTS.TAB03 TO USER_MASKING;

5

SQL Server (Microsoft)

Database level grants:

  • CREATE TABLE

 

 

Schema level grants:

  • ALTER

  • SELECT

  • INSERT

Table level grants:

  • UPDATE

  • REFERENCES

USE Acc_lhost_MSSQL2_lhost_SQLEXPRESS
GO

GRANT CREATE TABLE TO USER_MASKING_DB_GRANTS
GO

 

GRANT SELECT,ALTER,INSERT ON SCHEMA::MASKING_DB_GRANTS TO USER_MASKING_DB_GRANTS
GO

 

GRANT UPDATE,REFERENCES ON MASKING_DB_GRANTS.TAB01 TO USER_MASKING_DB_GRANTS
GO
GRANT UPDATE,REFERENCES ON MASKING_DB_GRANTS.TAB02 TO USER_MASKING_DB_GRANTS
GO
GRANT UPDATE,REFERENCES ON MASKING_DB_GRANTS.TAB03 TO USER_MASKING_DB_GRANTS
GO

6

MongoDB

4.2+

Database level:

Roles

  • readWrite

  • dbAdmin

use masking_db_grants

db.createUser({user:"user_masking_db_grants", pwd: "user_masking_db_grants", roles:[{"role" : "readWrite", "db" : "masking_db_grants"}]});
db.grantRolesToUser('user_masking_db_grants', [{ role: 'dbAdmin', db: 'masking_db_grants' }]);

Â