1 / 47

Chapter Eight

Chapter Eight. Security in SQL Server 2000. Objectives. Understand the security architecture of SQL Server 2000 Manage logins, users and roles Manage permissions Control access with database objects and ownership chains Audit SQL Server 2000. Security Architecture in SQL Server 2000.

hyacinth-patton
Download Presentation

Chapter Eight

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter Eight Security in SQL Server 2000

  2. Objectives • Understand the security architecture of SQL Server 2000 • Manage logins, users and roles • Manage permissions • Control access with database objects and ownership chains • Audit SQL Server 2000

  3. Security Architecture in SQL Server 2000 • Authentication • First stage of security • Identifies users based on login information they provide • Only verifies that users can connect to a SQL Server 2000 instance • Does not provide access to databases and their objects • Authorization • Second stage of security • Occurs when database permissions are checked to determine which actions a particular user can perform within a database

  4. Security Architecture in SQL Server 2000 • Logins provide access to an instance and are, in turn, mapped to database users to provide access to various databases housed within the instance Figure 8-1: Authentication and authorization through logins and database users

  5. Security Architecture in SQL Server 2000 Figure 8-2: Network security mapped to SQL Server security

  6. Logins • Authentication is implemented using logins in SQL Server 2000 • Logins • SQL Server 2000 object that provides connection access to an instance of SQL Server 2000 (authentication) • Logins can be based on Windows users and groups defined natively in SQL Server 2000

  7. Authentication Modes • A SQL Server 2000 instance can run in Windows Authentication Mode and Mixed Authentication Mode • Windows Authentication Mode allows users to connect to SQL Server 2000 instances using their Windows user account • Mixed Authentication Mode allows both Windows accounts and SQL Server logins to be used to connect to instances

  8. Windows Authentication Mode • When using Windows authentication, SQL Server calls out to the Windows domain to validate the account • When a user tries to connect to an instance of SQL Server 2000 without specifying a user name or password, Windows Authentication Mode is used • If the Windows credentials of the user are flagged as a valid login for a SQL Server 2000, then the user is allowed to connect to the instance

  9. Windows Authentication Mode • Along with limiting administration of a user to a single location (within a Windows domain), Windows authentication provides the following benefits: • Secure validation of credentials through Windows and encryption of passwords passed over the network • Windows password requirements • Automatic locking out of accounts that repeatedly fail to connect • Native auditing capabilities of Windows accounts

  10. Mixed Authentication Mode • In Mixed Authentication Mode, either a Windows-based account or a SQL Server 2000-based login can be used to connect to an instance • Mixed Authentication Mode is provided for backward compatibility with older applications designed to utilize SQL Server-based logins • Mixed Authentication Mode is also necessary for situations where users connecting to an instance of SQL Server 2000 do not have a Windows domain account

  11. Changing Authentication Mode of a SQL Server 2000 Instance • Once an authentication mode is specified for a SQL Server 2000 instance, it can be modified easily through Enterprise Manager Figure 8-3: Configuring authentication mode for an instance

  12. Managing Logins in SQL Server 2000 • To manage login information, expand a registered server in Enterprise Manager, expand the Security folder and click the Logins item Figure 8-4: Default logins in Enterprise Manager

  13. Creating Logins with Enterprise Manager Figure 8-5: SQL Server Login Properties screen

  14. Managing Logins with T-SQL Statements • Windows-based logins • The sp_grantlogin system-stored procedure is used to add Windows users and groups to a SQL Server 2000 instance • The sp_denylogin system-stored procedure is used to deny access to a particular Windows user or group, without removing its SQL Server login entries • The sp_revokelogin system-stored procedure is used to remove a login for a Windows user or group

  15. Managing Logins with T-SQL Statements • SQL Server 2000-based logins • The system-stored procedures used to add and remove logins for SQL Server authentication are: • The sp_addlogin system-stored procedure • The sp_droplogin system-stored procedure

  16. Other stored procedures for working with logins • As seen in the Enterprise Manager interface for creating new logins, both a default database and default language can be set for each new or existing login • The sp_defaultdb and sp_defaultlanguage stored procedures are used to specify a default database and language for a login

  17. Database Users • Database users • Individual accounts stored within each database that control access to database objects through permissions • Database users are mapped to Windows users, Windows groups and SQL Server logins

  18. Roles • Roles • Allow you to group database users (and even logins) who perform similar functions and to apply permissions for the group instead of the individual users • Allow both Windows and SQL Server logins to be grouped together, which eases administration in diverse network environments

  19. Fixed Server Roles • Fixed Server Roles • Associated with Windows and SQL Server logins defined for an instance • Used to provide special permissions, like configuring instance-wide settings and creating databases that cannot be explicitly provided to individual logins

  20. Fixed Server Roles Table 8-1: Fixed server roles

  21. Fixed Database Roles • There are several predefined roles in each database that provide sets of permissions for the database users who belongs to them Figure 8-6: Adding logins to fixed server roles

  22. Fixed Database Roles Table 8-2: Fixed database roles

  23. Fixed Database Roles Figure 8-7: Creating custom database roles

  24. Application Roles • Application role • Special roles that do not contain users • Used by applications to connect to SQL Server 2000 databases • Used primarily when an application is managing user authentication itself but still requires access to the database

  25. Creating Database Users Figure 8-8: Creating database users with Enterprise Manager

  26. DBO and the Guest Database User • Every database in an instance of SQL Server 2000 can have two special users: • Database owner • Special user that has permissions to perform all database activities • Guest user • Special account that allows database access to a login without a mapped database user

  27. Permissions • The three types of permissions that can be used to control user access within a database: • Object permissions • Allow access/modification to data as well as execution of stored procedures and user-defined functions • Statement permissions • Allow access to certain object-creation T-SQL statements like CREATE DATABASE and CREATE TABLE • Implied permissions • Permissions only available through predefined roles

  28. Object Permissions • Object permissions • Allow users to work with objects and they can be granted to users or roles for database objects • Allow users to insert, update, delete and select data from tables and views

  29. Statement Permissions • Statement permissions • Used to allow users to create databases and database objects • Access to the following statements are controlled through the statement permissions • CREATE DATABASE • BACKUP DATABASE • BACKUP LOG • CREATE TABLE

  30. Statement Permissions • Access to the following statements are controlled through the statement permissions (cont.): • CREATE VIEW • CREATE RULE • CREATE DEFAULT • CREATE PROCEDURE • CREATE FUNCTION

  31. Implied Permissions • Implied permissions • Special privileges that are provided through membership to a user-defined role • These are often permissions that cannot be explicitly provided to individual users

  32. Applying Permissions • Permissions within databases are hierarchical and, consequently, are inherited through group and role membership • Since a database role can contain users, groups and other roles, permissions could be applied to a high-level role and all of the users and groups associated with that role would receive the rights assigned to it

  33. Managing Permissions in Databases • Both statement and object permissions can be assigned to users, groups and roles within a database • Each permission can be granted, denied or revoked • When a permission is granted to a user, the user is able to perform the operation in the database

  34. Managing Statement Permissions • Most statement permissions are easily managed through Enterprise Manager Figure 8-9: Granting statement permissions in Enterprise Manager

  35. Managing Object Permissions • Object permissions are managed in a similar way to statement permissions Figure 8-10: Granting object permissions in Enterprise Manager

  36. Resolving Permission Conflicts • SQL Server 2000 always applies deny permissions first when evaluating a particular action for authorization • Denied permissions always take precedence over conflicting granted permissions at the user or role level • When permissions are revoked, SQL Server 2000 simply removes the previous granted granted or denied permission

  37. Resolving Permission Conflicts Figure 8-11: Conflicting permissions

  38. Implementing Security through Database Objects • Securing data with views • Views can be defined to provide either a filtered set of table columns or a limited set of data rows from underlying tables • A common use of a view is to create a special representation of a table that does not contain certain sensitive information

  39. Implementing Security through Database Objects • Securing data with stored procedures and user-defined functions • Stored procedures and user-defined functions allow sets of T-SQL statements to be stored and executed as a single unit • They are typically used to enforce business rules or perform logic

  40. Implementing Security through Database Objects • Controlling access with triggers • Triggers are similar to stored procedures in that they contain saved groups of T-SQL statements • However, triggers are not called directly, so permissions cannot be granted directly to use them • They are controlled by insert, update and delete permissions on the table with which they are defined

  41. Understanding Ownership Chains • Ownership chains • Hierarchy of dependent objects Figure 8-12: Ownership chain

  42. Auditing Security in SQL Server 2000 • Auditing security events in SQL Server 2000 allows a historical record of activity to be made for later review • This is helpful to identify attempts by users to violate security policies

  43. Auditing Security in SQL Server 2000 Figure 8-13: Configuring login auditing with Enterprise Manager

  44. C2 Auditing • Class C2 • Rating granted by National Computer Security Center (NCSC) for products that have been evaluated against the Department of Defense Trusted Computer System Evaluation Criteria • The minimum security rating required by many government agencies and offices and by many corporations

  45. C2 Auditing • A system rated at Class C2 provides a Trusted Computing Base (TCB) that implements the following mechanisms: • User identification and authentication to control general system access • Discretionary access control to protect objects and allow users to distribute access to those objects as appropriate • Auditing to enforce general user accountability

  46. Chapter Summary • SQL Server 2000 has a flexible and scalable security architecture capable of supporting the full spectrum of requirements from 10 users to 10,000 users • Validating user activity is implemented as a two-part process consisting of authentication and authorization • Logins require a separate database user account in each database to gain access

  47. Chapter Summary • Objects and statement permissions are assigned at the database level to users • Roles are an organizational unit in databases that allows users to be grouped together and share a set of permissions • SQL Server 2000 is capable of meeting rigorous C2 certification standards for security and auditing

More Related