Transcript Chapter 8 PowerPoint

Chapter 8
Is it Secure?
Copyright ©2014 Pearson Education, Inc.
Chapter8.1
Overview
• Security is essential for any database that will be put into
production.
• One way to begin thinking about security is to look at two terms:
• Authentication
• Authorization
Copyright ©2014 Pearson Education, Inc.
Chapter8.2
Authentication
• Authentication is the process of determining if the person is, in
fact, who he or she claims to be.
• This can be done in a variety of ways:
• Login name and password
• Certificate
• Biometrics
Copyright ©2014 Pearson Education, Inc.
Chapter8.3
Authorization
• Authorization is about “authorizing” a user to do things in the
database.
• It involves setting permissions on objects and data.
Copyright ©2014 Pearson Education, Inc.
Chapter8.4
SQL Server Authentication
• SQL Server has two primary ways of authenticating users:
• Windows authentication
• SQL Server authentication
Copyright ©2014 Pearson Education, Inc.
Chapter8.5
Windows Authentication
• In Windows Authentication, a windows or Active Directory
account is mapped to an SQL Server Account.
• The user logs into their Windows machine and accesses the SQL
Server through this account.
• This is the preferred method of authentication.
Copyright ©2014 Pearson Education, Inc.
Chapter8.6
SQL Server Authentication
• In SQL Server or mixed authentication, a user is given a login
name and a password for logging into the server.
• This is useful in environments where not every user has a
Windows account.
Copyright ©2014 Pearson Education, Inc.
Chapter8.7
Example: Creating an SQL Server Login
CREATE LOGIN StudentLogIn WITH PASSWORD=ʼp@ssw0rd1ʼ,
DEFAULT_DATABASE=TutorManagement
Copyright ©2014 Pearson Education, Inc.
Chapter8.8
Roles
• Roles are collections of permissions.
• Rather than try to assign and maintain individual user
permissions, users can be assigned to a role that provides a
common set of permissions.
• Roles provide a much more efficient and maintainable way of
controlling user access to the database.
• New roles can be created as needed and SQL Server provides a
set of built-in roles.
Copyright ©2014 Pearson Education, Inc.
Chapter8.9
Table of Built-in Roles
Database Role
db_accessadmin
db_backupoperator
Description
Can ALTER any User and create Schema
Grants the user to back up and restore the particular database
db_datareader
db_datawriter
db_ddladmin
db_denydatareader
Grants the user SELECT on all Tables and Views in the database
Grants the user INSERT, UPDATE, and DELETE permissions on all Tables and Views
Grants the ability to CREATE or ALTER any database object
Denies SELECT on all Tables and Views
db_denydatawriter
db_owner
db_securityadmin
public
Denies INSERT ,UPDATE, and DELETE on all Tables and Views
Grants ownership and full permissions on all database objects
Granted the ability to ALTER roles and CREATE Schema
Grants access to database but by default has no permissions on any objects. Every user is a member of
public as well as any other roles. The public role cannot be removed.
Copyright ©2014 Pearson Education, Inc.
Chapter8.10
Example: Creating a Role
USE TutorManagement
Go
CREATE ROLE StudentRole
Copyright ©2014 Pearson Education, Inc.
Chapter8.11
Schema
• Schema can be used to achieve results similar to roles.
• However, a role is a collection of permissions; a schema is a
collection of objects owned by a schema.
• A user can be assigned to a schema and then assigned
permissions on schema objects.
• When they log in, they will only see the objects in their schema.
Copyright ©2014 Pearson Education, Inc.
Chapter8.12
Analyzing Security Needs
• One way to analyze the security needs of a database is to look at
security requirements of each type of database user.
• You can analyze those needs in terms of specific permissions on
tables and objects.
Copyright ©2014 Pearson Education, Inc.
Chapter8.13
Analysis Example
Table name
Student
Tutor
SELECT
Course
StudentCourse
Ethnicity
Session
X
Request
RequestNote
X
X
INSERT
UPDATE
X
X
DELETE
Constraints
A public subset of tutor info
X*
X*
Copyright ©2014 Pearson Education, Inc.
*Only for own sessions
Chapter8.14
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 ©2014 Pearson Education, Inc.
Chapter8.15
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 ©2014 Pearson Education, Inc.
Chapter8.16
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 ©2014 Pearson Education, Inc.
Chapter8.17
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 ©2014 Pearson Education, Inc.
Chapter8.18
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 ©2014 Pearson Education, Inc.
Chapter8.19
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 ©2014 Pearson Education, Inc.
Chapter8.20
Backup Procedure Example
•
•
•
•
•
•
•
•
•
•
We will maintain 4 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.
Back up 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).
• If Westlake is closed, the employee is to take the disk home and deposit it when he or she
picks up the drives the next work day.
Copyright ©2014 Pearson Education, Inc.
Chapter8.21
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 ©2014 Pearson Education, Inc.
Chapter8.22
Views
• Views are essential 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 ©2014 Pearson Education, Inc.
Chapter8.23
Syntax for a View
CREATE VIEW <ViewName>
AS
<Select query>
Copyright ©2014 Pearson Education, Inc.
Chapter8.24
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 ©2014 Pearson Education, Inc.
Chapter8.25
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 ©2014 Pearson Education, Inc.
Chapter8.26
Stored Procedure Syntax
CREATE PROC <Procedure Name>
<Parameter list>
AS
<SQL statements>
Copyright ©2014 Pearson Education, Inc.
Chapter8.27
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 ©2014 Pearson Education, Inc.
Chapter8.28
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 ©2014 Pearson Education, Inc.
Chapter8.29
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 ©2014 Pearson Education, Inc.
Chapter8.30
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 ©2014 Pearson Education, Inc.
Chapter8.31
Variable Examples
DECLARE @CourseKey NCHAR(10)
SELECT @CourseKey=CourseKey
FROM [Session]
WHERE SessionDateKey=@SessionDateKey
AND SessionTimeKey=@SessionTimeKey
DECLARE @CurrentDate Date
SET @CurrentDate=GetDate()
Copyright ©2014 Pearson Education, Inc.
Chapter8.32
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 ©2014 Pearson Education, Inc.
Chapter8.33
If Example
IF EXISTS
(SELECT *
FROM student
WHERE studentKey=@studentKey)
BEGIN
SELECT studentLastName
FROM Student
WHERE Studentkey=@studentKey
END
Copyright ©2014 Pearson Education, Inc.
Chapter8.34
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 ©2014 Pearson Education, Inc.
Chapter8.35
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 ©2014 Pearson Education, Inc.
Chapter8.36
Big Data
• Big data refers to the recent attempts to utilize extremely large
stores of data, often of Exabyte scale, to glean as much
information as possible about current trends.
• Big data often uses mixed structured and unstructured data,
traditional databases, and also things like social media, emails,
usage logs, etc.
• The tools for analyzing this data are still being developed, but big
data is becoming more and more important as we go forward.
Copyright ©2014 Pearson Education, Inc.
Chapter8.37
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 ©2014 Pearson Education, Inc.
Chapter8.38
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 ©2014 Pearson Education, Inc.
Chapter8.39