MS SQL - Privacera Data Access - Evaluation Sequence
MS SQL - Privacera Data Access - Evaluation Sequence#
This topic steps through a test sequence intended to help confirm Privacera Data Access and policy-based controls for an MS SQL Server.
Install MS SQL CLI
mssql-cli is a command line query tool for MS SQL Server. It runs on Windows, macOS, and Linux.
For more general information and detailed installation instructions see Microsoft Docs / SQL / Tools / Command prompt utilities / mssql-cli.
For macOS and Windows platforms you can generally install using pip.
$ pip install mssql-cli
On AWS/CentOS /RHEL flavored systems use sudo to first install python-pip, then use pip.
sudo yum install -y python-pip
sudo pip install mssql-cli
For Ubuntu flavor Linux, use apt-get:
# Import the public repository GPG keys
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
# Register the Microsoft Ubuntu repository
sudo apt-add-repository https://packages.microsoft.com/ubuntu/18.04/prod
# Update the list of products
sudo apt-get update
# Install mssql-cli
sudo apt-get install mssql-cli
# Install missing dependencies
sudo apt-get install -f
Create Test Database and Content
Login as Administrator or user with sufficient privileges to create and populate a database.
mssql-cli -S ${MSSQL_SERVER_NAME}.database.windows.net -d${DATABASE} -U${ADMIN_USER} |
mssql-cli
CREATE DATABASE customer CREATESCHEMA customer_schema; |
Create a client 'Users'
Log into Privacera Portal.
In Privacera Portal Access Management: Users/Groups/Roles:
-
Create Role "Sales_Role".
-
Create User "Emily" and make Emily part of the Sales_Role.
Test Use Cases
1. Confirm the ability to log on to the Customer database as user 'emily'.
mssql-cli -S ${MSSQL_SERVER_NAME}.database.windows.net -d${DATABASE} -U${USER} # For example : mssql-cli -S test.database.windows.net -d customer -U emily |
Evaluate Privacera Access Control
In Privacera Portal: Access Management: Resource Policies, open the privacera_mssql application (in the MSSQL System).
Confirm policy "all - database, schema, table, column" is in place and defined.
Return to your mssql client and confirm access by user emily. While logged in as 'emily', select from customer database.
select * from customer_schema.customer_data; |
Return to Privacera Portal: Access Management: Resource Policies, privacera_mssql application, open the policy 'all - database, schema, table, column' for to edit. Disable this policy.
Return to the mssql client and attempt the selection. This selection should fail.
select * from customer_schema.customer_data; |