Database Security
In many cases, there are different users that connect to a database. Views are virtual tables based on the physical data. They are usually used either to simplify queries by combining tables or for security purposes to limit the access of data to columns or rows to the user.
Two basic tools for database security at the data level are assigning (granting) privileges to users and creating views. Users can be granted different kinds of access to data in tables that will restrict their read, insert, update, and delete functions. A "view" is a pseudo-table that restricts data by selecting a subset of columns or rows selected from one or more tables. A user may be granted the right of access to a view, but not to the base table (or tables), thus restricting the data that can be seen.
There can be many other aspects to consider when it comes to database security that a database administrator must consider such as SQL injection, leaked data, stolen backups and database inconsistencies. Research two different topics in regards to database security related to your organization and explain how you would, as a database administrator, deal with them.
In preparation for this assignment, it is important to understand the fundamental concepts of database security. Use the suggested resources or other resources you find to learn more about these topics.
Assignment Instructions
For this assignment, complete the following:
- Create a simple database or use an existing one to plan and design various roles and views on the data.
- Describe your strategy for at least three different roles based on the needs of your organization.
- Create the SQL code to create the three different roles.
- Explain the need to create at least one multi-table view and one filtered view (column or row).
- Create a VIEW composed of multiple tables of combined data.
- Create a VIEW composed of a SUBSET of the attributes and rows.
- Create SQL GRANT statements that grant the appropriate permissions for the roles created.
- Describe two different database security issues and explain how to resolve them.
Your completed assignment should include the following:
- The original database script.
- An explanation of the reasoning behind the three different roles.
- The SQL code to create the three different roles.
- An explanation of the two views to be created.
- The SQL code of the two views.
- The SQL GRANT statements.
- Demonstrated ability to use scripts in setting up roles and views and generating successful results. You will achieve this by providing screenshots of your steps.
- A description of two database security issues and the ways to resolve them.