IT 390 Business Database Administration

1. IT 390 Business Database Administration Unit 8: Security Management and the Multi-user Environment

2. 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.

3. 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

4. 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

5. 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.

6. 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?

7. 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

8. 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

9. 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.

10. 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

11. Transaction Isolation Level

12. 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

13. 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

14. Errors Introduced WithoutAtomic Transaction

15. Errors Prevented WithAtomic Transaction

16. 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.

17. Concurrent Transaction Processing

18. Lost-Update Problem

19. Resource Locking • Resource locking prevents multiple applications from obtaining copies of the same record when the record is about to be changed

20. 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

21. Concurrent Processingwith Explicit Locks

22. 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.

23. 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.

24. 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.

25. Deadlock

26. 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

27. Optimistic Locking

28. Pessimistic Locking

29. 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 Declaring Lock Characteristics

30. Marking Transaction Boundaries

31. Differentiate between the Windows NT/2000 authentication mode and Mixed security mode. Can you… ?

32. 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 Authentication in SQL Server 2000

33. 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 Planning and Monitoring Security

34. The SQL Server 2000 permissions can exist in any of the following modes: Grant Deny Revoke Planning and Monitoring Security (cont.)

35. 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.

36. 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.

39. Are the following SQL syntax correct? Syntax 1: Syntax 2: GRANT CONNECTION TO Joe REVOKE CONNECT FROM Matthew Activity

40. Syntax 1: The syntax is wrong and the correct form is: Syntax 2: The syntax is correct. Solution GRANT CONNECT TO Joe

41. 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. State Whether True or False

42. Statement 1 is True. Statement 2 is True. Solutions

43. Concurrency involves using the most updated data in a networked environment. Concurrency

44. 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? Activity

45. The lost update concurrency issue. Solution

46. 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? Activity

47. The incorrect summary problem. Solution

48. 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? Activity

49. The uncommitted dependency problem. Solution

50. The methods used for eradication of concurrency issues are known as concurrency control techniques. Concurrency Control Technique