http xkcd com327 INTRO TO SQL SERVER SECURITY

  • Slides: 13
Download presentation
http: //xkcd. com/327/ INTRO TO SQL SERVER SECURITY By Robert Biddle

http: //xkcd. com/327/ INTRO TO SQL SERVER SECURITY By Robert Biddle

About Me Data Architect with Hilton Grand Vacations Working with SQL Server for 8

About Me Data Architect with Hilton Grand Vacations Working with SQL Server for 8 years Certified � MCITP Database Administrator � MCITP Database Developer Blog: http: //robbiddle@wordpress. com Twitter: @robert_biddle Email: rob. biddle@gmail. com

Agenda Intended for Software Developers Cover the basics � Logins, Users, Roles, Schemas, Permissions

Agenda Intended for Software Developers Cover the basics � Logins, Users, Roles, Schemas, Permissions SQL Injection � What is it? � How to prevent it?

Authentication SQL Authentication � Requires Username and Password � Info stored on Database Server

Authentication SQL Authentication � Requires Username and Password � Info stored on Database Server Windows Authentication � Requires Username or Group � Info stored in Active Directory � Generates a Token for access � Integrated Security � Trusted Connection

Logins SA (sysadmin) Used for Server-level access Fixed Server Roles � � � �

Logins SA (sysadmin) Used for Server-level access Fixed Server Roles � � � � � sysadmin serveradmin securityadmin processadmin setupadmin bulkadmin diskadmin dbcreator public

Users dbo, guest, INFORMATION_SCHEMA, sys Used for database-level access Fixed Database Roles � �

Users dbo, guest, INFORMATION_SCHEMA, sys Used for database-level access Fixed Database Roles � � � � � db_owner db_accessadmin db_datareader db_datawriter db_ddladmin db_securityadmin db_backupoperator db_denydatareader db_denydatawriter

Tying Logins to Users Every User (database-level) must tie to a Login (server-level)

Tying Logins to Users Every User (database-level) must tie to a Login (server-level)

Database Owner Go to Properties >> Files. Maps the user to dbo, which has

Database Owner Go to Properties >> Files. Maps the user to dbo, which has db_owner rights. Use SA or a Service Account.

Schemas One level under database-level Essentially a Namespace or Organizational Unit Prefixed before Table

Schemas One level under database-level Essentially a Namespace or Organizational Unit Prefixed before Table name � Sales. Order. Detail � dbo. Error. Log � Person. Contact � [My. DomainMy. Username]. Mycreated. Table

Permissions Hierarchy

Permissions Hierarchy

Permissions are applied to Securables Granular control Can be Granted, Denied, or Revoked �

Permissions are applied to Securables Granular control Can be Granted, Denied, or Revoked � � � � ALTER CONTROL DELETE EXECUTE INSERT SELECT UPDATE VIEW DEFINITION

Dynamic SQL – Dynamically building a string and executing that string. Why is it

Dynamic SQL – Dynamically building a string and executing that string. Why is it good? � Gives more flexibility than using Stored Procedures. � Generally good performance. Why is it bad? � Vulnerable to attacks.

Resources SQL Server Security Cribsheet by Robyn Page � www. simple-talk. com (Under SQL

Resources SQL Server Security Cribsheet by Robyn Page � www. simple-talk. com (Under SQL Database Administration) The Curse and Blessings of Dynamic SQL by Erland Sommarskog � www. sommarskog. se/dynamic_sql. html Contact Info � Blog: http: //robbiddle. wordpress. com � Twitter: @robert_biddle � Email: rob. biddle@gmail. com