Term Description
AS Used with stored procedures and views to mark the beginning of the body of the procedure or view.
BEGIN Marks the beginning of a block. Often used with other keywords such as BEGIN TRAN and BEGIN TRY.
CATCH With BEGIN and END, it marks a block to catch and handle any errors cast from code in a TRY block.
COMMIT With TRAN, it completes all the SQL statements in the current transaction and where necessary writes them to the database.
CREATE Used with an object type to create an instance of that object. For example, CREATE PROC, CREATE TABLE, and CREATE VIEW. (Once an object is created, it can be edited by using the keyword ALTER instead of CREATE.)
DECLARE Declares a variable. All SQL Server variables begin with @ and must be given a data type. For example, DECLARE @ StudentKey NCHAR(10).
DEFAULT_DATABASE Sets the default database for a login.
END Ends a block. Often used with other keywords such as END TRAN, END TRY, and END CATCH.
EXISTS Used with a subquery, it returns a Boolean. True if the query returns any values, false if not.
GRANT With ON and an object name, grants a permission to a user or role.
IF Tests an expression to see if it is true or false (Any expression must return a Boolean.) If it is true, one branch of code can be executed, if it is false, another branch.
LOGIN With CREATE, it adds a login to the Server.
PASSWORD Sets the password for an SQL Server login.
PROCEDURE (PROC) An object that stores a set of related SQL code that is meant to be executed as one process. It can be used to safely handle user input and output. A user can be granted permissions to execute a stored procedure without being given permissions on the underlying tables.
ROLE With CREATE, it adds a role to the database.
ROLLBACK Used with TRANSACTION, it undoes any statements contained within that TRANSACTION.
TRANSACTION (TRAN) Used with BEGIN, a TRANSACTION keeps all the SQL statements within; the TRANSACTION is suspense until they are all committed to the database or rolled back.
TRY With BEGIN and CATCH, starts a block of code to be run. If any command generates an error, the execution will immediately jump to the CATCH block for processing.
USER With CREATE, adds a User to a particular database.
VIEW A VIEW is a stored query or filter. A VIEW doesn’t contain any data, but filters it. The idea is to create
“views” of the data that correspond to how particular sets of users interact with the data in the database.
WITH Sets Properties on an object such as a LOGIN.
Practices
1. Review the pizza database we built in Chapter 6 and queried in Chapter 7 . Identify the users of the database, and deter-mine what kind of access to the tables each of them needs.
2. Develop a threat analysis for the pizza company database.
3. Create roles for the various types of users in the pizza database.
4. Create a SQL Server login for a user, and assign the user to a role.
5. Create a view for one of the roles and grant permission to select from the view to one or more users.
6. Assume you are working for a small bookstore. It has a da-tabase that keeps track of all the inventory and all the sales and trades with customers. This bookstore also maintains an online presence with a Web site, where users can browse the catalog and purchase books using second-party
software to process the payment. There is only one store and they are located in the downtown area of a city known for occasional severe earthquakes. Create a disaster recov-ery plan for this company.
6–11 Look at this stored procedure and answer the
BEGIN TRAN
The apartment managers at Wild Wood like what you have done so far, but as the database takes shape, they have begun to worry about security. The tenant information should not be accessible to just anyone. And they would like to keep the financial information internal, and not let outsiders or other companies see the details of their operation.
1. Create tables of the data access needs of your users.
2. Create a security plan that includes authentication and authorization and general policies and procedures. Consider the use of roles, stored procedures, views, and other tools.
3. Documentation: Document and define all the aspects of your plan.
4. Create a preliminary threat analysis.
5. Make a preliminary disaster management plan.
6. Create a view of the data that is tailored to the needs of one of your uses.
7. For extra-credit, create a stored procedure that executes one of the basic activities for your database (making a rent payment, for instance, or a maintenance request).
VINCE’S VINYL
Having shown Vince your work so far, you broach the topic of security. At first, Vince doesn’t see much need for security mea-sures, but you point out a few areas where that should be consid-ered. For one thing, Vince probably doesn’t want to share his list of interested customers. That is valuable information in itself, and his customers will have an expectation of privacy.
Additionally, the day-to-day financial information concerning sales and purchases is probably best not available for general public perusal. You also point out that it is important that Vince be able to trust his data. He needs to know that no one has acci-dentally, or on purpose, messed up his inventory or sales data.
1. Create tables of the data access needs of Vince’s users.
2. Create a security plan that includes authentication and authorization and general policies and procedures.
Consider the use of roles, stored procedures, views, and other tools.
3. Documentation: Document and define all the aspects of your plan.
4. Create a preliminary threat analysis.
5. Make a preliminary disaster management plan.
6. Create a view of the data that is tailored to the needs of one of your uses.
7. For extra-credit, create a stored procedure that executes one of the basic activities for your database (purchasing an album, for instance, or recording a customer request).
GRANDFIELD COLLEGE
As with any database, data integrity is important to the software database at Granfield College. If the data are audited, they have to show that they know what software they have, how it is licensed, and on which machines it is installed. Accident and error are the most likely threats to their data integrity, but it is always possible that someone might try to purposely disrupt their data.
1. Create tables of the data access needs of your users.
2. Create a security plan that includes authentication and authorization and general policies and procedures.
Consider the use of roles, stored procedures, views, and other tools.
3. Documentation: Document and define all the aspects of your plan.
4. Create a preliminary threat analysis.
5. Make a preliminary disaster management plan.
6. Create a view of the data that is tailored to the needs of one of your uses.
7. For extra-credit, create a stored procedure that executes one of the basic activities for your database (installing a piece of software, for instance, or processing a software request).
WESTLAKE RESEARCH HOSPITAL
Security has always been a part of the WestLake Hospital’s data-base. In a double-blind study, it is absolutely essential that no one tampers with the data. Also, patient confidentiality and the sensitive nature of the study require that the patients’ records
and the records of their sessions with the doctors be kept abso-lutely private and secure. The researchers are anxious to see your plan for securing the data.
1. Create tables of the data access needs of your users.
2. Create a security plan that includes authentication and authorization and general policies and procedures.
Consider the use of roles, stored procedures, views, and other tools.
3. Documentation: Document and define all the aspects of your plan.
4. Create a preliminary threat analysis.
5. Make a preliminary disaster management plan.
6. Create a view of the data that is tailored to the needs of one of your uses.
7. For extra-credit, create a stored procedure that executes one of the basic activities for your database (making an appointment, for instance, or letting patients see some of their doctors’ session notes on them.)
SUGGESTIONS FOR SCENARIOS
Security is difficult, and each of these scenarios has very differ-ent security needs. First think about the authdiffer-entication. Who is going to use the database? Are there many potential users or just a few? If there are many, you might consider whether they naturally fall into just a few roles. If there are only very few, you might just use individual logins.
Next, I would think about the different users or groups of users. What permissions do they need on the tables to do their work? Then look at the threats. Where are the potential areas for attack, but also where are the potential areas for mistakes and bad data entry? Consider whether creating stored procedures and views could lessen those areas of vulnerability.
Finally, identify the ways the data could be harmed or lost.
What policies and procedures would help minimize that poten-tial loss? These are what make up your disaster plan.