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.
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.
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:
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:
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
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.
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.
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:
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:
Then execute the SQL statements contained in the following text file to verify the incorporated roles/permissions:
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:
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:
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.