Transcript Ch06-security

SQL Server 2005
Implementation and
Maintenance
Chapter 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
© Wiley Inc. 2006. All Rights Reserved.
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
© Wiley Inc. 2006. All Rights Reserved.
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.
© Wiley Inc. 2006. All Rights Reserved.
Fixed Server Roles
• Fixed server roles limit user
access to SQL Server
• Each role has a predefined set of
permissions
© Wiley Inc. 2006. All Rights Reserved.
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
© Wiley Inc. 2006. All Rights Reserved.
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
© Wiley Inc. 2006. All Rights Reserved.
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
© Wiley Inc. 2006. All Rights Reserved.
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
© Wiley Inc. 2006. All Rights Reserved.
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
© Wiley Inc. 2006. All Rights Reserved.
Schema Scope
•
•
•
•
•
•
•
•
•
•
Table
View
Function
Procedure
Queue
Type
Rule
Default
Synonym
Aggregate
© Wiley Inc. 2006. All Rights Reserved.
Statement Permissions
• Statement permissions regulate
schema access
• Examples
–
–
–
–
–
–
–
Create Database
Create Table
Create View
Create Procedure
Create Index
Create Rule
Create Default
© Wiley Inc. 2006. All Rights Reserved.
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
© Wiley Inc. 2006. All Rights Reserved.
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
© Wiley Inc. 2006. All Rights Reserved.
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
© Wiley Inc. 2006. All Rights Reserved.
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
© Wiley Inc. 2006. All Rights Reserved.
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
© Wiley Inc. 2006. All Rights Reserved.
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
© Wiley Inc. 2006. All Rights Reserved.
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
© Wiley Inc. 2006. All Rights Reserved.
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
© Wiley Inc. 2006. All Rights Reserved.
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
© Wiley Inc. 2006. All Rights Reserved.
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
© Wiley Inc. 2006. All Rights Reserved.