SQL ServerDBA安全领域面试题

SQL Server DBA Security Interview Questions
Written By: Jeremy Kadlec

 

From: http://www.mssqltips.com/tip.asp?tip=1566

 

Problem
SQL Server Security, probably one of themost controversial and debated topics among SQL Server DBAs andDevelopers.  One person's security is another person's nightmare andvice versa.  With security being so important for so many differentreasons let's try to determine some baseline interview questionsalthough some of the responses can vary greatly based on theenvironment and industry.  Good luck!

Solution

Question Difficulty = Easy

• Question 1 - True or False - If you lose rights to your SQL Server instance the only option is to hack the registry.
  • False - If the Dedicated AdministratorConnection (DAC) is setup this may be another way to access the SQLServer instance.  Another option may be to use theBUILTIN/Administrators group.  A final option may be to change registryvalues.
  • Additional information - Correct the SQL Server Authentication Mode in the Windows Registry
• Question 2 - What objects does the fn_my_permissions function report on?
  • APPLICATION ROLE
  • ASSEMBLY
  • ASYMMETRIC KEY
  • CERTIFICATE
  • CONTRACT
  • DATABASE
  • ENDPOINT
  • FULLTEXT CATALOG
  • LOGIN
  • MESSAGE TYPE
  • OBJECT
  • REMOTE SERVICE BINDING
  • ROLE
  • ROUTE
  • SCHEMA
  • SERVER
  • SERVICE
  • SYMMETRIC KEY
  • TYPE
  • USER
  • XML SCHEMA COLLECTION
  • Additional information - Script to determine permissions in SQL Server 2005
• Question 3 - Name three of the features managed by the Surface Area Configuration tool.
  • Ad-hoc remote queries
  • Common language runtime
  • Dedicated Administrator Connection
  • Database Mail
  • Native XML Web Services
  • OLE Automation
  • Service Broker
  • SQL Mail
  • Web Assistant
  • xp_cmdshell
  • Additional information
    • Accessing the Windows File System from SQL Server
    • Enabling xp_cmdshell in SQL Server 2005
    • Dedicated Administrator Connection in SQL Server 2005
• Question 4 - What options are available to audit login activity?
  • Custom solution with your application to log all logins into a centralized table
  • Enable login auditing at the instance level in Management Studio
  • Execute Profiler to capture logins into the instance
  • Leverage a third party product

  • Additional information - Who is logging in as the sa login in SQL Server?

Question Difficulty = Moderate

• Question 1 - What is SQL Injection and why is it a problem?
  • SQL Injection is an exploit whereunhandled/unexpected SQL commands are passed to SQL Server in amalicious manner.  It is a problem because unknowingly data can bestolen, deleted, updated, inserted or corrupted.
  • Additional information - Recover from a SQL Injection Attack on SQL Server
• Question 2 - What is the Guest user account?  What login is it mapped to?  Does it make sense to drop the Guest user account?
  • The Guest user account is created by defaultin all databases and is used when explicit permissions are not grantedto access an object.  It is not mapped directly to any login, but canbe used  by any login.  Depending on your security needs, it may makesense to drop the Guest user account, in all databases except Masterand TempDB, although sufficient testing should be conducted to validateapplications will not break with this security restriction.
  • Additional information - SQL Server Database Guest User Account
• Question 3 - True or False - SQL Server 2005 certificates are only backed up via native database backups.
  • False - Certificates can also be backed up via the BACKUP CERTIFICATE command.
  • Additional information - SQL Server 2005 Encryption - Certificates 101
• Question 4 - Name 3 of the features that the SQL Server 2005 built-in function LOGINPROPERTY performs on standard logins.
  • Date when the password was set
  • Locked out standard login
  • Expired password
  • Must change password at next login
  • Count of consecutive failed login attempts
  • Time of the last failed login attempt
  • Amount of time since the password policy has been applied to the login
  • Date when the login was locked out
  • Password hash
  • Additional information - Identify SQL Server 2005 Standard Login Settings

Question Difficulty = Difficult

• Question 1 - How can SQL Server instances be hidden?
  • To hide a SQL Server instance, we need tomake a change in SQL Server Configuration Manager. To do this launchSQL Server Configuration Manager and do the following: select theinstance of SQL Server, right click and select Properties. Afterselecting properties you will just set Hide Instance to "Yes" and clickOK or Apply. After the change is made, you need to restart the instanceof SQL Server to not expose the name of the instance.
  • Additional information - Hiding instances of SQL Server 2005
• Question 2 - True or False - Profiler is the only tool that has the ability to audit and identify DDL events.
  • False - In SQL Server 2005 DDL triggers wereintroduced to audit CREATE, ALTER and DROP events for relational(stored procedures, functions, views, etc.) and security (certificates,logins, server, etc.) objects.
  • Additional information - Auditing DDL (Create, Alter, Drop) Commands in SQL Server 2005
• Question 3 - What are some of the pros and cons of not dropping the SQL Server BUILTIN/Administrators Group?
  • Pros:
    • Any Windows login is by default a SQL Server system administrator
    • This single group can be used to manage SQL Server from a system administrators perspective
  • Cons:
    • Any Windows login is by default a SQL Server system administrator, which may not be a desired situation
    • SQL Server BUILTIN/Administrators Group has system administrator rights by default
    • SQL Server itself does not need to behacked to gain access to your data, if the Windows local administratorsgroup is compromised then it is possible to access SQL Server as asystem administrator
  • Additional information - Security Issues with the SQL Server BUILTIN/Administrators Group
• Question 4 - How can SQL Injection be stopped?
  • Development/DBA
    • Validate the SQL commands that are being passed by the front end
    • Validate the length and data type per parameter
    • Convert dynamic SQL to stored procedures with parameters
    • Remove old web pages and directories that are no longer in use because these can be crawled and exploited
    • Prevent any commands from executing withthe combination of or all of the following commands: semi-colon, EXEC,CAST, SET, two dashes, apostrophe, etc.
    • Based on your front end programminglanguage determine what special characters should be removed before anycommands are passed to SQL Server
      • Depending on the language this could be semi-colon, dashes, apostrophes, etc.
      • Consider building a function to perform this action for both character and numeric data
  • Network Administration
    • Prevent traffic from particular IP addresses or domains
      • See if email based alerts can be sent if traffic comes from these sources
    • Review the firewall settings to determine if SQL Injection attacks can prevented
      • If you have a maintenance agreement with yourfirewall vendor see if you can update your product to prevent or alerton SQL Injection
    • Research products or services to scan your code and web site on a regular basis to prevent the issue
      • http://www.acunetix.com/vulnerability-scanner/
      • http://www.rapid7.com/nexpose/web-application-va.jsp
      • http://www.fortify.com/products/detect/
      • http://www.mcafeesecure.com/us/
      • http://www.onlinehackscan.com/default.asp
  • Additional information - Recover from a SQL Injection Attack on SQL Server

Next Steps

• If you are interested in more SQL Server security related information on MSSQLTips.com, check out the following resources:
  • Security category with 20+ tips
  • Recover from a SQL Injection Attack on SQL Server
  • Identify SQL Server 2005 Standard Login Settings
  • Correct the SQL Server Authentication Mode in the Windows Registry
  • Script to determine permissions in SQL Server 2005
  • Natively Encrypting Social Security Numbers in SQL Server 2005
  • Assigning DBA Rights in SQL Server
  • Error - Windows cannot access the specified device, path or file.
• For more SQL Server DBA and/or Developer interview questions, check out these tips:
  • SQL Server Integration Services Interview Questions
  • SQL Server Backup and Recovery Interview Questions
  • SQL Server 2005 New Features Interview Questions
  • SQL Server DBA Phone Interview Questions
  • SQL Server T-SQL Interview Questions
• Do you have some common SQL Server interview questions thatwere not included in this tip that you ask as a portion of yourinterviews?  Feel free to post them in the forum below.

Readers Who Read This Tip Also Read

• SQL Server Interview Questions for a Network Administrator
• Junior SQL Server Developer Interview Questions
• Junior SQL Server DBA Interview Questions
• SQL Server Developer T-SQL Functions Interview Questions
• SQL Server Developer Integration Services SSIS Interview Questions
• More...

Comment or Ask Questions About This Tip

• Discuss this tip: http://blogs.mssqltips.com/forums/t/818.aspx
• There are 2 comments for this tip, last post by admin