Task 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 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
Describe 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: 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 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:
BikeStores 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?