Skip to content

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;

CREATETABLE customer_schema.customer_data (
idint,
person_name varchar(100),
email_address varchar(100),
ssn varchar(100),
country varchar(100),
us_phone varchar(100),
address varchar(100),
account_id varchar(100),
zipcode varchar(100));

insertinto customer_schema.customer_data values (1, 'Nancy','nancy@yahoo.com','201-99-5532','US','856-232-9702','939 Park Avenue','159635478','33317');
insertinto customer_schema.customer_data values (2,'Gene','gene@google.us','202-99-5532','UK','954-583-0575','303 Johnston Blvd','236854569','95202');
insertinto customer_schema.customer_data values (3,'Edward','edward@facebook.com','203-99-5532','US','209-626-9041','130 Hollister','365412985','60173');
insertinto customer_schema.customer_data values (4,'Pearlene','pearlene@gmail.com','204-99-5532','US','708-471-6810','17 Warren Rd','452189732','90017');
insertinto customer_schema.customer_data values (5,'James','james@cuvox.de','205-99-5532','US','661-338-6787','898 Newport Gray Rd','517836427','94041');
insertinto customer_schema.customer_data values (6,'Pamela','pamela@cuvox.de','206-99-5532','UK','650-526-5259','861 Strick Rd','685231473','80214');
insertinto customer_schema.customer_data values (7,'Donna','donna@fleckens.hu','207-99-5532','US','303-239-4282','1784 S Shore Dr','789563258','1730');
insertinto customer_schema.customer_data values (8,'Amy','amy@gustr.com','208-99-5532','US','774-553-4736','9522 Apple Valley Dr','854126945','55102');
insertinto customer_schema.customer_data values (9,'Adam','adam@teleworm.us','209-99-5532','UK','651-297-1448','745 Old Springville Rd','965412381','43201');
insertinto customer_schema.customer_data values (10,'Lucille','lucille@armyspy.com','210-99-5532','US','740-320-1270','4223  Midway Road','785651236','89102');
insertinto customer_schema.customer_data values (11,'Edard','edu@gustr.com','211-99-5532','UK','702-257-8796','3659  Dye Street','965121354','53207');
insertinto customer_schema.customer_data values (12,'Nick','nick@jourrapide.com','212-99-5532','US','414-483-8638','2966  Nutters Barn Lane','563515264','72764');
insertinto customer_schema.customer_data values (13,'Brian','brian@einrot.com','213-99-5532','US','479-872-9783','3300  Worthington Drive','654621233','91303');
insertinto customer_schema.customer_data values (14,'Stella','stella@jourrapide.com','214-99-5532','US','818-596-6681','1893  Ingram Road','261613654','35816');
insertinto customer_schema.customer_data values (15,'Leona','leona@dayrep.com','215-99-5532','UK','256-250-5413','4244  Burnside Court','986513211','75069');

SELECT * FROM customer_schema.customer_data;

Create a client 'Users'

Log into Privacera Portal.

In Privacera Portal Access Management: Users/Groups/Roles:

  1. Create Role "Sales_Role".

  2. 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;
Last update: July 23, 2021