security david frommer principal architect business intelligence microsoft partner of the year 2005...
TRANSCRIPT
Security
David FrommerPrincipal Architect
Business Intelligence
Microsoft Partner of the Year 2005 & 2007
Agenda
• Principals, Securables and Permissions
• User Schema Separation• Password Policies• Credentials• Proxy Accounts• Impersonation• Column Level Encryption
Principals, Securables and Permissions
• The new security model for SQL Server 2005
• Principal • Individuals, groups, and processes that
can request SQL Server resources.•Logins, Users, Roles, etc
• Securable•A Securable is a resource that can be
secured•Tables, Views, Endpoints, etc
Principals, Securables and Permissions
• Permission•Permissions grant principals access to
securables•Grant a user Execute rights to a Stored
Procedure, etc
•SQL 2005 introduces new permissions like Control, Alter Any and Impersonate
•Permissions work in hierarchies
Principals, Securables and Permissions
Agenda
• Principals, Securables and Permissions
• User Schema Separation• Password Policies• Credentials• Proxy Accounts• Impersonation• Column Level Encryption
User Schema Separation Overview
• What is a schema?•A collection of database objects that
form a namespace
• SQL 2000•Server.Database.Owner.Object
• SQL 2005•Server.Database.Schema.Object
User Schema Separation Benefits
• Dropping database users is greatly simplified
• Multiple users can own a schema through roles or windows groups.
• Multiple users can share a default schema• Developers and applications can own and
share objects in a specific schema instead of in DBO
• Permissions can be managed at the schema level instead of the object level
User Schema Separation
• During an upgrade, SQL Server will create a schema for every user in the database.
• Create schemas for applications and avoid continued use of the DBO schema
Agenda
• Principals, Securables and Permissions
• User Schema Separation• Password Policies• Credentials• Proxy Accounts• Impersonation• Column Level Encryption
Password Policies
• You can now use windows password policies for SQL accounts (note that SQL accounts cannot have a different policy than the Windows accounts)
• Password expiration rules• Windows Server 2003 or higher• Enforcement can be decided on a per-login
basis• This feature is not enforced by default.
Logins upgraded from SQL 2000 will not have this turned on.
Agenda
• Principals, Securables and Permissions
• User Schema Separation• Password Policies• Credentials• Proxy Accounts• Impersonation• Column Level Encryption
Credentials Overview
• A credential is a record that contains the authentication information required to connect to a resource outside of SQL Server• Generally it maps to a Windows login
• SQL Server logins can be mapped to credentials
• A login maps to one credential but a single credential can map to many logins
Credentials Benefits
• Giving SQL Server accounts access to OS resources
• Creating SQL Agent proxies• Giving applications access to other SQL
services (SSAS, SSRS, SSIS)
Agenda
• Principals, Securables and Permissions
• User Schema Separation• Password Policies• Credentials• Proxy Accounts• Impersonation• Column Level Encryption
Agent Proxy Accounts
• Defines the security context for a job step• SQL 2000 – only one proxy account available for all
jobs. Generally this account had very high levels of privileges
• SQL 2005 – Many proxy accounts that can have limited access to certain subsystems and principals can be assigned rights to use particular proxies.
• The list of available proxies will be filtered by the type of job step and proxies to which the user has access.
• During upgrade the old proxy account is changed to an UpgradeProxyAccount with access to the subsystems that were explicitly used.
Agent Proxy Accounts
Credential
Proxy
Principal
SQL Login
Windows Login
Grant Logon as Batch
SQLAgentUser Role
Give Access
Agent Proxy Accounts Subsystems
• ActiveX Script• Operating System• Replication Distributor• Replication Merge• Replication Queue Reader• Replication Snapshot• Replication Transaction-Log Reader• Analysis Services Command• Analysis Services Query• SSIS Package Execution
Agenda
• Principals, Securables and Permissions
• User Schema Separation• Password Policies• Credentials• Proxy Accounts• Impersonation• Column Level Encryption
Impersonation
• Run under the security context of another principal
• EXECUTE AS CALLER• EXECUTE AS user_name• EXECUTE AS SELF (Creator)• EXECUTE AS OWNER
Agenda
• Principals, Securables and Permissions
• User Schema Separation• Password Policies• Credentials• Proxy Accounts• Impersonation• Column Level Encryption
Encryption Overview
• Final security barrier for sensitive data is typically data encryption
• Encryption increases processor load and consumes storage space
• Encryption requires key management• Symmetric encryption:
• Is fast•Uses one key•Does not provide nonrepudiation
Encryption Types
• Asymmetric encryption:•Uses a key pair• Is slower than symmetric encryption•Provides confidentiality and
nonrepudiation
• Hybrid encryption:•Takes advantage of the speed of
symmetric encryption and the increased security of asymmetric encryption
Encryption Diagram
Encryption Best Practices
• Key management is critical to an encryption framework•Key generation•Key usage•Key backup•Key regeneration
Encryption Best Practices
• Limit the use of encryption to sensitive data•Consider performance effect of
encryption•Consider whether an external source
requires access to encrypted data•Consider increased size of ciphertext
over plaintext