sql server 2005 implementation and maintenance chapter 6: security and sql server 2005

SQL Server 2005 Implementation and

MaintenanceChapter 6: Security and SQL

Server 2005

Authentication Modes• The authentication mode dictates

how SQL Server processes usernames and passwords

• There are two modes– Windows Authentication

• This mode gives users access to SQL Server using their Windows credentials

– SQL Server and Windows Authentication

• Also called Mixed Mode

© Wiley Inc. 2006. All Rights Reserved.

Windows Authentication• Users do not have to remember

multiple user names and passwords

• Logins are controlled by Windows password policies

• Logins can be mapped to a single user, or Windows group account

• Non-Windows users (i.e. MAC) cannot log in to SQL Server


Mixed Mode• This allows Windows and non-Windows users to

log in• Non-Windows users use a SQL Server login

account– The user logs on to their network– The user opens a nontrusted connection to SQL

Server using a username and password other than those used to gain network access.

• It’s called a nontrusted connection because SQL Server doesn’t trust the operating system to verify the user’s password.

– SQL Server matches the username and password entered by the user to an entry in the Syslogins table.

• Consider use Windows Authentication for DBA and Mixed mode for applications


Common Login Items

• Logins set the default database and default language

• You can grant access to databases when you created a login

• You can add the login to a fixed server role at create time

• Although you can create Database logins in Windows Authentication mode, you won’t be able to use them. If you try, SQL Server will ignore you and use your Windows credentials instead.


Fixed Server Roles

• Fixed server roles limit user access to SQL Server

• Each role has a predefined set of permissions


Fixed Server Roles• Sysadmin

– to perform any task in SQL Server, DBA only• Serveradmin

– can set serverwide configuration options, such as how much memory SQL Server can use or how much information to send over the network in a single frame, or even shut down the server.

• Setupadmin– can install replication and manage extended stored procedures

• Securityadmin– manage security issues such as creating and deleting logins, reading– the audit logs, and granting users permission to create databases

• Processadmin– can end (kill) a process, good for developers to be in

• Dbcreator– can create and make changes to databases

• Diskadmin– manage files on disk. They perform actions such as mirroring

databases and adding backup devices• Bulkadmin

– can execute the BULK INSERT statement


Database User Mappings

• Also called user accounts• These accounts give users access to a

database• Users need an account in each

database they need to access• There are two special accounts

– DBO• Members in Sysadmin are also DBOs

– Guest user• Every user who has a server account can also

access other DBs as a guest– limit the access of this account


Permissions• Any object to which SQL Server

regulates access is called a securable• You assign permissions on securables

for users• Securables fall under one of three

scopes– Server– Database– Schema


Server Scope• Server• Endpoint• SQL Server login• SQL Server login mapped to

Windows login• SQL Server login mapped to

certificate• SQL Server login mapped to

asymmetric key


Database Scope

• Database users• Database users mapped to Windows login• Database users mapped to certificate• Database users mapped to asymmetric key• Database roles• Application roles• Assemblies• Message type• Service contract• Service• Fulltext catalog• DDL events• Schema


Schema Scope

• Table• View• Function• Procedure• Queue• Type• Rule• Default• Synonym• Aggregate


Statement Permissions

• Statement permissions regulate schema access

• Examples– Create Database– Create Table– Create View– Create Procedure– Create Index– Create Rule– Create Default


Object Permissions

• These regulate access to the data• There are 12 object permissions

– Control– Alter– Take ownership– Impersonate– Create– View definition– Select– Insert– Update– Delete– References– Execute


Database Roles

• These are groups with permissions already applied– Adding a user to one of these roles

gives them the permissions assigned to the role

• There are two types:– Fixed database roles– Custom database roles


Fixed Database Roles

• These are built-in to each database:– Db_owner– Db_accessadmin– Db_datareader– Db_datawriter– Db_ddladmin– Db_securityadmin– Db_backupoperator– Db_denydatareader– Db_denydatawriter– Public


Custom Database Roles

• The permissions assigned to the fixed roles will not meet all of your needs

• You can create your own database roles and assign permissions


Application Roles

• This is a special role that must be activated using a password

• It is designed to force users to use a specific application to access data

• The activation code can be hardwired into your custom applications


Permission States

• Permissions can be in one of three states• Grant

– User can use permission• Revoke

– User can use permission if they are a member of a group that has permission

• Deny– User cannot use permission under any

circumstance


Ownership Chains

• When a user queries a view, or executes a stored procedure, SQL Server checks the owner of the underlying object

• If the owners are the same, only permissions on the queried object are verified

• If they are different, permissions on both objects are verified– This is called a broken ownership chain


Linked Server Security

• Linked servers allow users to include data from others servers in a result set– Example, you could join data from

SQL Server and Oracle• Add a linked server using

sp_addlinkedserver


Encryption

• Data transferred from the client to the server can be encrypted on the wire

• You need a certificate to do this• Once you import the certificate

you can configure SQL Server using Configuration Manager


Devising a Plan• Consider several options:

– Types of users– Fixed server role usage– Database access– Type of access– Group permissions– Object creation– Public role permissions– Guest access


sql server 2005 implementation and maintenance chapter 6: security and sql server 2005

Documents