Lab Manual
In the first part of this lab you will learn how to protect the data in the Oracle database by implementing security via profiles, roles, and privileges. You will also use object privileges to protect individual tables or columns.
You can use Oracle Data Dictionary list at https://ss64.com/orad/. Use CTRL+F to search the page. Oracle uses self-explanatory names for tables, views and columns.
In the second part of this lab you will learn how to protect the data in the SQL Server database by implementing security via security policies, roles, and privileges. You will also use object privileges to protect individual tables or columns.
Use INFORMATION_SCHEMA reference at https://msdn.microsoft.com/en-us/library/ms186778.aspx
The requirement of the lab is listed as deliverables. The deliverable is abbreviated as "D" in this manual.
Deliverables
NOTE: do not use GUI, you have to provide SQL code. Check out "Lab4Tutorial.docx" for tutorials.
D1 - D12 need to completed using Oracle
D1. Create the following three user-defined roles that are shown in the table below and assign them the specified permissions for the
OE.CUSTOMERS table.
|
Role
|
Select
|
Insert
|
Update
|
Delete
|
|
account_managers
|
|
|
CREDIT_LIMIT only
|
|
|
customer_service
|
√
|
|
√
|
|
|
sales_reps
|
√
|
√
|
√
|
|
Include SQL scripts:
D2. Create user user1. Assign role account_manger to user1
Include SQL scripts:
D3. Use SQL Plus to login as user1 and test permissions.
List all steps you performed to test permissions add screenshot with results
D4. Close SQL Plus. Assign role sales_reps to user1
Include SQL scripts:
D5. As a part of an auditing process your company is conducting, you are required to report all users who have been granted system privileges with ADMIN option.
These users can grant system privileges to other users. Write a query that will display users and their privileges. (Hint: use data dictionary SYS_PRIVS)
Include SQL scripts, number of rows and screenshot of the results
D6. As a part of an auditing process your company is conducting, you are required to report all users who have been granted object privileges with GRANT option.
These users as well as object owners can grant object privileges to other users. Write a query that will display owner, grantee and table name. (Hint: use data dictionary TAB_PRIVS)
Include SQL scripts, number of rows and screenshot of the results
D7. Write SQL statement that lists all roles AND users (without repetition) who have access to the OE.CUSTOMERS table.
Include SQL scripts and screenshot of the results
D8. Do you see ACCOUNT_MANAGERS on the list generated from D7? Why?
D9. How can you check that whether ACCOUNT_MANAGERS have permission to update only one column in CUSTOMERS table?
Include SQL scripts and screenshot of the results
D10. Create an Oracle profile OE_PROFILE to limit database resources using the following criteria:
a. Logon time-2 hours
b. Idle time-3 minutes
c. CPU time required per call-1 second
d. 1 block per query
Include SQL scripts:
D11. Assign profile OE_PROFILE to user1.
Include SQL scripts:
D12. Test that IDLE time setting in the profile works.
Outline steps and provide SQL queries if necessary
D13 - D22 need to completed using SQL Server
D13. Create two user-defined roles in database AdventureWorks that are shown in the following table and assign them the specified permissions for the PURCHASING.Vendor table:
|
Role
|
Select
|
Insert
|
Update
|
Delete
|
|
account_managers
|
√
|
√
|
√
|
√
|
|
fin_clerk
|
AccountNumber, Name, CreditRating, and ActiveFlag
|
|
CreditRating and ActiveFlag
|
|
Include SQL scripts:
D14. Use data dictionary to check that permissions were granted correctly in D13. Hint: check column permissions and table permission separately.
Include SQL scripts and screen shots of the results:
D15. Use Computer Management to create local Windows user with user name sam. (Right click My Computer - Mange - Local Users and Groups - Right click - New)
You don't need to include anything for this step.
D16. Grant sam permission to logon to SQL server and AdventureWorks database.
Include SQL scripts:
D17. Open new query window and run statement "execute as login" to execute queries as sam (see chapter slides). Try to select NAMES from Vendor table.
Include SQL scripts and screen shots of the results:
D18. Switch to the previous query window (the one you used to run query in D16) and Assign role fin_clerk to sam.
Include SQL scripts:
D19. Switch to the "new" query window (the one you opened in #6) and try AGAIN to select NAMES from Vendor table when you logged as sam.
Include SQL scripts and screen shots of the results:
D20. Try to delete vendor with VendorID = 1 when you logged as sam (Explain the result). Include SQL scripts and explain the result.
D21. Explain in detail the use of the DENY statement in SQL Server.
Provide example on how to restrict access to a column VendorID in a table
D22. Create a password policy that password expires in 60 days and locks the user's account if the password is entered incorrectly four times in a row. Document the process
Step-by-step instructions and screenshot of successful operation.