SQL Server: Database Security Interview Questions and Answers (Day-6)

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

How do you grant a SQL Server developer truncate table permission without giving them the ability to alter the table?

This is accomplished by a DBA creating a stored procedure with the TRUNCATE TABLE statements and then granting execute permission on the stored procedure to those who need it.

I have a table where some of the columns should not be queryable by all users. How can I filter the data appropriately, so that not everyone can select the data?

One of the easiest ways to do this is through the use of views.

If we can use a view, we can successfully filter the data and grant the users access to the view, we can present just the data we want the users to have access to.

however, there are two requirements for this:

• The users do not already have the ability to select data from the table.
• A view can be created where an ownership chain can be established with the table.

How to grant only few columns without creating views?

We can assign column list to table.

I can create Stored Procedure, but However, when I went to execute the stored procedure, SQL Server told me I didn’t have permission to do so. Is this a bug or did I do something wrong?

This is entirely possible if you have the ability to create a stored procedure in a schema you don’t own.

In this particular case we’ve created a role that has the ability to create stored procedures in the database and the ability to alter the dbo schema. By default, the dbo schema is owned by the database owner.

What do you know about the SQL Server 2016 Row Level Security?

SQL Server 2016 has introduced Row Level Security (RLS) which is a feature that enables fine grained control over access to rows in a table. RLS allows you to easily control which users can access which data with complete transparency to the application.This enables us to easily restrict the data based on the user identity or security context.

Row-Level Security enables customers to control access to rows in a database table based on the characteristics of the user executing a query.

Which are the different predicates support by Row Level Security?

RLS supports two types of security predicates.

Filter predicates silently filter the rows available to read operations (SELECT, UPDATE, and DELETE).

Block predicates explicitly block write operations (AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, BEFORE DELETE) that violate the predicate.

What do you know about the Dynamic Data Masking?

Dynamic data masking limits sensitive data exposure by masking it to non-privileged users. Dynamic data masking helps prevent unauthorized access to sensitive data by enabling customers to designate how much of the sensitive data to reveal with minimal impact on the application layer. It’s a data protection feature that hides the sensitive data in the result set of a query over designated database fields, while the data in the database is not changed.

For example, a call center support person may identify callers by several digits of their social security number or credit card number, but those data items should not be fully exposed to the support person. A masking rule can be defined that masks all but the last four digits of any social security number or credit card number in the result set of any query.

What should be the characteristics for strong password?

• Is at least 8 characters long.
• Combines letters, numbers, and symbol characters within the password.
• Is not found in a dictionary.
• Is not the name of a command.
• Is not the name of a person.
• Is not the name of a user.
• Is not the name of a computer.
• Is changed regularly.
• Is significantly different from previous passwords.

How SQL Injection Works?

The primary form of SQL injection consists of direct insertion of code into user-input variables that are concatenated with SQL commands and executed. A less direct attack injects malicious code into strings that are destined for storage in a table or as metadata. When the stored strings are subsequently concatenated into a dynamic SQL command, the malicious code is executed.

The injection process works by prematurely terminating a text string and appending a new command. Because the inserted command may have additional strings appended to it before it is executed, the malefactor terminates the injected string with a comment mark “–“. Subsequent text is ignored at execution time.

What are your best practices to avoid SQL Injection?

• Validate all input.
• Use Type-Safe SQL Parameters.
• Use Parameterized Input with Stored Procedures.
• Wrapping Parameters with QUOTENAME() and REPLACE().
• Use the Parameters Collection with Dynamic SQL.

How Do You Determine What Data Are Confidential?

• We should to contact our legal department (if there is one), an attorney, or law professor, if in doubt about data.
• Ask about, What type of data should we be extra cautious about?
• Ask government officials or look at government websites on the industry we are in.
• We should think like a hacker.

Have you seen any performance hit using SQL Server Transparent Data Encryption (TDE)?

Given that you are performing more operations, there is some. One advantage to TDE is that the data is only encrypted in disk. So if it’s in SQL Server’s memory buffers, there’s no repeated decryption going on.

Any concerns about the SQL Server public database role?

Yes, the general recommendation is to avoid using the public role. Every database user is a member of public. As a result, any permissions assigned to it are also assigned to every database user.

What do you use to avoid the SQL Server fixed server roles?

The recommended practice is to create a role and assign explicit permissions.

A developer wants to execute a SQL Server stored procedure in debug mode. I believe SQL Server sysadmin level access required to use debug mode. I don’t want to give sysadmin access to the developer. Is there any I can handle this without giving sysadmin access?

Unfortunately, there is not. Debugging requires sysadmin role membership.

Is it possible to grant linked server permissions to Windows user group?

Yes: A Windows group is no different from any other login.

T-SQL Script to find mismatched between Database Login and Server Login.

What is the main hierarchy of Permissions?
Please visit this Microsoft official documents:

What should be your common practice for SQL Server Security?

• SQL Server should be hardened after the installation.
• After the installation, use the SQL Server Configuration Manager tool in order to disable unnecessary features and services.
• Install only required components.
• Recent service packs and critical fixes should be installed for SQL Server and Windows.
• Windows Authentication mode is more secure than SQL Authentication.
• If there is still a need to use SQL Authentication – enforce strong password policy.
• Disable the SA account and rename it. Do not use this account for SQL server management.
• Change default SQL Server ports associated with the SQL Server installation to keep hackers from port scanning the server.
• Hide SQL Server instances or disable the SQL Server Browser service.
• Remove BUILDIN\Administrators group from the SQL Server Logins.
• Enable logging SQL Server login attempts (failed & successful).
• Defining Database Ownership to Increase Security.
• Disabling certain system stored procedures.