Overview of this Lecture / week
This week we will cover many of the aspects relating to Database security. Unfortunately database security is not taken seriously enough in most organisations. People can access the data and database using a variety of tools. Great care must be taken to ensure that the necessary security is implemented and doesn’t apply to a particular tool or application. Data security most not be implemented in applications or in the business logic tier. The last line of defence is the database. But data security should be implemented at all points where the data is accessed.
Therefore a multi approach to data and database security is needed. You will not use one method of security, but you will use many security methods together.
Make sure to read articles about the Thick Database Paradigm, Smart Database Paradigm, etc.
Click here to download notes – Part AL5 - Database Security
Click here to download notes – Part BL6 - Database Security - Part 2
Videos of Notes
Check out this Database security video
Assignment B Handout.
Questions about the assignments will only be addressed during class time. I will also maintain a FAQ for Assignments with all the questions and answers. This is to allow all students to benefit from the answer.
You will need to use the Oracle Virtual Machine to complete these lab exercises
Lab1 – Creating and Managing Users
Create a new user called STUDENT2.Grant them CONNECT, RESOURCE privileges, and UNLIMITED storage/space on the USERS tablespace.
Connect to the database user you have been using for the previous week lab exercises. Select one of the tables and grant the new STUDENT2 user select privileges on this table.
Login to the STUDENT2 schema, or reconnect if you are already logged in. Select from the table in your other schema. You should be able to see the all the records in that table.
Try to update a record in that table while using the STUDENT2 schema.
Try inserting a record in that table while using the STUDENT2 schema.
Try deleting a record in that table while using the STUDENT2 schema.
Did you encounter any issues? If you did, investigate what you would need to do to overcome these issues.
Lab 2 – Creating and Using Roles
Based on what you did and learned in the previous lab exercise above, look at how you can solve the problem using ROLES.
Imagine if you have to grant 4 or 5 privileges on a table to 200+ users in a company. That’s a lot of work.
For the table (used in Lab1 above), create a Role to contain SELECT and UPDATE privileges. Assign this to STUDENT2. (Make sure to revoke any privileges you granted to STUDENT2 from your other schema first)
Now grant STUDENT2 the newly created role.
Login into or reconnect to STUDENT2 and test to see what commands you can run against the table and to make sure the Role is working correctly.
Lab 3 – Data Redaction
See the sample code in Part 2 of the notes (see above).
Create a data redaction policy to apply to one of the attributes in your table.
While logged into the schema owner (of the table) SELECT from the table and verify you can see the full non-redacted data.
Log into or reconnect to STUDENT2 and SELECT the data. Verify that the data displayed for the attribute is now redacted.
Create a new redaction policy for another attribute in the table and use a Regular Expression to redact the data.
Test and verify this is working.
Additional Reading Materials
Smart Database Paradigm Recourse Center
Pink Database Paradigm
Relational Databases and the Thick Database Paradigm
SQL Injection – What not to do and what you should do.