Securing Sql Server 2000

Transcript

How to Secure Your SQL Server 2000 Environment Microsoft Corporation Naval Khosla Technical Specialist - Databases AGENDA Security basics MS SQL Server 2000 Secure Installation MS SQL Server 2000 Pre Installation Planning Post Installations Steps Managing SQL Security Implementing an Authentication Mode Assigning Login Accounts to Users and Roles Assigning Permissions to Users and Roles Managing Security Within SQL Server Managing Application Security Managing SQL Server Security in the Enterprise Database Monitoring & Configuration Understanding SQL Server Transaction Logs Developer Best Practices for SQL in the Enterprise SQL Security Resources Security basics Goals Confidentiality Integrity Availability Security basics Fundamentals Identification Authentication Authorization (access control) Confidentiality Integrity Security Basics Life cycle Design Test Operate Support MS SQL Server 2000 Secure Installation Major Software Installation Considerations Determining the Security Context of the Services Accounts Using Named and Multiple-Instances of SQL Server Selecting a Security Mechanism Selecting SQL Server Collations and Sort Rules Selecting Network Libraries Determining the Security Context of the Services Accounts Default SQL Server Services Logon Account An instance of SQL Server service An instance of SQL Server Agent service Using a Domain User Logon Account Uses Windows 2000/3 trusted connections Using the Local System Logon Account Cannot communicate with remote servers that use Windows trusted connections Autostart Services Run automatically when Windows services are started Using Named and Multiple Instances of SQL Server Default Instance Is identified by the network name on which it is running Only one default instance is permitted Named Instance Is identified by the network name plus an instance name Applications must use SQL Server 2000 client components to connect to a named instance Multiple Instances Default instance and one or more named instances Each instance operates independently on the same computer Selecting a Security Mechanism Selecting Windows Authentication Mode User must have valid Windows 2000 or Windows NT user account User must receive validation by the operating system Selecting Mixed Mode User can use Windows Authentication User can use SQL Server Authentication Selecting SQL Server Collations and Sort Rules Groups of Collations Windows collation is based on the rules for an associated Windows locale SQL collation matches attributes of code page number and sort order specified in earlier versions of SQL Server Default Collation and Sort Rules SQL Server applies the default if you do not designate a collation and sort rules Avoid changing the default collation and sort rules Selecting Network Libraries Default Network Libraries Named Pipes and TCP/IP Sockets are the default for server network libraries for an instance of SQL Server TCP/IP sockets client network library for all management tools Named Pipes as a second client network library on all versions of Windows NT 4.0 and Windows 2000 Server Network Libraries That SQL Server Supports TCP/IP sockets VIA GigaNet SAN Named pipes Multiprotocol NWLink IPX/SPX AppleTalk ADSP VIA ServerNet II SAN Banyan VINES Post Installation Activities Delete or secure old setup files (e.g : sqlstp.log, sqlsp.log, and setup.iss files) - Killpwd free MS tool Make sure you have a clear understanding of SQL System Databases Apply latest Service Packs & Hot Fixes (SUS & SMS) Backup System Databases & User Databases Disable SQL Server ports on your firewall - TCP port 1433 and UDP port 1434 & others associated Assess your server’s security with Microsoft Best Practices Tool Secure Installation Guidelines If you install into NTFS file system, the directories and files are secured Service accounts and the local administrators group get full control, no other permissions set We secure the SQL Server registry keys Same permissions as the NTFS files The default is integrated security on Windows NT/2000/3 Set a sa password when using mixed security – Never select a blank password Don’t run SQL service as box administrator or LocalSystem (NT Authority/System) Domain User with appropriate permissions, NOT a domain admin (See SQL Server Books Online) Use Enterprise Manager to change service account Secure Installation Guidelines… Physical security Protect all related systems, media, backups, etc Protect your Database behind firewalls S/W mediating database access Isolate services where possible Dedicated database server Latest code is most secure code Apply latest service packs and security patches Managing SQL Security Managing SQL Security Implementing an Authentication Mode Assigning Login Accounts to Users and Roles Assigning Permissions to Users and Roles Managing Security Within SQL Server Managing Application Security Managing SQL Server Security in the Enterprise Implementing an Authentication Mode Authentication Processing Choosing an Authentication Mode Mutual Authentication Using Kerberos Impersonation and Delegation Encryption Steps in Implementing an Authentication Mode Creating Login Accounts Setting Up Login Accounts Authentication Processing Windows 2000 Group or User Windows 2000 Verifies Verifiesentry entryinin sysxlogins sysxloginstable; table; trusts truststhat that Windows Windows2000 2000 has hasverified verified password password sysxlogins sysxlogins SQL Server Verifies entry in sysxlogins table, and verifies password SQL Server Login Account sysxlogins sysxlogins Choosing an Authentication Mode Advantages of Windows Authentication Mode Advanced security features Adding groups as one account Fast access Advantages of Mixed Mode Non-Windows 2000 and Internet clients can use it to connect Mutual Authentication Using Kerberos Kerberos Encrypted Encrypted Data Data Password Password Mutual Mutual Authentication Authentication User SQL Server Impersonation and Delegation Impersonation SQL Server Delegation SQL Server File system Encryption Internal Encryption Login passwords Transact-SQL definitions Network Encryption Steps in Implementing an Authentication Mode Set Setthe theAuthentication AuthenticationMode Mode Stop Stopand andRestart RestartMSSQLServer MSSQLServerService Service Create CreateWindows Windows2000 2000Groups Groupsand andUsers Users Authorize AuthorizeWindows Windows2000 2000Groups Groupsand andUsers Usersto to Access AccessSQL SQLServer Server Create CreateSQL SQLServer ServerLogin LoginAccounts Accountsfor forUsers UsersWho WhoConnect Connect with withNon-Trusted Non-TrustedConnections Connections Creating Login Accounts master..sysxlogins master..sysxlogins name name dbname dbname password password BUILTIN\Administrators BUILTIN\Administrators accountingdomain\payroll accountingdomain\payroll accountingdomain\maria accountingdomain\maria mary mary sa sa master master Northwind Northwind Northwind Northwind pubs pubs master master NULL NULL NULL NULL NULL NULL ******** ******** ******** ******** Assigning Login Accounts to Users and Roles Northwind..sysprotects Northwind..sysprotects Permissions Permissionsare are stored storedhere here id id uid uid action action protecttype protecttype 1977058079 1977058079 1977058079 1977058079 1977058079 1977058079 1977058079 1977058079 00 00 00 77 193 193 195 195 196 196 193 193 205 205 205 205 205 205 205 205 Users Usersare arestored stored here here Northwind..sysusers Northwind..sysusers uid uid name name 00 11 33 77 public public dbo dbo INFORMATION_SCHEMA INFORMATION_SCHEMA payroll payroll Assigning Login Accounts to User Accounts Adding User Accounts SQL Server Enterprise Manager sp_grantdbaccess system stored procedure dbo User Account guest User Account Assigning Login Accounts to Roles Fixed Server Roles Fixed Database Roles User-defined Database Roles Fixed Server Roles Role Role Permission Permission sysadmin sysadmin Perform Performany anyactivity activity dbcreator dbcreator Create Createand andalter alterdatabases databases diskadmin diskadmin Manage Managedisk diskfiles files processadmin processadmin Manage Manage SQL SQL Server Server processes processes serveradmin serveradmin Configure Configure server-wide server-wide settings settings setupadmin setupadmin Install Installreplication replication securityadmin securityadmin Manage Manageand andaudit auditserver serverlogins logins bulkadmin bulkadmin Execute Execute BULK BULK INSERT INSERT statements statements Fixed Database Roles Role Role Permission Permission public public Maintain Maintainall alldefault defaultpermissions permissions db_owner db_owner db_ddladmin db_ddladmin Perform Performany anydatabase databaserole roleactivity activity Add Addor orremove removedatabase databaseusers, users,groups, groups, and androles roles Add, Add,modify, modify,or ordrop dropdatabase databaseobjects objects db_security db_securityadmin admin Assign Assignstatement statementand andobject objectpermissions permissions db_backupoperator db_backupoperator Back Backup updatabase database db_datareader db_datareader Read Readdata datafrom fromany anytable table db_datawriter db_datawriter Add, Add,change, change,or ordelete deletedata datafrom fromall alltables tables db_denydatareader db_denydatareader Cannot Cannotread readdata datafrom fromany anytable table db_denydatawriter db_denydatawriter Cannot Cannotchange changedata datain inany anytable table db_accessadmin db_accessadmin User-defined Database Roles Add a Role: When a Group of People Needs to Perform the Same Activities in SQL Server If You Do Not Have Permissions to Manage Windows 2000 Accounts Assigning Permissions to Users and Roles Types of Permissions Granting, Denying, and Revoking Permissions Granting permissions to allow access Denying permissions to prevent access Revoking granted and denied permissions Types of Permissions Statement Statement CREATE CREATEDATABASE DATABASE CREATE CREATETABLE TABLE CREATE CREATEVIEW VIEW CREATE CREATEPROCEDURE PROCEDURE Object Object Predefined Predefined SELECT SELECT INSERT INSERT UPDATE UPDATE DELETE DELETE REFERENCES REFERENCES TABLE VIEW CREATE CREATERULE RULE CREATE CREATEDEFAULT DEFAULT CREATE CREATEFUNCTION FUNCTION SELECT SELECT COLUMN UPDATE UPDATE REFERENCES REFERENCES BACKUP BACKUPDATABASE DATABASE BACKUP BACKUPLOG LOG EXEC EXEC STORED PROCEDURE Fixed FixedRole Role Object ObjectOwner Owner Granting, Denying, and Revoking Permissions REVOKE: REVOKE: Neutral Neutral GRANT: GRANT: Can CanPerform PerformAction Action DENY: DENY: Cannot CannotPerform PerformAction Action Granting Permissions to Allow Access User/Role User/Role Eva Eva Ivan Ivan David David public public SELECT INSERT UPDATE UPDATE DELETE DELETE SELECT INSERT DRI DRI Denying Permissions to Prevent Access User/Role User/Role Eva Eva Ivan Ivan David David public public SELECT INSERT UPDATE UPDATE DELETE DELETE SELECT INSERT DRI DRI Revoking Granted and Denied Permissions User/Role User/Role Eva Eva Ivan Ivan David David public public SELECT INSERT UPDATE UPDATE DELETE DELETE SELECT INSERT DRI DRI Managing Security Within SQL Server Determine Use of Default Login Accounts sa BUILTIN\Administrators Determine Function of guest User Account Determine public Role Permissions Apply Permissions to Roles Create Objects with Owner dbo Secure CmdExec and ActiveScripting Job Steps Managing Application Security Managing Security with Views and Stored Procedures Managing Client Application Security with Application Roles Managing Security with Views and Stored Procedures SELECT SELECT ** FROM FROM Employee_View Employee_View SELECT SELECT ** FROM FROM Employees Employees EXEC EXEC Employee_Update Employee_Update 1, 1, 99 Employees Employees EmployeeID EmployeeID 11 22 33 LastName LastName Davolio Davolio Fuller Fuller Leverling Leverling FirstName FirstName Nancy Nancy Andrew Andrew Janet Janet ReportsTo ReportsTo 22 22 ...... Ownership Chains If calling object and caller object have same owner; permissions check skipped on called object Can be used to hide underlying schema through views and/or stored procedures Leverage ownership chaining to manage permissions Using Ownership Chaining Hide underlying schema through views/SPs Leverage ownership chaining to manage perms Ownership Chaining: If calling object and caller object have same owner; permissions check skipped on called object Example: Create table user1.t1 (c1 int not null) Create proc user2.proc1 as select * from user1.t1 return If user3 has execute permissions on proc1, still need select permissions on user1.t1 Execute Perms Select Perms checked checked for User3 for User3 User2.Proc1 User1.T1 User1.Proc1 User1.T1 Execute Perms checked for User3 NO Perms checked for User3 User3 Managing Client Application Security with Application Roles Order OrderEntry EntryApplication Application Microsoft MicrosoftExcel Excel Orders Orders OrderID OrderID 10248 10248 10249 10249 10250 10250 CustomerID CustomerID VINET VINET TOMSP TOMSP HANAR HANAR EmployeeID EmployeeID 33 11 22 ...... Creating Application Roles Creating an Application Role Inserts a Row into the sysusers Table Managing Application Role Permissions Activating Application Roles User Must Specify Password Scope Is Current Database—if User Switches to Another Database, User Has Guest Permissions in That Database Role Cannot Be Deactivated Until User Disconnects EXEC EXEC sp_setapprole sp_setapprole 'SalesApp', 'SalesApp', {ENCRYPT {ENCRYPT N'hg_7532LR'}, N'hg_7532LR'}, 'ODBC' 'ODBC' Managing SQL Server Security in the Enterprise Using Group Policy to Secure SQL Server Using Proxy Servers, Firewalls, and Routers Using On-the-Wire Encryption to Secure Data Using Group Policy to Secure SQL Server Security Areas That Can Be Configured Account policies Restricted groups Software policies Using Proxy Servers, Firewalls, and Routers Internet User Microsoft Proxy Server SQL Server Using On-the-Wire Encryption to Secure Data Internet Protocol Security Secure Sockets Layer Recommended Practices Use Use Mixed Mixed Mode Mode for for Non-Trusted Non-Trusted or or Internet Internet Clients Clients Only Only Use Use the the sysadmin sysadmin Role Role Rather Rather Than Than the the sa sa Login Login Account Account Remove Remove Windows Windows 2000 2000 Accounts Accounts First, First, Then Then SQL SQL Server Server Accounts Accounts dbo dbo User User Should Should Own Own All All Objects Objects Use Use Stored Stored Procedures Procedures and and Views Views to to Simplify Simplify Security Security Database Auditing & Configuration SQL Server 2000 Auditing Turn on failed login auditing The default is None Turn on additional security auditing SQL Trace – the server side of profiling SQL Profiler – the UI components Auditing is performed by SQL Trace – internal to SQLServr.exe C2-Style Auditing also available SQL Profiler Trace and Capture Server Activity Choose events to monitor Choose trace criteria Choose what data to capture Group data meaningfully Database Monitoring Alerts Job Operators Database Configuration Sp_configure parameters Tempdb configuration Understanding SQL Server Transaction Logs How the Transaction Log Works 11 Data Data modification modification is is sent sent by by application application Buffer Cache Data pages pages are are located located in, in, 22 Data or or read read into, into, buffer buffer cache cache and and modified modified 33 Modification Modification is is recorded recorded in in transaction transaction log log on on disk disk Disk Disk 44 Checkpoint Checkpoint writes writes committed committed transactions transactions to to database database Developer Best Practices for SQL in the Enterprise Application Best Practices Use Weak access accounts Only capable of actions needed to run app Use different account for administration Use windows auth rather than SQL Auth Easier to secure No password storage required If using SQL auth, use SSL Turn on Encryption for sensitive data Use roles for permissions and ownership Ease of management Objects owned by roles, need not be dropped/renamed when user dropped Do not grant permissions to public Don’t show “developer quality” error messages to users Can reveal information to attackers in multi phase attacks Dynamic SQL Dynamic SQL inside stored procedures runs in the security context of the executor of the proc, not in the owner of the stored proc This is a security feature to protect you! Example: Admin: create proc myproc @p1 nvarchar(500) as exec (@p1) return 0 Malicious user: exec myproc “sp_addlogin ‘joe’ exec sp_addsrvrolemember ‘joe’,’sysadmin’ ” And you’re sysadmin… ☺ Preventing SQL Injection Class of attack where attacker can insert or manipulate queries made by application to backend Example: APPLICATION CODE var shipcity; ShipCity = Request.form (“Shipcity”) var sql = “SELECT * FROM OrdersTable WHERE ShipCity = ‘” + Shipcity + “’”; GOOD USER Inputs Redmond in the form Query to backend is: SELECT * FROM OrdersTable WHERE ShipCity = ‘Redmond’ MALICIOUS USER Inputs the following in the form: Redmond’ DROP TABLE OrderTable – Query to the backend is: SELECT * FROM OrdersTable WHERE ShipCity = ‘Redmond’ DROP TABLE OrdersTable—’ SQL Injection Why SQL injection works? Connection made in context of higher privileged account App accepts arbitrary user input Mitigating SQL Injection Validate all user input Define set of valid input, accept only that Reject all invalid input Avoid using dynamic SQL in stored procs Run applications in minimally privileged contexts Never run as sysadmin Tips for App Dev Teams Understanding various security issues Different threat vectors, attack scenarios Awareness of issues like SQL injection, cross site scripting, buffer overflow attacks Componentize and Threat Analyze Enumerate Component boundaries Analyze functions, interfaces and interactions of the component can it be compromised? What data flows in and out? Compromise could be through different kinds of threats: Escalation of privileges; tampering of data; spoofing; information disclosure; code injection Code Review Develop Code review checklists Guideline for common security issues Directed code reviews—based on threat analysis Generic file reviews—top down approach SP3 Security Changes Requiring non blank SA passwords on upgrade Option to by-pass if really required Non blank strong SA password highly recommended Sp_change_users_login Password required for autofix option No creation of logins with NULL pwds Changing database ownership Only sysadmins can Restriction to prevent cross DB escalation of privilege Cross DB Ownership Chaining Off by default; option to turn on at instance level Marking system objects On sysadmin can mark objects as system objects SQL Security Resources Resources – Microsoft Web Sites SQL Server 2000 Security Information page http://www.microsoft.com/sql/security Best Practices Analyzer Tool for Microsoft SQL Server http://www.microsoft.com/downloads/details.aspx?displ ayla%20ng=en&familyid=B352EB1F-D3CA-44EE-893E9E07339C1F22&displaylang=en Microsoft Baseline Security Analyzer http://www.microsoft.com/technet/security/tools/ Tools/mbsahome.asp Software Update Services 1.0 with Service Pack 1 http://www.microsoft.com/downloads/details.aspx?Famil yID=a7aa96e4-6e41-4f54-972cae66a4e4bf6c&DisplayLang=en Resources - Books SQL Server Security Chip Andrews, David Litchfield, Bill Grindlay http://www.amazon.com/exec/obidos/tg/detail//0072225157/qid=1067276265 SQL Server Security Distilled Morris Lewis http://www.amazon.com/exec/obidos/tg/detail//1590591925/qid=1067276265 Mastering SQL Server 2000 Security Mike Young, Curtis Young http://www.amazon.com/exec/obidos/tg/detail//0471219703/qid=1067276265 Writing Secure Code Second Edition Michael Howard & David LeBlanc http://www.amazon.com/exec/obidos/tg/detail//0735617228/qid=1067276203