Sql Server Security Basics by LearnNowOnline

SQL Server Security Basics

Learn More @ http://www.learnnowonline.com Copyright ÂŠ by Application Developers Training Company

Objectives • Understand potential data threats and how SQL Server’s design protects against them • Learn about SQL Server and Windows integrated authentication • See how SQL Server provides an authorization system to control access to data and objects

Agenda • Security Overview • Authentication • Authorization

Security Overview • Relational data is a tempting target for attackers • SQL Server 2008 provides plenty of features to secure your data and server • Need to understand the threats • Match countermeasures to the threats

The Threats • Identifying threats is a critical first step • Type of data will probably influence security measures

• Sometimes the best way to protect data is to never put it in a database • Typical threats • Theft of data • Data vandalism • Protecting data integrity • Illegal storage

Security Design Philosophy • Trustworthy Computing memo, 2002 • Four pillars of security design • Secure by design • Secure by default • Secure in deployment • Secure through communications

• “It’s just secure” • Implications throughout the product • SQL Server is reasonably secure out of the box • Your job is to keep it secure Learn More @ http://www.learnnowonline.com Copyright © by Application Developers Training Company

The Two Stages of Security • Similar to Windows security • Authentication: who are you? • Authorization: now that we know who you are,

what can you do?

Key SQL Server Security Terms • • • • •

Authentication Authorization Group Impersonation Login

• • • • •

Permission Principal Privilege Role User

Agenda • Security Overview • Authentication • Authorization

Authentication • Process of verifying that a principal is who or what it claims to be • SQL Server has to uniquely identify principals in order to

authorize

• Two paths to authentication • Windows authentication • SQL Server authentication

Windows Integrated Authentication • SQL Server assumes a trust relationship with Windows Server • Windows does the heavy lifting for authentication • The SQL Server checks permissions on the principal

• Advantages • Single user login • Auditing features • Simplified login management • Password policies

Configuring SQL Server Security Settings • Select either when install or later • Settings apply to all databases and server objects in an instance of SQL Server • Changing modes after installation may or may not cause problems • Windows to Mixed • Mixed to Windows

SQL Server Authentication • Client applications must provide login credentials as part of connection string • Logins stored in SQL Server • Windows authentication stronger • But must use SQL Server authentication with old

versions of Windows, non-Windows systems

Windows and SQL Server Logins • SQL Server logins are not stored in Windows • Disabled if you select Windows authentication

• Mixed mode is much more flexible • But less secure

Beware of the sa Login • • • • • • •

System administrator login Mapped to sysadmin fixed server role Conveys full system administrator privileges Cannot modify or delete Must use a strong password! Use only as access of last resort NEVER use sa for database access through client applications Learn More @ http://www.learnnowonline.com Copyright © by Application Developers Training Company

Password Policy and Enforcement • Before SQL Server 2005, no enforcement of passwords for SQL Server logins • No minimum strength • No expiration policy

• SQL Server now hooks into Windows password policy • Windows Server 2003, Vista, and later versions • NetValidatePasswordPolicy API method Learn More @ http://www.learnnowonline.com Copyright © by Application Developers Training Company

Contained Databases • Not a security feature per se • But introduces a new authentication scheme

• Solves problem of moving databases • Past: move database plus external dependencies • Contained databases solves associated problems

Contained Databases Authentication • • • •

Can create a SQL user with a password Windows user in database Not associated with a login Authenticate against contained database • Get a token for that database only • Security boundary is tightly scoped

Contained Databases Authentication Connection Request

Matching user in database?

Yes

Password match?

SQL Server No Initial catalog specified?

Yes

Initial catalog contained?

Authentication type?

No No

Authentication failure

Permission in database?

Windows Matching login or group?

Yes

Matching principal in database?

Yes

Server-level authentication

Learn More @ http://www.learnnowonline.com Copyright ÂŠ by Application Developers Training Company

Database authentication

Yes

Agenda • Security Overview • Authentication • Authorization

Authorization • Principals: user or process allowed to access securable objects • Securables: protected resource • Permissions: type of access

Principals • Windows-level principals • Windows Domain Login • Windows Group • Windows Local Login

• SQL Server-level principals • • • •

SQL Server Login SQL Server Login mapped to a certificate SQL Server login mapped to a Windows login SQL Server Login mapped to an asymmetric key

• Database-level principals • • • • • • •

Application Role Database Role Database User Database User mapped to a certificate Database User mapped to a Windows login Database User mapped to an asymmetric key Public Role Learn More @ http://www.learnnowonline.com Copyright © by Application Developers Training Company

Principals • Scope of a principal determines scope of permission • Principal can be a login, user, or role • Roles are analogous to Windows groups • Users in role inherit role’s permissions • Simplify security management

• Types of roles • Fixed server roles • User-defined server roles • Fixed database roles • User-defined database roles Learn More @ http://www.learnnowonline.com Copyright © by Application Developers Training Company

Fixed Server Roles • Cannot alter, even to add new ones, except to add logins to a role • Server roles • • • • • • • •

System administrator Bulk insert administrator Database creator Disk administrator Process administrator Server administrator Setup administrator Security administrator Learn More @ http://www.learnnowonline.com Copyright © by Application Developers Training Company

User-Defined Server Roles • Long awaited security feature • Long have had user-defined database roles • But nothing at the server level

• Used to be, only way to grant some permissions was through a fixed server role • SQL Server 2012 solves these problems

Fixed Database Roles • Control authorization within a database • Configure each database individually • Database roles • • • • • • • • •

db_accessadmin db_backupoperator db_datareader db_datawriter db_ddladmin db_denydatareader db_denydatawriter db_owner db_securityadmin

The Public Role • Every database user assigned to this role • Be very careful about granting permissions • Normally restrict permissions for this role

The dbo (Database Owner) Role • Mapped to sysadmin fixed server role • Not related to db_owner role

User-Defined Database Roles • Standard role • Application role

Securable Objects • Protected resource that you can control access to • Physical object or action

Securable Objects Server Database Endpoint Remote Binding Route Server Role SQL Server Login

Database Application Role Assembly Asymmetric Key Certificate Database user Fixed Database Role Full-Text Catalog Message Type Schema Service Service Contract Symmetric Key

Learn More @ http://www.learnnowonline.com Copyright ÂŠ by Application Developers Training Company

Schema Default Function Procedure Query Stats Queue Rule Synonym Table Trigger Type View XML Schema Collection

Learn More! • This is an excerpt from a larger course. Visit www.learnnowonline.com for the full details! • Learn more about about SQL Server on SlideShare  A Tour of SQL Server