Lab– Data and Database Administration

Overview

In this lab, we will practice using SQL to manage transactions and security for a database. We will use the following concepts from class:

  • Views / Stored Procedures
  • Transaction Management commands
  • GRANT and REVOKE commands

Learning Objectives

Upon completion of this learning unit you should be able to:

  • Describe the importance of data and database management
  • Describe database transactions are and how they are managed
  • Explain concurrency control and locking methods and what role they have in ensuring database integrity
  • Describe database backup and recovery and the role they play in database integrity
  • Describe the role and responsibilities of a Database Administrator and tools used to perform required tasks
  • Apply transaction management principles to solve problems

Lab Goals

This lab consists of 2 parts:

  1. In the first part you will use SQL transactions.
  2. In the second part, you will play round with SQL security.

You will have to hand in pieces from parts 1 and 2 on this week’s learning assessment.

What you will need to begin

By now you should be proficient at logging on to your hosted SQL Server account. If you aren’t I suggest practice, practice, practice!

  1. IMPORTANT: Finish the previous lab before starting this one.
  2. Connect to your SQL Server instance.

Part 1: Just playin’ with transactions

In this first part we will do some demos so that we can understand how transactions work. Once you get this down pat, then you will attempt to write the transactions for the Fudgebank database in part 3.

  1. Create the demo table and populate it with some sample data:
  2. Let’s see how implicittransactions work. When using implicit transactions, there is no need to use the begin tran statement to initiate a transaction. The transaction begins as soon as the first DML statement is encountered. Here’s a sample implicit transaction that adds three more colors:

  3. Next let’s see a rollback in action by deleting all the rows from the table and then undoing the transaction. Of course, to witness the transaction rolling back we need to execute another select statement on the table after the rollback, which in turn is ANOTHER TRANSACTION that we must commit. (This is, in my opinion, why implicit transactions stink to high heaven.)
  4. You can see how messy and counter-intuitive this is but what is the alternative to implicit transactions? Well, we’ve seen that you can’t undo a statement if you don’t nest it in a transaction and SQL server’s default is AUTOCOMMIT mode so that’s not the answer. The answer is EXPLICIT transactions. Everything between the begin tran and the commit / rollback is treated as one logical unit of work.
  5. At this point you might be asking what exactly is the use-case for an SQL statement like rollback? After all it makes sense to want to undo you work if and when you make a mistake (like deleting all the rows from a table), but what if the end-user is doing the work? How can we determine if the transaction did or did not occur as expected and should be either committed or rolled back? Well, the answer is not so easy and really depends on the type of work we’re doing.
    For example if you’re trying to determine whether or not a two rows of data were inserted TOGETHER as expected, you could write a transaction like this:
  6. But if we run the same code but try to insert ‘black’ and ‘gray’ the data should not insert because while ‘gray’ will insert fine, black is already in the table and will not. And because we want the insertion of BOTH COLORS to succeed or fail AS A LOGICAL UNIT OF WORK, we get the desired result.
  7. As you can probably judge from the way the SQL script is written this code is just aching to be nested in a stored procedure. Creating a stored procedure does not change the game at all when it comes to transaction, and you still need explicit transactions to guarantee both colors get inserted. The big advantage of stored procedures is the capability of hiding the complexity of what’s going on to the user.
  8. Finally executing our transaction-safe procedure is as easy as calling it:

Questions on Part 1

  1. What is the default transactional behavior of SQL server? How does one override that behavior?
  2. Explain the difference between an explicit and implicit transaction?
  3. How do you create an implicit transaction in SQL?
  4. How do you create an explicit transaction in SQL?
  5. Describe a use-case for transactions as a student learning SQL
  6. Describe a use-case for transactions in the real-world.
  7. In terms of transactions - What does Auto-commit mean?
  8. Does nesting a group of DML statements inside a stored procedure constitute an explicit transaction? Explain your answer.

Part 2: SQL Security in practice

In this part you will learn a little bit about how security is implemented on SQL server. To accomplish this without completely messing up access to your own SQL server catalog, you will experiment with a different logon that exists on the SQL server your instructor has created for you guestuser.

  1. First let’s give the guestuser account access to our database:
  2. Next connect to your database as guestuser. From the Object explorer, choose Connect  Database Engine. The password for guestuser is SU2orange!

    Important: after you connect as the guestuser make sure to select your database from the list of available databases.
    You’ll probably want to flip back and forth between the SQL tab where you’re logged in as guestuser and the tab where you’re logged in as yourself from this point forward.
  3. As the guest user: Open up a query window and type the following:
  4. As yourself: Let’s give the guest user permissions to read from fudgemart employees.

    In this case, the securable is the table fudgemart employees and the permission is read (select).
  5. As the guest user: Open up a query window and type the following:

    You should now see data!!!!
  6. As yourself: Let’s give the guest user permissions to read and insert from fudgemart employees:
  7. As the guest user: Try to insert a row. Does it work?
  8. One of the cool things you can do is deny a user access to the table, but permit them to use views and stored procedures.
    As yourself: type:
  9. As the guest user: Try this:

    Which one works? 
  10. When you’re all done, be sure to remove the user from the database so that others cannot access your database with this account:

Questions on Part 2

  1. What does the create user statement do? Is that DDL? DML? DTL? Or SML?
  2. Which types of objects are securable with GRANT and DENY statements?
  3. Describe a use case by which you would not want a user reading the table directly but rather using a view instead.
  4. Describe a use case by which you would not want a user updating data in the table directly but rather using a stored procedure instead?
  5. Write SQL to deny the guest user permissions to delete from the colors table you created in part two.
  6. Write SQL to allow the guest user the ability to run an SQL View of your choosing
  7. Write SQL to give the guestuser permissions to insert and read data from the colors table you created in part two.
  8. Write SQL to give the guestuser permissions to execute the p_add_two_colors stored procedure you created in part two.

1 / 14