Skip to content

Policy Sync#

Azure MSSQL Policy Sync#

These instructions to enable and configure a Privacera MSSQL database connector to an existing MSSQL database running in an Azure cloud platform. This connector uses the PolicySync method in which access policies defined in Privacera are mapped to and synchronized to the 'native' access controls in MSSQL.

The PolicySync approach has several benefits and advantages:

  • Fine-grained access control - at the database, schema, table, view, and column levels.

  • Column level masking

  • Dynamic row-level filters on tables and views

Using Azure MSSQL as the database for Power BI Desktop, you can configure workspace access-control on the on-premises Power BI. For more information, click here.

Prerequisites

The MSSQL Server must already be installed and running.  

If you are installing an evaluation, you may need to install and configure an MSSQL Server with one or more databases to test against.

1) Target Database Access

The MSSQL Database server must also be accessible from the Privacera Platform host(s).  The standard inbound port for MSSQL Server access is TCP 1433.  Make sure that is open 'outbound' from Privacera Platform host(s) and inbound to your target MSSQL server.

2) Access Control by Privacera Service Account

Privacera Platform requires access to the target database and the service account must be established in a 'loginmanager' role. This can be configured in three ways:  (1) Access Control on Azure AD Users; (2) Access Control on Local Database Users; or (3) Access Control on both Azure AD user and local users.

Access Control on Azure AD Users

  1. Confirm the MSSQL Server is configured to work with Azure AD Users. 

  2. In your Azure AD, create a Privacera 'service' user to be used by Privacera for the Policy access control synchronization.  For this example we'll assume the name is 'privacera_policysync@example.com' but set the value appropriately for your domain(s).    Keep note of the username and password as we'll use both later.

  3. For each targeted database:

    1. Log on to the target database with an Admin role account.

    2. Execute the following:

      IF DATABASE_PRINCIPAL_ID('privacera_policysync@example.com') IS NULL BEGIN 
  CREATE USER [privacera_policysync@example.com] FROM EXTERNAL PROVIDER; 
  END;

-- Grant full control on database to privacera_policysync@example.com user
GRANT CONTROL ON DATABASE::${YOUR_DATABASE} TO [privacera_policysync@example.com];leverage

Access Control on Local Database Users

  1. Create a Privacera 'service' user in the master database to be used by Privacera for the Policy access control synchronization.  For this example, we'll assume the name is 'privacera_policysync' but set the value appropriately for your domain(s). Keep note of the username and password as we'll use both later.

    IF NOT EXISTS (SELECT name  FROM sys.sql_logins WHERE name = 'privacera_policysync') BEGIN 
  CREATE LOGIN [privacera_policysync] WITH PASSWORD = '${PASSWORD}' 
  END;

IF DATABASE_PRINCIPAL_ID('privacera_policysync') IS NULL BEGIN 
  CREATE USER [privacera_policysync] FROM LOGIN [privacera_policysync]; 
  END;

EXEC sp_addrolemember [loginmanager], [privacera_policysync];

  2. For each targeted database:

    1. Log on to the target database with an Admin role account.

    2. Execute the following:  

      IF DATABASE_PRINCIPAL_ID('privacera_policysync') IS NULL BEGIN
  CREATE USER [privacera_policysync] FROM LOGIN [privacera_policysync];
  END;

-- Grant full control on database to privacera_policysync user
GRANT CONTROL ON DATABASE::${YOUR_DATABASE} TO [privacera_policysync];

Access Control on Azure AD and Local Database Users

  1. Confirm the MSSQL Server is configured to work with Azure AD Users. 

  2. In your Azure AD, create a Privacera 'service' user to be used by Privacera for the Policy access control synchronization.  For this example, we'll assume the name is 'privacera_policysync@example.com' but set the value appropriately for your domain(s).    Keep note of the username and password as we'll use both later.

  3. Create a Privacera 'service' user in the master database to be used by Privacera for the Policy access control synchronization.  For this example, we'll assume the name is 'privacera_policysync@example.com' but set the value appropriately for your domain(s).    Keep note of the username and password as we'll use both later. 

    IF DATABASE_PRINCIPAL_ID('privacera_policysync@example.com') IS NULL BEGIN 
  CREATE USER [privacera_policysync@example.com] FROM EXTERNAL PROVIDER; 
  END;

EXEC sp_addrolemember [loginmanager], [privacera_policysync@example.com];

  4. For each targeted database:

    1. Log on to the target database with an Admin role account.

    2. Execute the following:  

      IF DATABASE_PRINCIPAL_ID('privacera_policysync@example.com') IS NULL BEGIN
  CREATE USER [privacera_policysync@example.com] FROM EXTERNAL PROVIDER;
  END;

-- Grant full control on database to privacera_policysync@example.com user
GRANT CONTROL ON DATABASE::${YOUR_DATABASE} TO [privacera_policysync@example.com];

3) Create or Identify an ADLS Gen2 storage used to store MSSQL Server Audits

  1. Consult the following article How to Configure MSSQL Server for Database Synapse Audits.

  2. Using information from that article obtain the Audit storage URL.  This will be used in the Privacera MSSQL PolicySync configuration.

Configuration

  1. Create and open for edit vars.policysync.mssql.yml.

    cd ~/privacera/privacera-manager
cp config/sample-vars/vars.policysync.mssql.yml config/custom-vars/
vi custom-vars/vars.policysync.mssql.yml

  2. Set the following properties for your specific installation. For property details and description, click here.

    MSSQL_JDBC_URL: "jdbc:sqlserver://example.database.windows.net:1433"
MSSQL_JDBC_DB: "master"
MSSQL_JDBC_USERNAME: "<PLEASE_CHANGE>"
MSSQL_JDBC_PASSWORD: "<PLEASE_CHANGE>"
MSSQL_AUTHENTICATION_TYPE: "SqlPassword"
MSSQL_DEFAULT_USER_PASSWORD: "<PLEASE_CHANGE>"
MSSQL_OWNER_ROLE: "<PLEASE_CHANGE>"
MSSQL_AUDIT_ENABLE: "true"
MSSQL_AUDIT_STORAGE_URL: "https://sqlaudit.blob.core.windows.net/sqldbauditlogs/example"
MSSQL_AUDIT_STORAGE_URL: "<PLEASE_CHANGE>"
MSSQL_MANAGE_DATABASE_LIST: "<PLEASE_CHANGE>"
MSSQL_MANAGE_SCHEMA_LIST: “<PLEASE_CHANGE>”
MSSQL_MANAGE_TABLE_LIST: “<PLEASE_CHANGE>”
MSSQL_MANAGE_VIEW_LIST: “<PLEASE_CHANGE>”
MSSQL_MANAGE_ENTITIES: "false"
MSSQL_GRANT_UPDATES: "false"
MSSQL_ENABLE: "true"
MSSQL_V2_ENABLE: "true"
MSSQL_MANAGE_ENTITY_PREFIX: "dev_*,sa_*"
MSSQL_MANAGE_ENTITY_PREFIX: ""
MSSQL_ENTITY_ROLE_PREFIX: "priv_"
MSSQL_AUDIT_LOAD_KEY: "load_synapse"
MSSQL_IGNORE_USER_LIST: "user1,user2,user3"
MSSQL_IGNORE_GROUP_LIST: "group1,group2,group3"
MSSQL_IGNORE_ROLE_LIST: "role1,role2,role3"
MSSQL_MANAGE_USER_LIST: "user1,user2,user3"
MSSQL_MANAGE_GROUP_LIST: "group1,group2,group3"
MSSQL_MANAGE_ROLE_LIST: "role1,role2,role3"
MSSQL_MANAGE_USER_FILTERBY_GROUP: "false"
MSSQL_MANAGE_GROUPS: "false"
MSSQL_ENABLE_ROW_FILTER: "false"
MSSQL_ENABLE_VIEW_BASED_MASKING: "false"
MSSQL_MANAGE_GROUP_POLICY_ONLY: "false"
MSSQL_EXTERNAL_USER_AS_INTERNAL: "false"

There are two properties that establish the type of 'masking' that will be supported for this connector:  'native masking', and 'view-based masking'.

Native Masking - in MSSQL known as 'Dynamic Data Masking' - is supported directly by MSSQL Server. This level of masking has low granularity and only supports the ability to mask by database for each user. See Microsoft documentation Dynamic Data Masking for more background.

Privacera Platform supports 'View-based Masking', which for MSSQL supports Row-level filtering and masking.  View-based masking is the default and is recommended.

Property Name Value/Comments
MSSQL_ENABLE_MASKING Set to 'true' to enable MSSQL 'native' masking and disable View-based masking functionality.
MSSQL_ENABLE_VIEW_BASED_MASKING Set to 'true' to enable View-based masking functionality.
MSSQL_UNMASKED_DATA_ROLE A comma-separated MSSQL role list for roles authorized for unmasked data. all other users will see masked data.

Power BI PolicySync#

This section covers how to enable configure Privacera Power BI connector for workspace fine-grained access-control on Power BI running in Azure. You can set permissions in a Privacera policy depending on the workspace roles: Admin, Member, Contributor, Viewer. Only users and groups from the Azure Active Directory are allowed in Azure Power BI.

Prerequisites

Ensure that the following prerequisites are met:

  1. Create a service principal and application secret for the Power BI, and get the following information from Azure Portal. For more information, refer the Microsoft Azure documentation - click here.

    • Application (client) ID
    • Directory (tenant) ID
    • Client Secret

  2. Create a group to assign your created Power BI application to it. This is required because the Power BI Admin API allows only the service principal to be an Azure AD Group. For more information, refer the Microsoft Azure documentation - click here.

    Follow the steps in the link given above, and configure the following to create a group and add Power BI as a member:

    1. On the New Group dialog, select security in the Group type, and then add the required group details.

    2. Click Create.

    3. On the +Add members dialog, select your Power BI application.

  3. Configure Power BI Tenant to allow Power BI service principals to read the REST API. For more information, refer the Microsoft Azure documentation - click here.

    Follow the steps in the link given above and configure the following:

    1. In the Developer settings, enable Allow service principals to use Power BI APIs.

    2. Select Specific security groups (Recommended), and then add the Power BI group you created above.

    3. In the Admin API Settings, enable Allow service principals to use read-only Power BI admin APIs (Preview). For more information, refer the Microsoft Azure documentation - click here.

    4. Select Specific security groups, and then add the Power BI group you created above.

  4. Enable Privacera UserSync for AAD to pull groups attribute ID. For more details, refer to the topic Azure Active Directory - Data Access User Synchronization.

Configuration

  1. SSH to the instance where Privacera is installed.

  2. Run the following command.

    cd ~/privacera/privacera-manager
cp config/sample-vars/vars.policysync.powerbi.yml config/custom-vars/
vi custom-vars/vars.policysync.powerbi.yml

  3. Edit the following properties. For property details and description, click here.

    POWER_BI_USERNAME: "<PLEASE_CHANGE>"
POWER_BI_PASSWORD: "<PLEASE_CHANGE>"
POWER_BI_TENANT_ID: "<PLEASE_CHANGE>"
POWER_BI_CLIENT_ID: "<PLEASE_CHANGE>"
POWER_BI_CLIENT_SECRET: "<PLEASE_CHANGE>"
POWER_BI_V2_ENABLE: "true"
POWER_BI_MANAGE_WORKSPACE_LIST: "<PLEASE_CHANGE>"
POWER_BI_IGNORE_USER_LIST: "<PLEASE_CHANGE>"
POWER_BI_MANAGE_USER_LIST: "<PLEASE_CHANGE>"
POWER_BI_MANAGE_GROUP_LIST: "<PLEASE_CHANGE>"
POWER_BI_IGNORE_WORKSPACE_LIST: "<PLEASE_CHANGE>"
POWER_BI_IGNORE_USER_LIST: "<PLEASE_CHANGE>"
POWER_BI_MANAGE_USER_FILTERBY_GROUP: "false"
POWER_BI_ENABLE_AUDIT: "false"
POWER_BI_AUDIT_LOAD_KEY: "load"
POWER_BI_GRANT_UPDATES: "true"

  4. Run the following command:

    cd ~/privacera/privacera-manager/
./privacera-manager.sh update

Limitations

  • The role in a resource policy of Access Management is not supported.

  • Only AAD users/groups are supported in a resource policy of Access Management. The Local users/groups (created manually in Access Management) is not supported.