Transcript transaction - Pearson Education

IT 390 Business Database Administration
Unit 8:
Security Management and the Multi-user
Environment
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 1
Objectives
•
•
•
•
•
•
Explain the importance of security in SQL Server 2000.
Identify basic database security features and roles.
Describe the SQL Server 2000 security models.
Plan and monitor security in SQL Server 2000.
Implement Authentication on a Microsoft SQL Server
Database.
Explain authentication modes and mechanisms in SQL
Server 2000.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 2
Security in SQL Server 2000
•
Security controls access the system resources, such as
computer systems and databases.
•
SQL Server 2000 provides a reliable interface by
authorizing users to use the system resources.
•
Provide SQL Server 2000 security at the following
levels:

Physical

Manual
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 3
SQL Server 2000 Security Model
•
Provides protection from the hackers and
safeguards the database against
unauthorized access.
•
Ensure security through the following:

Roles

Permissions

Authentication mechanisms

Authentication modes
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 4
User Logins, Groups, and Roles
•
The SQL Server 2000 logins let you authorize users to access
the database by specifying valid usernames and passwords.
•
Groups are a collection of database members who are given
permissions to use the SQL Server 2000 database.
•
Roles group users according to their database use.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 5
Activity
Thomas, the DBA of a company
wants to differentiate authorized
users into readers, writers, and
modifiers of the SQL Server 2000
database. Which part of the security
model would enable him to do this
task?
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 6
Solution
Thomas, the DBA of a company wants to
differentiate authorized users into readers,
writers, and modifiers of the SQL Server
2000 database. Which part of the security
model would enable him to do this task?
A Role
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 7
ACID Transactions
• Acronym ACID transaction is one that is
Atomic, Consistent, Isolated, and
Durable
• Atomic means either all or none of the
database actions occur
• Durable means database committed
changes are permanent
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 8
ACID Transactions
• Consistency means either statement level or
transaction level consistency.
 Statement level consistency: each statement
independently processes rows consistently
 Transaction level consistency: all rows
impacted by either of the SQL statements are
protected from changes during the entire
transaction.
• With transaction level consistency, a
transaction may not see its own changes.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 9
ACID Transactions
• Isolation means application programmers are
able to declare the type of isolation level and to
have the DBMS manage locks so as to achieve
that level of isolation
• SQL-92 defines four transaction isolation
levels:
 Read uncommitted
 Read committed
 Repeatable read
 Serializable
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 10
Transaction Isolation Level
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 11
Concurrency Control
• Concurrency control ensures that one
user’s work does not inappropriately
influence another user’s work


No single concurrency control technique is ideal for
all circumstances
Trade-offs need to be made between level of
protection and throughput
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 12
Atomic Transactions
• A transaction, or logical unit of work
(LUW), is a series of actions taken against
the database that occurs as an atomic unit

Either all actions in a transaction occur or do none of
them
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 13
Errors Introduced Without
Atomic Transaction
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 14
Errors Prevented With
Atomic Transaction
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 15
Concurrent Transaction
• Concurrent transactions refer to two or more
transactions that appear to users as they are
being processed against a database at the same
time.
• In reality, CPU can execute only one instruction at
a time.
 Transactions are interleaved meaning that
the operating system quickly switches CPU
services among tasks so that some portion of
each of them is carried out in a given interval.
• Concurrency problems: lost update and
inconsistent reads.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 16
Concurrent Transaction Processing
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 17
Lost-Update Problem
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 18
Resource Locking
• Resource locking prevents multiple
applications from obtaining copies of the
same record when the record is about to
be changed
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 19
Lock Terminology
•
•
•
•
•
Implicit locks are locks placed by the DBMS
Explicit locks are issued by the application program
Lock granularity refers to size of a locked resource
 Rows, page, table, and database level
Large granularity is easy to manage but frequently causes
conflicts
Types of lock
 An exclusive lock prohibits other users from reading the
locked resource
 A shared lock allows other users to read the locked resource,
but they cannot update it
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 20
Concurrent Processing
with Explicit Locks
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 21
Serializable Transactions
• Serializable transactions refer to two
transactions that run concurrently and
generate results that are consistent with
the results that would have occurred if
they had run separately.
• Two-phased locking is one of the
techniques used to achieve
serializability.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 22
Two-phased Locking
• Two-phased locking
 Transactions are allowed to obtain locks as
necessary (growing phase).
 Once the first lock is released (shrinking
phase), no other lock can be obtained.
• A special case of two-phased locking.
 Locks are obtained throughout the transaction.
 No lock is released until the COMMIT or
ROLLBACK command is issued.
 This strategy is more restrictive but easier to
implement than two-phased locking.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 23
Deadlock
• Deadlock, or the deadly embrace, occurs when two
transactions are each waiting on a resource that the other
transaction holds.
• Preventing deadlock
 Allows users to issue all lock requests at one time.
 Requires all application programs to lock resources in
the same order.
• Breaking deadlock
 Almost every DBMS has algorithms for detecting
deadlock.
 When deadlock occurs, DBMS aborts one of the
transactions and rollbacks partially completed work.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 24
Deadlock
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 25
Optimistic versus Pessimistic Locking
•
•
•
Optimistic locking assumes that no transaction conflict will occur:
 DBMS processes a transaction; checks whether conflict
occurred:
• If not, the transaction is finished
• If so, the transaction is repeated until there is no conflict
Pessimistic locking assumes that conflict will occur:
 Locks are issued before a transaction is processed, and then
the locks are released
Optimistic locking is preferred for the Internet and for many
intranet applications
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 26
Optimistic Locking
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 27
Pessimistic Locking
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 28
Declaring Lock Characteristics
• Most application programs do not explicitly declare
locks due to its complication
• Instead, they mark transaction boundaries and
declare locking behavior they want the DBMS to use
 Transaction boundary markers: BEGIN,
COMMIT, and ROLLBACK TRANSACTION
• Advantage
 If the locking behavior needs to be changed,
only the lock declaration need be changed, not
the application program
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 29
Marking Transaction
Boundaries
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 30
Can you… ?
•
Differentiate between the Windows NT/2000
authentication mode and Mixed security mode.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 31
Authentication in SQL Server 2000
•
The process of validation of the SQL
Server 2000 database users file by
these two modes of authentication:

Windows NT/2000 authentication
mode

Mixed security mode
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 32
Planning and Monitoring Security
•
Security planning deals with the decisions by
which the users are permitted to access a part of
the database.
•
SQL Server 2000 provides two types of
permissions:

Statement permissions

Object permissions
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 33
Planning and Monitoring Security (cont.)
• The SQL Server 2000 permissions can exist
in any of the following modes:

Grant

Deny

Revoke
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 34
Establishing a Security Scheme
You can implement a security scheme using the following SQL
statements:
• GRANT: You specify the following options in a GRANT
statement:
 The list of privileges to be granted
 The name of the table or views to which the privileges
apply
 The User ID to which the privileges are granted
• REVOKE: Similar to granting privileges, you can
revoke all privileges on a table from a user. The
cascading effect of the REVOKE statement varies with
the kind of privilege you are working.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 35
Types of Privileges
• System-level privileges: System-level privileges
are applied to a particular user account and may
include commands to create a table or a view,
alter, drop, and modify a table, or to select
specific data from a table.
• Object-level privileges: Object-level privileges are
granted on a table or a view that the user must be
allowed to access.
• In SQL, the following privileges can be specified
for each table or view Object:
SELECT, INSERT, DELETE, and UPDATE.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 36
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 37
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 38
Activity
• Are the following SQL syntax correct?
 Syntax 1:
GRANT CONNECTION TO Joe

Syntax 2:
REVOKE CONNECT FROM Matthew
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 39
Solution

Syntax 1: The syntax is wrong and the
correct
form is:
GRANT CONNECT TO Joe

Syntax 2: The syntax is correct.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 40
State Whether True or False
In a nested transaction, the outer most
transaction needs to be committed so that the
complete structure is saved.
Save Points are last good known committed
flags in the transaction log, to which a
transaction can be rolled back.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 41
Solutions
• Statement 1 is True.
• Statement 2 is True.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 42
Concurrency
• Concurrency involves using the most
updated data in a networked environment.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 43
Activity
• Identify the concurrency issue.

The business unit of Ethnic Blends Inc. in
Tokyo sells the last remaining stock of a
famous designer. Due to a technical flaw in
the network, the unit at Paris could not
update the same transaction. It receives a
request for the same product and processes
the new transaction. Which concurrency
issue has taken place?
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 44
Solution
• The lost update concurrency issue.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 45
Activity
• Identify the concurrency issue.

The Finance department is updating the
annual packages of the employees of Ethnic
Blends Inc. The appraisal is part of the
annual bonus agreement. At the same time
the MIS department tries to retrieve the
average annual package of all the
departments. This is done to prepare the
annual reports. Which concurrency issue
takes place?
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 46
Solution
• The incorrect summary problem.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 47
Activity
• Identify the concurrency issue.

The finance department of Ethnic Blends
have finally updated the salary structure of
employees of all departments. By mistake,
the Sales department updating does not get
committed. The Tax department is now
calculating the return taxes and the Sales
department figures are giving contradictory
results. Which concurrency issue has taken
place?
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 48
Solution
• The uncommitted dependency problem.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 49
Concurrency Control Technique
• The methods used for eradication of
concurrency issues are known as
concurrency control techniques.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 50
Activity
• Identify the concurrency control technique
used.

The EmployeeID details is unique for each
employee. In cases where employee join or
leave Ethnic Blends, the database
modifications are performed with respect to the
EmployeeID. For addition or removing an
Employee record, exclusive rights need to be
assigned to a transaction. Which concurrency
control technique should be used?
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 51
Solution
• Lock-based protocols.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 52
Activity
• Identify the type of failure.

During a commit process for an online
transaction, the billing department system
fails to bill the customer’s account. Due to
this, the purchasing process does not
complete successfully. What could be the
type of failure if given that the network and
peripherals were error-free?
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 53
Solution
• Transaction failure
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 54
Activity
Harry, the DBA of a company wants to deny a
Windows 2000 group to connect to SQL Server
2000 and grant a user account, on the current
database, for an SQL Server 2000 login.
Which system stored procedures should he
use?
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 55
Solution
Harry, the DBA of a company wants to deny a
Windows 2000 group to connect to SQL Server
2000 and grant a user account, on the current
database, for an SQL Server 2000 login.
Which system stored procedures should he
use?
sp_grantdbaccess
sp_denylogin
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 56
Summary

Understanding and maintaining security on a database
requires a wide variety of skills.

A DBA should have a good grasp on basic transaction
and security guidelines and what kinds of things can go
wrong without that understanding and implementation.

Some of the commands an Administrator must be
familiar with in-depth are GRANT, DENY and
REVOKE.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 57
Summary
 Did
you understand the key points
from the Lesson?
 Do
you have any questions?
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 8 Slide 58