SQL SERVER – Say No to DB Data Roles – SQL Security – Notes from the Field #022

[Note from Pinal]: This is a 22nd episode of Notes from the Field series. Security is very important and we all realize that. However, when it is about implementing the security, we all are not sure what is the right path to take. If we do not have enough knowledge, we can damage ourself only. DB Data Roles are very similar concept, when implemented poorly it can compromise your server security.

In this episode of the Notes from the Field series database expert Brian Kelley explains a very crucial issue DBAs and Developer faces on their production server. Linchpin People are database coaches and wellness experts for a data driven world. Read the experience of Brian in his own words.


I am prejudiced against two fixed database roles: db_datareader and db_datawriter. When I give presentations or talk to customers, some are surprised by my stance. I have two good reasons to recommend against these two roles (and their counterparts, db_denydatareader and db_denydatawriter).

A Violation of the Principle of Least Privilege

The first reason is they violate the Principle of Least Privilege. If you’re not familiar with this security principle, it’s really simple: give permissions to do the job – no more and no less. The db_datareader and db_datawriter roles give access to all tables and views in a given database. Most of the time, this is more access than what is needed. This is a violation of the Principle of Least Privilege.

There are some cases where a user needs such access, but there is always the possibility that a new table or view will be added which the user should not have access to. This creates a dilemma: do I create new roles and remove the user from db_datareader or db_datawriter or do I start using DENY permissions? The first involves additional work.The second means the security model is more complex. Neither is a good solution.

Failing the 3 AM Test

The second reason is the use of these roles violates what I call the “3 AM test.” The 3 AM test comes from being on call. When I am awakened at 3 AM because of a production problem, is this going to cause me unnecessary problems? If the answer is yes, the solution fails the test. I classify db_datareader and db_datawriter role usage as failing this test. Here’s why: the permissions granted are implicit. As a result, when I’m still trying to wake up I may miss that a particular account has permissions and is able to perform an operation that caused the problem. I’ve been burned by it in production before. That’s why it fails my test.

An Example

To see why this is an issue, create a user without a login in a sample database. Make it a member of the db_datareader role. Then create a role and give it explicit rights to a table in the database. This script does so in the AdventureWorks2012 database:
USE AdventureWorks2012;
GO
CREATE USER TestDBRoleUser WITHOUT LOGIN;
GO
EXEC sp_addrolemember @membername = 'TestDBRoleUser', @rolename = 'db_datareader';
GO
CREATE ROLE ExplicitPermissions;
GO
GRANT SELECT ON HumanResources.Employee TO ExplicitPermissions;
GO

Pick any table or view at random and check the permissions on it. I’m using HumanResources.Employee:

We see the permissions for the role with explicit permissions. We don’t, however, see the user who is a member of db_datareader. When first troubleshooting it’s easy to make the assumption that the user doesn’t have permissions. This assumption means time is wasted trying to figure out how the user was able to cause the production problem. Only later, when someone things to check db_datareader, will the root cause be spotted. This is why I say these roles fail the 3 AM test.

If you want to get started with performance tuning and database security with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – Auditors, Passwords and Exceptions – Notes from the Field #010

[Note from Pinal]: This is a 10th episode of Notes from the Field series. Every other day, I read online about various security breach happening worldwide – some have lost passwords, some have lost phone numbers and some have lost credit card information. Security is the most important aspect of the application. The worst scenario would be when theives comes to your door and you have kept your key under your door-mat, which they are able to guess. Password is just like key to get into the database. If you keep your password so easy that everyone can guess, it would be very bad for your system. You need to keep it complex and also follow your organization’s policy. In this blog post Brian hits very important concept about application security.

In this episode of the Notes from the Field series database expert Brian Kelley explains a very crucial issue DBAs and Developer faces on their production server. Linchpin People are database coaches and wellness experts for a data driven world. Read the experience of Brian in his own words.


Auditors focus on logins, especially the password settings. Why? They do because it’s easy to audit and those audits find that too many organizations still get them wrong. With respect to SQL Server, you can’t control things like how many days before the password expires or what the complexity rules are. Those are controlled by the operating system and SQL Server follows the operating system’s rules. What can be controlled is if a login follows the login policy rules. There are actually three options here:

  • The login doesn’t follow any of the policy rules.
  • The login follows all of the policy rules.
  • The login follows all of the policy rules except for password expiration.

Auditors are interested in exceptions. When something doesn’t follow the rules or doesn’t follow all of the rules, an auditor gets interested. If you’re asked to show the auditors evidence of what a login follows, there are two ways to do this: via the GUI or via a script. In many cases auditors will ask for a screenshot that looks something like:

Auditing SQL Login (GUI)

Note the areas I’ve highlighted. This is the information most auditors are looking for with respect to logins that are created within SQL Server (not domain accounts like domain users or groups). If you only have a small number of logins, then using the GUI is not very time consuming. If you have a lot of SQL logins or SQL Servers to audit, however, you want to script this. While an auditor may ask for a screenshot, what an auditor really wants is the information contained in the screenshot. Clicking through every login and taking a screenshot is a waste of time. Thankfully, there is a particular catalog view, sys.sql_logins, that contains all the information the auditor will want.

The catalog view sys.sql_logins contains all the same information as sys.server_principals plus other information that is applicable only to SQL Server-based logins. What’s more, it only contains the SQL Server-based logins. It doesn’t contain any information on Windows users and groups. Therefore, it’s the right catalog view to use to satisfy the auditors asking about logins and passwords. This query will return the information they need:

SELECT name, is_disabled,
is_policy_checked, is_expiration_checked
FROM sys.sql_logins;

You can still give them a screenshot, however instead of giving them a screenshot of each and every login, you should give them the screenshot of the query and the results of that query. In the following screenshot, I’ve flagged two logins that would be of interest to the auditors because these do not follow the password policy (is_policy_checked = 0).

SQL Login info by Query

If you have a lot of SQL Servers, you could simply automate this query with a PowerShell script that queries each server and dumps the results to a text file. In that way you save a lot of time providing the results needed, freeing you up for other tasks. Remember, auditors need the information, they don’t necessarily need a screenshot for each and every login.

If you want to get started with performance tuning and database security with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – What Permissions I have on Database I am Connected to?

Here is the first thing I do when I get access to any new server – I check what are different permissions I have with respect to the database I am connected with. The matter of the fact is that most of the database consultants and administrator want to know what are different permissions they have on the database when they get access to any new system. I personally want to own only the permissions which help me to accomplish my work, not a single permission more or less. If I have more permissions, I request the team to take away so I do not make any mistakes accidently.

Here is the script which you can execute to get a list of the permissions over databases.

SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
GO

Here is the result set of above query when I executed with sa or admin access.

Here is the result set of above query when I executed with public access.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Importance of User Without Login – T-SQL Demo Script

Earlier I wrote a blog post about SQL SERVER – Importance of User Without Login and my friend and SQL Expert Vinod Kumar has written excellent follow up blog post about Contained Databases inside SQL Server 2012. Now lots of people asked me if I can also explain the same concept again so here is the small demonstration for it. Let me show you how login without user can help. Before we continue on this subject I strongly recommend that you read my earlier blog post here.

In following demo I am going to demonstrate following situation.

  1. Login using the System Admin account
  2. Create a user without login
  3. Checking Access
  4. Impersonate the user without login
  5. Checking Access
  6. Revert Impersonation
  7. Give Permission to user without login
  8. Impersonate the user without login
  9. Checking Access
  10. Revert Impersonation
  11. Clean up

USE [AdventureWorks2012]
GO
-- Step 1 : Login using the SA
-- Step 2 : Create Login Less User
CREATE USER [testguest] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]
GO
-- Step 3 : Checking access to Tables
SELECT *
FROM sys.tables;
-- Step 4 : Changing the execution context
EXECUTE AS USER   = 'testguest';
GO
-- Step 5 : Checking access to Tables
SELECT *
FROM sys.tables;
GO
-- Step 6 : Reverting Permissions
REVERT;
-- Step 7 : Giving more Permissions to testguest user
GRANT SELECT ON [dbo].[ErrorLog] TO [testguest];
GRANT SELECT ON [dbo].[DatabaseLog] TO [testguest];
GO
-- Step 8 : Changing the execution context
EXECUTE AS USER   = 'testguest';
GO
-- Step 9 : Checking access to Tables
SELECT *
FROM sys.tables;
GO
-- Step 10 : Reverting Permissions
REVERT;
GO
-- Step 11: Clean up
DROP USER [testguest]Step 3
GO

Here is the step 9 we will be able to notice that how a user without login gets access to some of the data/object which we gave permission.

What I am going to prove with this example? Well there can be different rights with different account. Once the login is authenticated it makes sense for impersonating a user with only necessary permissions to be used for further operation. Again this is very basic and fundamental example. There are lots of more points to be discussed as we go in future posts. Just do not take this blog post as a template and implement everything as it is.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Importance of User Without Login

Some questions are very open ended and it is very hard to come up with exact requirements. Here is one question I was asked in recent User Group Meeting.

Question: “In recent version of SQL Server we can create user without login. What is the use of it?”

Great question indeed. Let me first attempt to answer this question but after reading my answer I need your help. I want you to help him as well with adding more value to it.

Answer:

Let us visualize a scenario. An application has lots of different operations and many of them are very sensitive operations. The common practice was to do give application specific role which has more permissions and access level. When a regular user login (not system admin), he/she might have very restrictive permissions. The application itself had a user name and password which means applications can directly login into the database and perform the operation. Developers were well aware of the username and password as it was embedded in the application. When developer leaves the organization or when the password was changed, the part of the application had to be changed where the same username and passwords were used. Additionally, developers were able to use the same username and password and login directly to the same application.

In earlier version of SQL Server there were application roles. The same is later on replaced by “User without Login”. Now let us recreate the above scenario using this new “User without Login”. In this case, User will have to login using their own credentials into SQL Server. This means that the user who is logged in will have his/her own username and password. Once the login is done in SQL Server, the user will be able to use the application. Now the database should have another User without Login which has all the necessary permissions and rights to execute various operations. Now, Application will be able to execute the script by impersonating “user without login – with more permissions”.

Here there is assumed that user login does not have enough permissions and another user (without login) there are more rights. If a user knows how the application is using the database and their various operations, he can switch the context to user without login making him enable for doing further modification. Make sure to explicitly DENY view definition permission on the database. This will make things further difficult for user as he will have to know exact details to get additional permissions.

If a user is System Admin all the details which I just mentioned in above three paragraphs does not apply as admin always have access to everything. Additionally, the method describes above is just one of the architecture and if someone is attempting to damage the system, they will still be able to figure out a workaround. You will have to put further auditing and policy based management to prevent such incidents and accidents.

I guess this is my answer. I read it multiple times but I still feel that I am missing something. There should be more to this concept than what I have just described. I have merely described one scenario but there will be many more scenarios where this situation will be useful. Now is your turn to help – please leave a comment with the additional suggestion where exactly “User without Login” will be useful as well did I miss anything when I described above scenario.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Beginning of SQL Server Architecture – Terminology – Guest Post

SQL Server Architecture is a very deep subject. Covering it in a single post is an almost impossible task. However, this subject is very popular topic among beginners and advanced users.  I have requested my friend Anil Kumar who is expert in SQL Domain to help me write  a simple post about Beginning SQL Server Architecture. As stated earlier this subject is very deep subject and in this first article series he has covered basic terminologies. In future article he will explore the subject further down. Anil Kumar Yadav is Trainer, SQL Domain, Koenig Solutions. Koenig is a premier IT training firm that provides several IT certifications, such as Oracle 11g, Server+, RHCA, SQL Server Training, Prince2 Foundation etc.


In this Article we will discuss about MS SQL Server architecture.

The major components of SQL Server are:

  1. Relational Engine
  2. Storage Engine
  3. SQL OS

Now we will discuss and understand each one of them.

1) Relational Engine: Also called as the query processor, Relational Engine includes the components of SQL Server that determine what your query exactly needs to do and the best way to do it. It manages the execution of queries as it requests data from the storage engine and processes the results returned.

Different Tasks of Relational Engine:

  1. Query Processing
  2. Memory Management
  3. Thread and Task Management
  4. Buffer Management
  5. Distributed Query Processing

2) Storage Engine: Storage Engine is responsible for storage and retrieval of the data on to the storage system (Disk, SAN etc.). to understand more, let’s focus on the concepts.

When we talk about any database in SQL server, there are 2 types of files that are created at the disk level – Data file and Log file. Data file physically stores the data in data pages. Log files that are also known as write ahead logs, are used for storing transactions performed on the database.

Let’s understand data file and log file in more details:

Data FileData File stores data in the form of Data Page (8KB) and these data pages are logically organized in extents.

Extents: Extents are logical units in the database. They are a combination of 8 data pages i.e. 64 KB forms an extent. Extents can be of two types, Mixed and Uniform. Mixed extents hold different types of pages like index, system, data etc (multiple objects). On the other hand, Uniform extents are dedicated to only one type (object).

Pages: As we should know what type of data pages can be stored in SQL Server, below mentioned are some of them:

  • Data Page: It holds the data entered by the user but not the data which is of type text, ntext, nvarchar(max), varchar(max), varbinary(max), image and xml data.
  • Index: It stores the index entries.
  • Text/Image: It stores LOB ( Large Object data) like text, ntext, varchar(max), nvarchar(max),  varbinary(max), image and xml data.
  • GAM & SGAM (Global Allocation Map & Shared Global Allocation Map): They are used for saving information related to the allocation of extents.
  • PFS (Page Free Space): Information related to page allocation and unused space available on pages.
  • IAM (Index Allocation Map): Information pertaining to extents that are used by a table or index per allocation unit.
  • BCM (Bulk Changed Map): Keeps information about the extents changed in a Bulk Operation.
  • DCM (Differential Change Map): This is the information of extents that have modified since the last BACKUP DATABASE statement as per allocation unit.

Log File: It also known as write ahead log. It stores modification to the database (DML and DDL).

  • Sufficient information is logged to be able to:
    • Roll back transactions if requested
    • Recover the database in case of failure
    • Write Ahead Logging is used to create log entries
      • Transaction logs are written in chronological order in a circular way
      • Truncation policy for logs is based on the recovery model

SQL OS: This lies between the host machine (Windows OS) and SQL Server. All the activities performed on database engine are taken care of by SQL OS. It is a highly configurable operating system with powerful API (application programming interface), enabling automatic locality and advanced parallelism. SQL OS provides various operating system services, such as memory management deals with buffer pool, log buffer and deadlock detection using the blocking and locking structure. Other services include exception handling, hosting for external components like Common Language Runtime, CLR etc.

I guess this brief article gives you an idea about the various terminologies used related to SQL Server Architecture. In future articles we will explore them further.

Guest Author 

The author of the article is Anil Kumar Yadav is Trainer, SQL Domain, Koenig Solutions. Koenig is a premier IT training firm that provides several IT certifications, such as Oracle 11g, Server+, RHCA, SQL Server Training, Prince2 Foundation etc.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQLAuthority News – 2 Security Updates for SQL Server 2000 SP 4 Users

If you are using SQL Server 2000 still today my very first recommendation to you is to upgrade to SQL Server 2012. SQL Server 2000 is now 12 years old product and since then many new enhancements as well features which are relevant to current growth and progress in Informational Industry. Now is the time to catch up with the latest trends. Here is one more point for you to notice if this helps you consider to upgrade to the latest version. One can’t upgrade directly from SQL Server 2000 to SQL Server 2012. You need to first upgrade to either SQL Server 2005/2008/R2 and then further plan to upgrade to SQL Server 2012. There is no direct upgrade path for SQL Server 2000 to SQL Server 2012. I strongly believe this is the time to upgrade to the latest version.

Well, also there is a rule that to let something continue if it is not broken and working fine. If you are following that rule and still using SQL Server 2000 I strongly suggest that you upgrade your SQL Server 2000 SP4 and update it with latest Security updates.

Here are two important SQL Server Security Updates.

Security Update for SQL Server 2000 Service Pack 4 (KB983811)

Security Update for SQL Server 2000 Service Pack 4 (KB983812)

As we are talking about SQL Server 2000 let me ask you a quick question – how many of you are still using SQL Server 2000 or earlier version in a production system on at least one server?

Reference: Pinal Dave (http://blog.sqlauthority.com)