Configuration of Row-level permissions

Grant RLS in Snowflake

Precondition on Snowflake

Row Access Policy is needed to be generated on target database.

Data columns are required to be defined on datatype level in the Row Access Policy definition.

Role being assigned for Agile Data Engine needs to have the apply permission for the created row access policies.

https://docs.snowflake.com/en/user-guide/security-row-intro.html

Permission definition in CONFIG_ENTITY_PERMISSIONS-package

rowLevelPolicies-section

CODE

{
    "rowLevelPolicyId": "", <<--- when generating new policies, use for example command
    
    uuidgen | tr 'A-Z' 'a-z' 
    
    in linux to generate a new unique UUID
    "label": "Allowed Sales Unit", <<-- Short name for the row level policy
    "preDefinedPolicyFunctionName": "security.data_access_only_to_unit", <<-- the schema specific Row Access Policy existing in Snowflake
    "requiresDefinedGrantees": false, <<-- Snowflake does not required defined grantees
    "deleted": false,
    "description": "Information only to allowed sales units" <<-- short description of the row level policy
},

After defining the rowLevelPolicies, save the Config-package and reopen it for adding the needed attributes

rowLevelPolicyAttributes-section

CODE

{
    "rowLevelPolicyAttributeId": "",<<--- when generating new policy attributes, use for example command
    
    uuidgen | tr 'A-Z' 'a-z' 
    
    in linux to generate a new unique UUID
    "rowLevelPolicyId": "<row_policy_id>", <<-- use the rowLevelPolicyId from the rowLevelPolicies-section
    "attributeName": "sales_unit", <<-- the name of the expected attribute both in row access policy and entities 
    "position": 1, <<-- the order of the attributes in case there are multiple attributes for the row level policy
    "datatype": "VARCHAR" <<-- the mandatory logical datatype, needs to match between the entity attribute and the row level policy definition
},

After defining the rowLevelPolicyAttributes, save the Config-package, commit and deploy to runtimes.

DDL generated

CODE

ALTER TABLE data ADD ROW ACCESS POLICY security.data_access_only_to_unit ON (sales_unit);

Change logic for Row Access Policy controlled table

All access rights will be removed
Row Access Policies will be removed
Entity changes will be made
Row Access Policies will be applied
All access rights will be applied

Grant RLS in Amazon Redshift

Precondition on Amazon Redshift

Row Level Security policy is needed to be generated on target database.

Data columns are required to be defined on detailed level (datatype+length or datatype+precision&scale) in the Row Level Security policy definition in Agile Data Engine.

Grantees are to be defined in grantees-section of CONFIG_ENTITY_PERMISSION

Agile Data Engine’s database user needs to be assigned with a named role (given in to Agile Data Engine configuration), which can be used for ignoring the Row Level Security policies on need basis when modifying the Table structure having policies attached.

https://docs.aws.amazon.com/redshift/latest/dg/t_rls.html

Permission definition in CONFIG_ENTITY_PERMISSIONS-package

grantees-section

CODE

{
    "granteeId": 4, <<-- provide an identifier, which has not been used yet
    "granteeName": "data_viewer", <<-- the database role in target database
    "granteeType": "ROLE" <<-- this can be by default user, role or group
},

rowLevelPolicies-section

CODE

{
    "rowLevelPolicyId": "", <<--- when generating new policies, use for example command
    
    uuidgen | tr 'A-Z' 'a-z' 
    
    in linux to generate a new unique UUID
    "label": "Allowed Sales Unit", <<-- Short name for the row level policy
    "preDefinedPolicyFunctionName": "data_access_allowed_on_unit", <<-- the Row Level Security Policy existing in Redshift
    "requiresDefinedGrantees": true, <<-- in Redshift the grantees are required to be defined for the policy
    "deleted": false,
    "description": "Information only to allowed sales units" <<-- short description of the row level policy
},

After defining the rowLevelPolicies, save the config-package and reopen it for adding the needed attributes

rowLevelPolicyAttributes-section

CODE

{
    "rowLevelPolicyAttributeId": "",<<--- when generating new policy attributes, use for example command
    
    uuidgen | tr 'A-Z' 'a-z' 
    
    in linux to generate a new unique UUID
    "rowLevelPolicyId": "<row_policy_id>", <<-- use the rowLevelPolicyId from the rowLevelPolicies-section
    "attributeName": "sales_unit", <<-- the name of the expected attribute both in row access policy and entities 
    "position": 1, <<-- the order of the attributes in case there are multiple attributes for the row level policy
    "datatype": "VARCHAR", <<-- the mandatory logical datatype, needs to match between the entity attribute and the row level policy definition
    "dataLength": 40,  <<-- define the datatype sizing precisely (length or precision&scale)
    "dataPrecision": null,
    "dataScale": null,
},

After defining the rowLevelPolicyAttributes, save the Config-package, commit and deploy to runtimes.

Grantee assignment for Row Level Policy

As Redshift requires the grantees to be defined for the policy for each policy attachment, the boolean (true) set for the row level permission, requiresDefinedGrantees, alters the user interface to require the grantees to be chosen each time such row level policy is being applied to an entity.

DDL generated

CODE

ATTACH RLS POLICY data_access_allowed_on_unit ON data TO ROLE data_viewer

Change logic for Row Level Security policy controlled table

ADE’s database user assigned role will be temporarily GRANTED RLS ignore rule
Entity changes will be made
RLS ignore rule of ADE’s database user assigned role will be REVOKED

Grant RLS in Azure SQL Database and Azure Synapse SQL

Precondition on Azure SQL Database and Azure Synapse SQL

Row level policy function is required to be generated on target database.

Data columns are required to be defined on detailed level (datatype+length or datatype+precision&scale) in the Row Level Security policy definition in Agile Data Engine.

Role/user being assigned for Agile Data Engine needs to have the permission altering any security policy.

https://learn.microsoft.com/en-us/sql/relational-databases/security/row-level-security?view=sql-server-ver16

Usage of Row level policies in Azure SQL Database and Azure Synapse SQL will prevent usage of SWAP pattern as data insertion method. The reason is that when security policy is attached to a table, the table can not be renamed.

Permission definition in CONFIG_ENTITY_PERMISSIONS-package

rowLevelPolicies-section

CODE

{
    "rowLevelPolicyId": "", <<--- when generating new policies, use for example command
    
    uuidgen | tr 'A-Z' 'a-z' 
    
    in linux to generate a new unique UUID
    "label": "Allowed Sales Unit", <<-- Short name for the row level policy
    "preDefinedPolicyFunctionName": "security.dataAccessAllowedOnUnit", <<-- Schema specific Row Level Policy function existing in Azure database
    "requiresDefinedGrantees": false, <<-- Azure database does not require defined grantees
    "nameSuffix": "allowed_unit", <-- the suffix to be used when creating the security policy
    "deleted": false,
    "description": "Information only to allowed sales units" <<-- short description of the row level policy
},

After defining the rowLevelPolicies, save the config-package and reopen it for adding the needed attributes

rowLevelPolicyAttributes-section

CODE

{
    "rowLevelPolicyAttributeId": "",<<--- when generating new policy attributes, use for example command
    
    uuidgen | tr 'A-Z' 'a-z' 
    
    in linux to generate a new unique UUID
    "rowLevelPolicyId": "<row_policy_id>", <<-- use the rowLevelPolicyId from the rowLevelPolicies-section
    "attributeName": "sales_unit", <<-- the name of the expected attribute both in row access policy and entities 
    "position": 1, <<-- the order of the attributes in case there are multiple attributes for the row level policy
    "datatype": "VARCHAR", <<-- the mandatory logical datatype, needs to match between the entity attribute and the row level policy definition
    "dataLength": 40,  <<-- define the datatype sizing precisely (length or precision&scale)
    "dataPrecision": null,
    "dataScale": null,
},

After defining the rowLevelPolicyAttributes, save the Config-package, commit and deploy to runtimes.

DDL generated

CODE

CREATE SECURITY POLICY sales_sales_unit
ADD FILTER PREDICATE security.dataAccessAllowedOnUnit(sales_unit)
ON sales
WITH (STATE = ON);

Change logic for Row Level Policy controlled table

All access rights will be removed
Row Access Policies will be removed
Entity changes will be made
Row Access Policies will be applied
All access rights will be applied

Grant RLS in Google BigQuery

Precondition on Google BigQuery

Data columns are required to be defined in the Row Level Security policy definition in Agile Data Engine.

Role/user being assigned for Agile Data Engine needs to have the either the BigQuery Admin or BigQuery DataOwner permission to be able to define row access policy on tables.

Row level Security

Permission definition in CONFIG_ENTITY_PERMISSIONS-package

grantees-section

CODE

{
    "granteeId": 4, <<-- provide an identifier, which has not been used yet
    "granteeName": "data_viewer", <<-- the database role in target database
    "granteeType": "ROLE" <<-- this can be by default user, role, serviceAccount or group
},

rowLevelPolicies-section

CODE

{
    "rowLevelPolicyId": "", <<--- when generating new policies, use for example command
    
    uuidgen | tr 'A-Z' 'a-z' 
    
    in linux to generate a new unique UUID
    "label": "Allowed Sales Unit", <<-- Short name for the row level policy
    "nameSuffix": "allowed_unit", <-- the suffix to be used when creating the security policy
    "ruleExpression": "sales_unit = 'APAC'",
    "requiresDefinedGrantees": true, <<-- in BigQuery the grantees are required to be defined for the policy
    "deleted": false,
    "description": "Information only to allowed sales units" <<-- short description of the row level policy
},

After defining the rowLevelPolicies, save the config-package and reopen it for adding the needed attributes

rowLevelPolicyAttributes-section

CODE

{
    "rowLevelPolicyAttributeId": "",<<--- when generating new policy attributes, use for example command
    
    uuidgen | tr 'A-Z' 'a-z' 
    
    in linux to generate a new unique UUID
    "rowLevelPolicyId": "<row_policy_id>", <<-- use the rowLevelPolicyId from the rowLevelPolicies-section
    "attributeName": "sales_unit", <<-- the name of the expected attribute both in row access policy and entities 
    "position": 1, <<-- the order of the attributes in case there are multiple attributes for the row level policy
    "datatype": "VARCHAR" <<-- the mandatory logical datatype, needs to match between the entity attribute and the row level policy definition
},

After defining the rowLevelPolicyAttributes, save the Config-package, commit and deploy to runtimes.

Grantee assignment for Row Level Policy

As BigQuery requires the grantees to be defined for the policy for each policy attachment, the boolean (true) set for the row level permission, requiresDefinedGrantees, alters the user interface to require the grantees to be chosen each time such row level policy is being applied to an entity.

DDL generated

CODE

CREATE ROW ACCESS POLICY sales_allowed_unit
ON sales
GRANT TO ('role:data_viewer@example.com')
FILTER USING (sales_unit = 'APAC');

Change logic for Row Level Security controlled table

ADE’s database user will be temporarily given RLS true -filter, which allows full access
Entity changes will be made
ADE’s database user RLS true -filter will be dropped

Grant RLS in Databricks SQL

Precondition on Databricks SQL

Data columns are required to be defined in the Row Level Security policy definition in Agile Data Engine.

Row filters

Permission definition in CONFIG_ENTITY_PERMISSIONS-package

rowLevelPolicies-section

CODE

{
    "rowLevelPolicyId": "", <<--- when generating new policies, use for example command
    
    uuidgen | tr 'A-Z' 'a-z' 
    
    in linux to generate a new unique UUID 
    "label": "Allowed Sales Unit", <<-- Short name for the row level policy
    "preDefinedPolicyFunctionName": "security.data_access_only_to_unit", <<-- the schema specific Row Filter existing in Databricks
    "requiresDefinedGrantees": false, <<-- Databricks does not required defined grantees
    "deleted": false,
    "description": "Information only to allowed sales units" <<-- short description of the row level policy
},

After defining the rowLevelPolicies, save the Config-package and reopen it for adding the needed attributes

rowLevelPolicyAttributes-section

CODE

{
    "rowLevelPolicyAttributeId": "",<<--- when generating new policy attributes, use for example command
    
    uuidgen | tr 'A-Z' 'a-z' 
    
    in linux to generate a new unique UUID
    "rowLevelPolicyId": "<row_policy_id>", <<-- use the rowLevelPolicyId from the rowLevelPolicies-section
    "attributeName": "sales_unit", <<-- the name of the expected attribute both in row access policy and entities 
    "position": 1, <<-- the order of the attributes in case there are multiple attributes for the row level policy
    "datatype": "VARCHAR" <<-- the mandatory logical datatype, needs to match between the entity attribute and the row level policy definition
},

After defining the rowLevelPolicyAttributes, save the Config-package, commit and deploy to runtimes.

DDL generated

CODE

ALTER TABLE data SET ROW FILTER security.data_access_only_to_unit ON (sales_unit);

Change logic for Row Access Policy controlled table

All access rights will be removed
Row Access Policies will be removed
Entity changes will be made
Row Access Policies will be applied
All access rights will be applied