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
-
Confirm the MS SQL Server is configured to work with Azure AD Users.
-
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.
-
For each targeted database:
-
Log on to the target database with an Admin role account.
-
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
-
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];
-
For each targeted database:
-
Log on to the target database with an Admin role account.
-
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
-
Confirm the MS SQL Server is configured to work with Azure AD Users.
-
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.
-
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];
-
For each targeted database:
-
Log on to the target database with an Admin role account.
-
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
-
Consult the following article How to Configure MS SQL Server for Database Synapse Audits.
-
Using information from that article obtain the Audit storage URL. This will be used in the Privacera MS SQL PolicySync configuration.
CLI Configuration#
-
SSH to the instance where Privacera is installed.
-
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
-
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. -
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 |
||
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 |
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 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 |