Transcript MCITP Administrator: Microsoft SQL Server 2005 Database

MCITP Administrator: Microsoft
SQL Server 2005 Database Server
Infrastructure Design Study
Guide (70-443)
Chapter 6 : Designing SQL
Server Service-Level Security
• Server Service Level security
operates for the entire instance
• Includes
– Logins
– Authentication Mode
– HTTP Endpoints
– SQL Agent Roles
– .NET Assemblies
© Wiley Inc. 2006. All Rights Reserved.
Logins
• Two kinds
– Windows Logins
• Windows Users
• Windows Groups
– SQL Logins
• Sa – special login
© Wiley Inc. 2006. All Rights Reserved.
Server Roles
• Created on install
• Can’t be altered
• User minimum permissions
needed to complete a job
© Wiley Inc. 2006. All Rights Reserved.
Database Users
•
•
•
•
•
Mapped to logins
Can be mapped to certificates
Security best applied to roles
Guest user assigned to everyone
Can be orphaned in restored
databases
© Wiley Inc. 2006. All Rights Reserved.
Schemas
• Schema replaces owner from
SQL Server 2000
• Separates owner from schema for
permissions
• Every object must belong to a
schema
• Functions as a namespace
© Wiley Inc. 2006. All Rights Reserved.
Database Roles
• Similar to server roles
• Fixed database roles created on
install; cannot be altered
• Assign users to roles for specific
jobs
• Datawriter and datareader can
easily manage permissions but
give permissions on every object
© Wiley Inc. 2006. All Rights Reserved.
User-defined Roles
• Created by administrator
• Permissions custom to each role
• Best practice is to assign all users
to roles and all rights to roles
© Wiley Inc. 2006. All Rights Reserved.
Application Roles
• Permissions assigned to this role
• No users added
• Role is “invoked” by user and
secured by a password
• Once invoked, permissions remain
the same as long as the session is
active.
© Wiley Inc. 2006. All Rights Reserved.
DDL Triggers
• Similar to triggers in previous
versions
• These operate on data definition
statements (CREATE, DROP, etc).
• Best used for auditing changes to
the schema
• Multiple triggers can be assigned to
an event
© Wiley Inc. 2006. All Rights Reserved.
DDL Triggers – cont’d
• Two scopes
– Server level – logins, endpoints, other server
level objects
– Database level – users and other database
level objects
• Events at each level are hierarchial
• Can trigger on Event groups or Events
• User EVENT DATA instead of inserted
and deleted tables
© Wiley Inc. 2006. All Rights Reserved.
Database Level Encryption
Policy
• Encrypt data
– Discussed in other chapters
• Encrypt code for objects
– Be sure objects are under source
control
– Develop policy to ensure consistency
© Wiley Inc. 2006. All Rights Reserved.
Securing Endpoints
• Multiple types of endpoints (SOAP,
Database mirroring, Service Broker,
etc.)
• Security can be applied just like
other objects for users to limit
access
• Dedicated Admin Connection is a
different type of endpoint
© Wiley Inc. 2006. All Rights Reserved.
Securing Endpoints – cont’d
• SOAP endpoints need to be secured
– Variety of authentication methods can
be used
– Can encrypt the protocol as well using
https
• Service Broker and Database
Mirroring Endpoints
– Need certificate to authenticate
– Can be encrypted
© Wiley Inc. 2006. All Rights Reserved.
SQL Server Agent Roles
• Three New Roles
– SQLAgentUserRole
– SQLAgentReaderRole
– SQLAgentOperatorRole
• Allow delegation of job responsibilities to
non-sysadmin users
• Limited to jobs and history
• Proxies greatly expanded for many more
subsystems
© Wiley Inc. 2006. All Rights Reserved.
.NET Assembly Security
• Created with any .NET language
• Registered with Windows host
• SAFE Permissions
– Limited to data access inside a SQL
Server instance
• EXTERNAL_ACCESS
– Allows access inside SQL instance,
Windows host file system, local
registry, and web services
© Wiley Inc. 2006. All Rights Reserved.
.NET Assembly Security –
cont’d
• UNSAFE Permissions
– Completely unrestricted
– Can access memory buffers, legacy
COM components, etc.
– Must be created by sysadmin
– Be very careful before allowing
UNSAFE assemblies
© Wiley Inc. 2006. All Rights Reserved.
Summary
• User best practices of assigning minimum
permissions, especially with fixed roles
(server or database)
• Use roles as much as possible
• DDL Triggers are excellent for auditing
• Secure endpoints – they are access
points into SQL Server
• Use the lowest level of CLR permissions
required.
© Wiley Inc. 2006. All Rights Reserved.