Microsoft SQL - Privacera data access for evaluation sequence

Microsoft SQL- Privacera data access for 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 Microsoft 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


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:

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;

Privacera Platform

Table of ContentsTable of Contents

Microsoft SQL - Privacera data access for evaluation sequence

Microsoft SQL- Privacera data access for evaluation sequence