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 the most controversial and debated topics among SQL Server DBAs and Developers.  One person's security is another person's nightmare and vice versa.  With security being so important for so many different reasons let's try to determine some baseline interview questions although some of the responses can vary greatly based on the environment 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.
  • 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.
  • 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 where unhandled/unexpected SQL commands are passed to SQL Server in a malicious manner.  It is a problem because unknowingly data can be stolen, 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 default in all databases and is used when explicit permissions are not granted to access an object.  It is not mapped directly to any login, but can be used  by any login.  Depending on your security needs, it may make sense to drop the Guest user account, in all databases except Master and TempDB, although sufficient testing should be conducted to validate applications 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.
  • 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 to make a change in SQL Server Configuration Manager. To do this launch SQL Server Configuration Manager and do the following: select the instance of SQL Server, right click and select Properties. After selecting properties you will just set Hide Instance to "Yes" and click OK or Apply. After the change is made, you need to restart the instance of 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.
  • 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 be hacked to gain access to your data, if the Windows local administrators group is compromised then it is possible to access SQL Server as a system 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 with the combination of or all of the following commands: semi-colon, EXEC, CAST, SET, two dashes, apostrophe, etc.
      • Based on your front end programming language determine what special characters should be removed before any commands 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
    • Additional information - Recover from a SQL Injection Attack on SQL Server

Next Steps

Readers Who Read This Tip Also Read

Comment or Ask Questions About This Tip

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值