SQL Common Security Expressions

IT306 ExcelTrack Module Preview

Cloud Services Management

Term 1902B

Page 2 of 5

IT350 Module Preview

Advanced Database Concepts

Page 8 of 8

Task 2 - Testing Security Flaws

Purpose

Bike Stores is implementing a new website to allow online bike purchases. In support of this, a stored procedure called GetUser has been created to retrieve a customer record using the customer’s first name, last name, and password supplied from a website form. From your research on database security, you notice that there is a serious security concern with the GetUser procedure. You have established a series of SQL statements to test for GetUser security flaws. These statements will be used with two temporary tables called staffs_temp and order_items_temp, which are copies of the staffs and order_items tables, respectively.

Assessment Instructions

You must have SQL Server Express and SQL Server Management Studio (SSMS) installed to perform this assessment task. The sample database for this module is called BikeStores. Download the Database Design Diagram below.

· Bikestores Database Design Diagram

Use the BikeStores database design diagram for your stored procedure assessment..

Please copy each SQL statement into a Microsoft® Word® document. Below that, enter a screenshot of the execution of the SQL showing the code and the resulting output. Below that, enter text explaining the SQL statement and outcome. What has occurred? Was there an adverse impact from the SQL statement execution?

Task 2.1 - Dynamic SQL Statements Without Binding

Execute the individual SQL statements contained within the following text file in a Microsoft SSMS query window:

· BikeStores SQL Injection Commands

After executing these statements, explain why the GetUser stored procedure is problematic. Did any unauthorized data modifications occur? Did data corruption occur? Were any tables improperly dropped from the database?

Task 2.2 - Dynamic SQL Statements With Binding

Based on the Task 2.1 results, the GetUser stored procedure has been revised and replaced with a stored procedure called GetUserWithBind. Execute the individual SQL statements contained within the following text file in a Microsoft SSMS query window:

· BikeStores SQL Injection Commands With Binding

After executing these statements, explain whether the GetUserWithBind stored procedure has solved the security issues uncovered in Task 2.1. If the security issues have been resolved, explain how it was accomplished. Perform research and briefly describe the binding parameter concept. Below is a table contrasting the use of “execute” versus “execute sp_executesql” in Microsoft SQL Server.

EXEC or EXECUTE	EXEC sp_executesql
Parameterization is not possible	Parameterization is possible
Risk of SQL injection is high	Risk of SQL injection is lower
An EXEC call wastes a lot of space in the plan cache	It gets cached like a stored procedure without wasting white spaces if parameterization is used
It does not force a plan to be cached	It forces the plan to be cached at first execution

Task 3 - Security Implementation

Purpose

The owners of Bike Stores want to implement a greater level of security within the company database. You will explore the implementation of permissions, roles, data masking, and column encryption into the database structure. These measures can help maintain the confidentiality and integrity of sensitive data contained within the BikeStores database.

Assessment Instructions

You must have Microsoft SQL Server Express and SQL Server Management Studio (SSMS) installed to perform this assessment task. The sample database for this module is called BikeStores. Download the Database Design Diagram below.

· Bikestores Database Design Diagram

Use the BikeStores database design diagram for your database security implementation.

Please copy each SQL statement into a Microsoft® Word® report document. Below that, enter a screenshot of the execution of the SQL showing the code and the resulting output. Below that, enter text explaining the SQL statement and outcome. Please note that you do not have to show all query output records in your report document.

Task 3.1 - Establish a Table Holding Customer Credit Card Data

A new table needs to be created in the BikeStores database to store customer credit card information. The table creation script and instructions can be accessed via the following:

· Instructions for Establishing the BikeStores Customer Credit Cards Table

· Create Customer Credit Cards Table Script

Provide a brief overview of what occurs in the table creation script. What constraints are imposed on the Sales.Customer_Credit_Cards table?

Task 3.2 - Permissions and Roles

Generate and execute SQL statements to establish the following roles in the BikeStores database: GeneralUser and Salesperson. Establish and execute SQL statements that grant the following permissions to these roles:

ROLE	TABLE	PERMISSION
Salesperson	Sales.Staffs	SELECT
Salesperson	Sales.Customers	SELECT INSERT UPDATE DELETE
GeneralUser	Sales.Customers	SELECT
Salesperson	Sales.Customer_Credit_Cards	SELECT INSERT UPDATE DELETE
GeneralUser	Sales.Customer_Credit_Cards	SELECT

Then execute the SQL statements contained in the following text file to verify the incorporated roles/permissions:

· BikeStores Role Application Statements

Provide a description of the applied roles/permissions contained in the text file. What can the applicable users do in the BikeStores database? Did any query errors occur? If yes, why?

Task 3.3 - Data Masking

Execute the SQL statements contained in the following text file to implement data masking on two attributes:

· BikeStores Masking Application Commands

Discuss what transpires with each command/statement.

Task 3.4 - Column Encryption

BikeStores management has decided that masking customer credit card numbers does not provide sufficient protection. You have now been asked to encrypt the credit card numbers in the database. Execute the SQL statements contained in the following text file to encrypt customer credit card numbers:

· Bike Stores Encryption Commands

Discuss what transpires with each command/statement. What is the difference between symmetric and asymmetric keys? What are the challenges associated with each encryption key type?

After you receive access to each module, you will be able to see the submission requirements and a Checklist Rubric for the Competency Assessment.