Skip to content

Microsoft SQL#

These instructions to enable and configure a Privacera Microsoft SQL (MS SQL) database connector to an existing MS SQL 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 MS SQL.

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

Prerequisites#

The MS SQL Server must already be installed and running.  

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

1) Target Database Access

The MS SQL Database server must also be accessible from the Privacera Platform host(s).  The standard inbound port for MS SQL Server access is TCP 1433.  Make sure that is open 'outbound' from Privacera Platform host(s) and inbound to your target MS SQL 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 MS SQL 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];

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 MS SQL 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 MS SQL Server Audits

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

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

CLI Configuration#

  1. SSH to the instance where Privacera is installed.

  2. Run the following command.

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

  3. Set the properties for your specific installation. For property details and description, see the Configuration Properties section that follows.

    Note

    Along with the above properties, you can add custom properties that are not included by default. For more information about these properties, see Microsoft SQL Connector.

    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 MS SQL known as 'Dynamic Data Masking' - is supported directly by MS SQL 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 MS SQL supports Row-level filtering and masking.  View-based masking is the default and is recommended.

    Property Name Value/Comments
    MS SQL_ENABLE_MASKING Set to true to enable MS SQL 'native' masking and disable View-based masking functionality.
    MS SQL_ENABLE_VIEW_BASED_MASKING Set to true to enable View-based masking functionality.
    MS SQL_UNMASKED_DATA_ROLE A comma-separated MS SQL role list for roles authorized for unmasked data. all other users will see masked data.

  4. Run the following commands.

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

Configuration Properties#

Property Mandatory Description Default Value Example
MSSQL_V2_ENABLE Yes Property to enable/disable MS SQL. true
MSSQL_JDBC_URL Yes This property is used to set the JDBC URL, which can be used to connect to the MS SQL server.
JDBC URL must follow below convention:
jdbc:sqlserver://<MSSQL_SERVER_HOST>:<MSSQL_SERVER_PORT>.
Get the URL from the Prerequisites section above.		 jdbc:sqlserver://example.com
MSSQL_MASTER_DB Yes This property is used to specify the JDBC master database that will be used to establish the initial connection to MS SQL. master
MSSQL_JDBC_USERNAME Yes This property is used to specify the JDBC username that will be used to connect to MS SQL.
MSSQL_JDBC_PASSWORD Yes This property is used to specify the JDBC password that will be used to connect to MS SQL.
MSSQL_AUTHENTICATION_TYPE Yes

Authentication type for the database engine.

If MSSQL_JDBC_USERNAME is a 'local user', set value as below:

MSSQL_AUTHENTICATION_TYPE: "SqlPassword"

If MSSQL_JDBC_USERNAME is an Azure AD user, then set as below: 

MSSQL_AUTHENTICATION_TYPE: "ActiveDirectoryPassword"

 SqlPassword
MSSQL_DEFAULT_USER_PASSWORD Yes This property is used to specify the password that will be used for each new user created by PolicySync.  
MSSQL_OWNER_ROLE Yes This property is used to specify who owns all of the resources managed by PolicySync.
The specified role will become the owner of all managed resources and will have complete control over those resources.
We support changing the owner of a database, schema, tables, and views.

Note: If the owner role is left blank, ownership will not change, and users who create tables/views or other objects will be the owner of those objects, and PolicySync will not be able to control access to those objects.

PRIVACERA_DEFAULT_OWNER
MSSQL_AUDIT_ENABLE Yes Set to true if audits have been configured for the MS SQL server.

Access audits will be filtered based on managed resources.

 true
MSSQL_AUDIT_STORAGE_URL Yes

Audits storage URL obtained in Prerequisite section.

If this parameter is left blank, Privacera Platform will target all databases attached to the MS SQL Server. If one or more database names are listed (comma separated values), only those databases will be controlled by Privacera Platform. 

   example.com
MSSQL_MANAGE_DATABASE_LIST Yes This property specifies a comma-separated list of database names that PolicySync manages.
If you want to manage all databases, you can skip this property.
This also accepts wildcards.
The manage database list takes precedence over the ignore database list.
For example, testdb1, testdb2, sales db*		 testdb1, testdb2, sales db*
MSSQL_MANAGE_SCHEMA_LIST Add the database schemas to be managed by PolicySync.
Enter the value for the property in the following:
{database_name}.{schema_name}
If the value is kept blank, then all schemas will be managed.
If the value is none, then no schemas will be managed.
If the value is specified as {database_name}.*, then all schemas will be managed.
Use comma-separated values to enter multiple schemas.		 customer.customer_schema1,customer.customer_schema2
or
customer.*
MSSQL_MANAGE_TABLE_LIST Add the database tables to be managed by PolicySync.
Enter the value for the property in the following:
{database_name}.{schema_name}.{table_name}
If the value is kept blank, then all tables will be managed.
If the value is none, then no tables will be managed.
If the value is specified as {database_name}.{schema_name}.*, then all tables will be managed.
Use comma-separated values to enter multiple tables.		 customer.customer_schema1.table1,customer.customer_schema2.table2
or
customer.customer_schema.*
MSSQL_GRANT_UPDATES Yes This property determines whether actual grant/revoke and create/update/delete queries for user/group/role run on MS SQL. true
MSSQL_AUDIT_LOAD_KEY Load the access audits from MS SQL using SQL queries. Load
MSSQL_MANAGE_USER_LIST This property specifies a comma-separated list of user names that PolicySync manages.
If you want to manage all users, you can skip this property.
This also works with wildcards.
The ignore users list takes precedence over the manage users list.
For example, user1,user2,dev_user* 		user1,user2,dev_user*
MSSQL_MANAGE_GROUP_LIST This property specifies a comma-separated list of group names that PolicySync manages.
If you want to manage all groups, you can skip this property.
This also works with wildcards.
The ignore group list takes precedence over the manage groups list.
For example, group1,group2,dev_group*
 group1,group2,dev_group*
MSSQL_MANAGE_ROLE_LIST This property specifies a comma-separated list of role names that PolicySync manages.
If you want to manage all roles, you can skip this property.
This also accepts wildcards.
The manage role list takes precedence over the ignore role list.
For example, role1,role2,dev_role*
 role1,role2,dev_role*
MSSQL_IGNORE_USER_LIST This property specifies a comma-separated list of user names that PolicySync does not manage.
If you do not want to ignore any users, you can leave this property blank.
This also accepts wildcards.
This takes precedence over the list of users to manage.
For example, user1,user2,dev_user* 		user1,user2,dev_user*
MSSQL_IGNORE_GROUP_LIST This property specifies a comma-separated list of group names that PolicySync does not manage.
If you do not want to ignore any groups, you can leave this property blank.
This also accepts wildcards.
This takes precedence over the list of manage groups.
For example, group1,group2,dev_group* 		group1,group2,dev_group*
MSSQL_IGNORE_ROLE_LIST This property specifies a comma-separated list of role names that PolicySync does not manage. .
If you do not want to ignore any roles, you can leave this property blank.
This also works with wildcards.
This takes precedence over the manage roles list.
For example, role1,role2,dev role* 		role1,role2,dev_role*
MSSQL_MANAGE_USER_FILTERBY_GROUP Set this property to true if you only want to manage users who belong to the groups specified in the MSSQL_MANAGE_GROUP_LIST property. false
MSSQL_MANAGE_GROUPS Use this property to create roles in MS SQL when groups are retrieved from Apache Ranger. true
MSSQL_ENABLE_ROW_FILTER If you want to enable native row filter functionality, set this property to true. This is not recommended because native row filters can only be created on tables and not on views. false true/false
MSSQL_ENABLE_VIEW_BASED_MASKING

Set this property to true, if you want to enable secure view-based masking in MS SQL PolicySync.

Note:- MS SQL does not support native masking. It is recommended to use view-based masking.

 true true/false
MSSQL_MANAGE_GROUP_POLICY_ONLY If this property is set to true, groups will only be used for access management. Any policies applied to users and roles will be ignored. false
MSSQL_EXTERNAL_USER_AS_INTERNAL If this property is set to true, it is used to create an external user as an internal user. false true/false