Transcript Unit 9

PT2520 Unit 9: Database Security II
Is It Secure?
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter8.1
Analyzing Security Needs
• One way to analyze the security needs of
a database is to look at the security
requirements of each type of database
user.
• You can analyze those needs in terms of
specific permissions on tables and objects.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter8.2
Analysis Example
Table name
Student
Tutor
SELECT
Course
StudentCourse
Ethnicity
Session
X
Request
RequestNote
X
X
INSERT
UPDATE
DELETE Constraints
X
X
A public subset of
tutor info
X*
X*
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
*Only for own
sessions
Chapter8.3
Threat Analysis
• Threat analysis involves identifying all the
ways a database can be harmed and then
finding strategies to mitigate those
threats.
• Databases can also be damaged by
accidental actions.
• Analyzing threats is a complex and
ongoing task.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter8.4
Threat Analysis Example
Role
Student
Threat
Description
SELECT
See private information of other students
INSERT
False or inaccurate information in Student table
UPDATE False or inaccurate information in the Session table,
removing other students from scheduled sessions
DELETE
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter8.5
Disaster Recovery
• Disaster recovery means planning for the
worst.
• Disasters can be manmade, such as an
attack by a hacker, or a major mistake by
an administrator.
• Disasters can also be natural. Fires, floods,
and earthquakes can destroy data.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter8.6
Disaster Recovery Plan
• A disaster recovery plan is a plan for how
to recover data and its availability after
various possible disasters.
• A disaster recovery plan consists of
policies and procedures for disaster
prevention and recovery.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter8.7
Policies
• Policies are rules for how to do things.
• For instance, a business could have a
rule that all databases are backed up
twice a day.
• Another policy could be that all backups
are kept off-site in some secure place.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter8.8
Procedures
• Procedures are step-by-step instructions
for how to do things.
• In a disaster plan, procedures are the
step-by-step instructions for
implementing a policy.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter8.9
Backup Procedure Example
•
•
•
•
•
•
•
•
•
•
•
Maintain four portable hard drives.
Each morning, retrieve the two drives with the oldest backup date.
Perform a full database backup to one of the drives at 11:00 AM.
Backup the log files to the hard drive.
Record the current date and time of the backup on the hard disk.
Send an employee to deposit the hard drive in a safety deposit box
at Westlake Security Co.
At closing, around 5:00 PM, do a full backup to the second hard
disk.
Back up the log files to the hard disk.
Record the date and time on the hard disk.
Send an employee to deposit the hard drive in a safety deposit box
at Westlake Security Co. (Westlake is open until 7 PM.)
If Westlake is closed, the employee is to take the disk home and
deposit it when he or she drives in to work the next work day.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter8.10
Finding Solutions
• Implementing effective security measures
can be very complex.
• You can use a mixture of schema roles
and permissions.
• One approach is to build a layer of
views and stored procedures to manage
all user access.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter8.11
Views
• Views are essentially stored queries.
• Ideally, each view corresponds to a
particular “view” that a user has of
the data.
• Views can be used to hide the underlying
structure of the database.
• Views are accessed just like tables.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter8.12
Syntax for a View
CREATE VIEW <ViewName>
AS
<Select query>
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter8.13
View Example
CREATE VIEW vw_Sessions
AS
SELECT TutorLastName AS [Tutor],
StudentKey AS [Student],
SessionDateKey AS [Date],
SessionTimeKey AS [Time],
CourseKey AS [Course]
FROM Tutor t
INNER JOIN [Session] s
ON t.TutorKey=s.TutorKey
WHERE SessionDateKey >=GetDate()
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter8.14
Stored Procedures
• Stored procedures consist of one or more
SQL commands.
• They can take parameters from the user.
• They allow all the commands to be
executed as a unit.
• They allow error checking and validation
to help ensure a safe transaction.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter8.15
Stored Procedure Syntax
CREATE PROC <Procedure Name>
<Parameter list>
AS
<SQL statements>
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter8.16
Stored Procedure Example
CREATE PROCEDURE usp_StudentLogIn
@studentKey nchar(10)
AS
IF EXISTS
(SELECT *
FROM student
WHERE studentKey=@studentKey)
BEGIN
SELECT studentLastName
FROM Student
WHERE Studentkey=@studentKey
END
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter8.17
A Few Stored Procedure Notes
• The following slides discuss a few of the
features of stored procedures, specifically:
– Parameters
– Variables
– If/else and blocks
– Transactions and try/catch blocks
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter8.18
Parameters
• A parameter is a value passed to the
stored procedure from the user.
• Parameters are listed after the CREATE
Statement and before the AS.
• All parameters start with the @ symbol
and must be given a data type:
• @studentKey nchar(10)
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter8.19
Variables
• Variables are declared after the AS
keyword and must be assigned values
internally.
• Variables are declared with the DECLARE
keyword.
• Variables can be assigned values with the
SET or SELECT keywords.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter8.20
Variable Examples
DECLARE @CourseKey NCHAR(10)
SELECT @CourseKey=CourseKey
FROM [Session]
WHERE SessionDateKey=@SessionDateKey
AND SessionTimeKey=@SessionTimeKey
DECLARE @CurrentDate Date
SET @CurrentDate=GetDate()
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter8.21
IF, ELSE, BEGIN, END
• It is possible to select among possibilities
by using the IF and ELSE keywords.
• IF sets up the condition and what to do if
the condition is true.
• ELSE describes what to do if the condition
is false.
• BEGIN is used to mark the start of an IF or
ELSE block.
• END is used to mark the end of the block.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter8.22
If Example
IF EXISTS
(SELECT *
FROM student
WHERE studentKey=@studentKey)
BEGIN
SELECT studentLastName
FROM Student
WHERE Studentkey=@studentKey
END
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter8.23
TRY CATCH TRANS
• TRY CATCH blocks can be used with
transactions to catch any errors.
• The TRY tests the code for errors.
• If there are no errors, the statements are
committed to the database.
• If there are errors, the execution will go to
the CATCH block and roll back the
transaction.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter8.24
TRY CATCH Example
BEGIN TRAN
BEGIN TRY
UPDATE [Session]
SET StudentKey=@StudentKey
WHERE
SessionDateKey=@SessionDateKey
AND SessionTimeKey=@SessionTimeKey
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter8.25
Documentation
• It is crucial to document the security setup.
• Authentication types and policies should be
spelled out.
• All roles and schema should be described.
• All stored procedures and views should be
described.
• Disaster plans and all policies and procedures
should be documented and readily available.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
Chapter8.26
All rights reserved. No part of this publication may be reproduced, stored in a
retrieval system, or transmitted, in any form or by any means, electronic,
mechanical, photocopying, recording, or otherwise, without the prior written
permission of the publisher. Printed in the United States of America.
Copyright © 2012 Pearson Education, Inc.
Publishing as Prentice Hall