In-Place Masking (IPM) - Database User Permissions
This article outlines the specific permissions needed for an Oracle database and a SQL Server database user to perform In-Place Masking using the GenRocket Runtime.
SQL Server
The customer team is responsible for creating the database schemas, while the GenRocket Runtime utilizes these schemas to store temporary GenRocket tables, stored procedures, and stored functions necessary for performing table masking.
Database Schema Name Convention
Database Schema name convention to be followed: gen_rocket_ipm_1, gen_rocket_ipm_2, .. gen_rocket_ipm_N
- N is the total number of schemas created by the customer team.
- GenRocket will use N schemas to perform masking of N tables in parallel; one schema per table.
Permissions
The following permissions are required for the [DATABASE_USER] on a [SCHEMA_NAME] :
| Description | Command |
|---|---|
| Allow user to modify/delete Stored Procedures within the Schema | GRANT ALTER ON SCHEMA::[SCHEMA_NAME] TO [DATABASE_USER]; |
| Allow user to create Stored Procedures | GRANT CREATE PROCEDURE TO [DATABASE_USER]; |
| Allow user to create Stored Functions | GRANT CREATE FUNCTION TO [DATABASE_USER]; |
| Allow user to execute Stored Procedures & Functions | GRANT EXECUTE TO [DATABASE_USER]; |
| Allow user to populate Tables within the Schema | GRANT INSERT ON SCHEMA::[SCHEMA_NAME] to [DATABASE_USER]; |
| Allow user to query the Tables within the Schema | GRANT SELECT ON SCHEMA::[SCHEMA_NAME] to [DATABASE_USER]; |
| Allow user to delete Tables within the Schema | GRANT DELETE ON SCHEMA::[SCHEMA_NAME] to [DATABASE_USER]; |
| Allow user to create & drop Tables | GRANT CREATE TABLE TO [DATABASE_USER]; |
The following permissions are required for the [DATABASE_USER] on the table to be masked, [TARGET_SCHEMA].[TARGET_TABLE]:
| Description | Command |
|---|---|
| Allow user to query the Target Table | GRANT SELECT ON [TARGET_SCHEMA].[TARGET_TABLE] to [DATABASE_USER]; |
| Allow user to update the Target Table | GRANT UPDATE ON [TARGET_SCHEMA].[TARGET_TABLE] to [DATABASE_USER]; |
Example
Assuming we already have:
- Database user - gen_rocket_ipm_user
-
Target tables:
- [dbo].[ORGANIZATION]
- [dbo].[DEPARTMENT]
- [dbo].[USER]
-
Database Schemas:
- gen_rocket_ipm_1
- gen_rocket_ipm_2
- gen_rocket_ipm_3
We need to provide the following:
-
Permission to alter Stored Procedures in Schemas
- GRANT ALTER ON SCHEMA::gen_rocket_ipm_1 TO [gen_rocket_ipm_user];
- GRANT ALTER ON SCHEMA::gen_rocket_ipm_2 TO [gen_rocket_ipm_user];
- GRANT ALTER ON SCHEMA::gen_rocket_ipm_3 TO [gen_rocket_ipm_user];
-
Permission to create & drop Stored Procedures in Schemas
- GRANT CREATE PROCEDURE TO [gen_rocket_ipm_user];
-
Permission to execute Stored Procedures in Schemas
- GRANT EXECUTE TO [gen_rocket_ipm_user] ;
-
Permission to create & drop Stored Functions in Schemas
- GRANT CREATE FUNCTION TO [gen_rocket_ipm_user] ;
-
Permission to insert the records in the Tables of Schemas
- GRANT INSERT ON SCHEMA::gen_rocket_ipm_1 to [gen_rocket_ipm_user];
- GRANT INSERT ON SCHEMA::gen_rocket_ipm_2 to [gen_rocket_ipm_user];
- GRANT INSERT ON SCHEMA::gen_rocket_ipm_3 to [gen_rocket_ipm_user];
-
Permission to query the records in the Tables of Schemas
-
GRANT SELECT ON SCHEMA::gen_rocket_ipm_1 to [gen_rocket_ipm_user];
GRANT SELECT ON SCHEMA::gen_rocket_ipm_2 to [gen_rocket_ipm_user]; - GRANT SELECT ON SCHEMA::gen_rocket_ipm_3 to [gen_rocket_ipm_user];
-
GRANT SELECT ON SCHEMA::gen_rocket_ipm_1 to [gen_rocket_ipm_user];
-
Permission to delete the records in the Tables of Schemas
- GRANT DELETE ON SCHEMA::gen_rocket_ipm_1 to [gen_rocket_ipm_user];
- GRANT DELETE ON SCHEMA::gen_rocket_ipm_2 to [gen_rocket_ipm_user];
- GRANT DELETE ON SCHEMA::gen_rocket_ipm_3 to [gen_rocket_ipm_user];
-
Permission to create & drop Tables in Schemas
- GRANT CREATE TABLE TO [gen_rocket_ipm_user];
-
Permission to query the Target Tables
- GRANT SELECT ON [dbo].[ORGANIZATION] to [gen_rocket_ipm_user];
- GRANT SELECT ON [dbo].[DEPARTMENT] to [gen_rocket_ipm_user];
- GRANT SELECT ON [dbo].[USER] to [gen_rocket_ipm_user];
-
Permission to update the Target Tables
- GRANT UPDATE ON [dbo].[ORGANIZATION] to [gen_rocket_ipm_user];
- GRANT UPDATE ON [dbo].[DEPARTMENT] to [gen_rocket_ipm_user];
- GRANT UPDATE ON [dbo].[USER] to [gen_rocket_ipm_user];
Oracle
The customer team is responsible for creating the database schemas, while the GenRocket Runtime utilizes these schemas to store temporary GenRocket tables, stored procedures, and stored functions necessary for performing table masking.
Database Schema Name Convention
Database Schema name convention to be followed: gen_rocket_ipm_1, gen_rocket_ipm_2, .. gen_rocket_ipm_N
- N is the total number of schemas created by the customer team.
- GenRocket will use N schemas to perform masking of N tables in parallel; one schema per table.
Note: Corresponding database connection properties files should:
- be named as gen_rocket_ipm_N.properties
- be saved in the same folder as target database connection properties file
- Example: gen_rocket_ipm_1.properties for gen_rocket_ipm_1 schema / user gen_rocket_ipm_2.properties for gen_rocket_ipm_2 schema / user
Permissions
The following permissions are required for the gen_rocket_ipm_N user.
| Description | Command |
|---|---|
| Allow user to connect to the Schema | GRANT CONNECT, RESOURCE TO gen_rocket_ipm_N; |
| Allow user to create/modify/execute/drop Stored Procedures & Functions within the Schema | GRANT CREATE PROCEDURE TO gen_rocket_ipm_N; |
| Allow user to create/alter/drop Tables within the Schema | GRANT CREATE TABLE TO gen_rocket_ipm_N; |
| Allow user to add/drop Index | GRANT CREATE ANY INDEX TO gen_rocket_ipm_N |
The following permissions are required for the table to be masked, [TARGET_SCHEMA].[TARGET_TABLE] :
| Description | Command |
|---|---|
| Allow user to select/update/query the Target Table | GRANT SELECT, UPDATE ON <TARGET_SCHEMA>.<TARGET_TABLE> TO gen_rocket_ipm_N; |
When executing IPM without horizontal scaling, the following permissions are required for the Target schema / user,
| Description | Command |
|---|---|
| Grant DBMS_PARALLEL_EXECUTE permission | GRANT EXECUTE ON DBMS_PARALLEL_EXECUTE TO <TARGET_SCHEMA>; |
| Grant CREATE JOB permission | GRANT CREATE JOB TO <TARGET_SCHEMA>; |
When executing IPM with horizontal scaling, the following permissions are required for all the GenRocket schema / user,
| Description | Command |
|---|---|
| When executing IPM with horizontal scaling, the following permissions are required for all the GenRocket schema / user, | GRANT EXECUTE ON DBMS_PARALLEL_EXECUTE TO gen_rocket_ipm_N; |
| Grant CREATE JOB permission | GRANT CREATE JOB TO gen_rocket_ipm_N; |
Example
Assuming we already have:
-
Database user:
- gen_rocket_ipm_1
- gen_rocket_ipm_2
- gen_rocket_ipm_3
-
Target tables:
- ORDERS.ORGANIZATION
- ORDERS.DEPARTMENT
- ORDERS.USERS
We need to provide the following:
-
Permission to connect to the Schema
- GRANT CONNECT, RESOURCE TO gen_rocket_ipm_1;
- GRANT CONNECT, RESOURCE TO gen_rocket_ipm_2;
- GRANT CONNECT, RESOURCE TO gen_rocket_ipm_3;
-
Permission to create & drop Stored Procedures & Functions in Schemas
- GRANT CREATE PROCEDURE TO gen_rocket_ipm_1;
- GRANT CREATE PROCEDURE TO gen_rocket_ipm_2;
- GRANT CREATE PROCEDURE TO gen_rocket_ipm_3;
-
Permission to create/alter/drop Tables within Schemas
- GRANT CREATE TABLE TO gen_rocket_ipm_1;
- GRANT CREATE TABLE TO gen_rocket_ipm_2;
- GRANT CREATE TABLE TO gen_rocket_ipm_3;
-
Permission to create/drop indexes
- GRANT CREATE ANY INDEX TO gen_rocket_ipm_1;
- GRANT CREATE ANY INDEX TO gen_rocket_ipm_2;
- GRANT CREATE ANY INDEX TO gen_rocket_ipm_3;
-
Permission to select/update/query the records in Target Tables
- GRANT SELECT, UPDATE ON ORDERS.ORGANIZATION TO gen_rocket_ipm_1;
- GRANT SELECT, UPDATE ON ORDERS.ORGANIZATION TO gen_rocket_ipm_2;
- GRANT SELECT, UPDATE ON ORDERS.ORGANIZATION TO gen_rocket_ipm_3;
- GRANT SELECT, UPDATE ON ORDERS.DEPARTMENT TO gen_rocket_ipm_1;
- GRANT SELECT, UPDATE ON ORDERS.DEPARTMENT TO gen_rocket_ipm_2;
- GRANT SELECT, UPDATE ON ORDERS.DEPARTMENT TO gen_rocket_ipm_3;
- GRANT SELECT, UPDATE ON ORDERS.USER TO gen_rocket_ipm_1;
- GRANT SELECT, UPDATE ON ORDERS.USER TO gen_rocket_ipm_2;
- GRANT SELECT, UPDATE ON ORDERS.USER TO gen_rocket_ipm_3;
-
Permission to do parallel execute:
- GRANT EXECUTE ON DBMS_PARALLEL_EXECUTE TO gen_rocket_ipm_1;
- GRANT EXECUTE ON DBMS_PARALLEL_EXECUTE TO gen_rocket_ipm_2;
- GRANT EXECUTE ON DBMS_PARALLEL_EXECUTE TO gen_rocket_ipm_3;
-
Permission to create job:
- GRANT CREATE JOB TO to gen_rocket_ipm_1;
- GRANT CREATE JOB TO to gen_rocket_ipm_2;
- GRANT CREATE JOB TO to gen_rocket_ipm_3;
Article Feedback: Was this helpful?
Give feedback