When Too Much is Not a Good Thing

In my previous article, I demonstrated some fun ways of locking down access for certain users to ensure they did not have data access while at the same time allowing them to review the database schema. The solution provided had the added benefit of reducing the chances that such a user could unknowingly cause a server outage.

In that solution, I showed the value of the “View Definition” permission. There are a couple of caveats to this permission that could lead to unwanted results.

It is these unwanted results that oft occur due to lack of attention to detail, use of internet examples, misunderstanding of the requirements; and always from granting way too much access. I am going to explore a couple of these erroneous methods and why they absolutely bug me.

You Get Everything!

Let’s start with the most common mis-step that I see. A user requests access to the database and the stand-in DBA takes the easy route. The easy route here is to grant the requester sa access, dbo access, and every other database and server role there is because it is easier to just tick the boxes.

This method of assigning permissions happens far too frequently. I have seen it in hundreds of databases for clients. I have also written about it here.

You see, once you grant somebody sa access, none of the rest of the permissions grants or denies matter. Sysadmin overrides everything! In this particular case, this would be way too much access for the user who just needs to see the schema. Be extremely careful about when and to whom you grant sysadmin access.

Next up in the realm of granting way too much access is the far too frequent practice of assigning the permissions to the public role in each database. I have written about this practice as well – here and here. This one really gets my ire. Granting permissions to the public role starts to cross into the realm of high risk and downright negligent. Let’s see how some internet examples demonstrate the solution to our problem with regards to the public role.

First, using the test user and role we created in the yesterdays article, let’s confirm that we do not have access to view definitions outside of the assigned role permissions.

SELECT
sp.state_desc
  , sp.permission_name
  , s.name AS SchemaName
  , o.name
FROM sys.database_permissions sp
LEFT JOIN sys.all_objects o
ON sp.major_id = o.object_id
LEFT OUTER JOIN sys.schemas s
ON s.schema_id = o.schema_id
JOIN sys.database_principals u
ON sp.grantee_principal_id = u.principal_id
WHERE u.name = 'public'
  AND o.name IS NOT NULL
  AND o.is_ms_shipped = 0
ORDER BY o.name;

After executing that query, I can confirm that the public role does not have the “View Definition” permission. Let’s now revoke the permission to the role and confirm no schema definitions could be viewed.

USE DBA;
GO
REVOKE VIEW DEFINITION TO BusinessViewDef;

Now, knowing that the user and the role do not have permissions, let’s go ahead and assign permissions to the public role.

GRANT VIEW DEFINITION TO PUBLIC;

Just like magic, a user that should not be able to view schema definitions can now view information it is not supposed to see. This is a security problem. Just to confirm, let’s evaluate the principal permissions compared to the role permissions.

USE DBA;
GO
SELECT
sp.state_desc
  , sp.permission_name
  , s.name AS SchemaName
  , o.name
FROM sys.database_permissions sp
LEFT JOIN sys.all_objects o
ON sp.major_id = o.object_id
LEFT OUTER JOIN sys.schemas s
ON s.schema_id = o.schema_id
JOIN sys.database_principals u
ON sp.grantee_principal_id = u.principal_id
WHERE u.name = 'testvwdef'
  --AND o.name IS NOT NULL
  --AND o.is_ms_shipped = 0
ORDER BY o.name;
EXECUTE AS LOGIN = 'testvwdef'
SELECT SUSER_NAME();
SELECT *
FROM sys.fn_my_permissions(NULL, 'Database');
REVERT

Keep this in mind as you go down the dark path to granting permissions to the public role. Just because it is easy doesn’t make it right. Similar note, you should proceed cautiously with scripts from the internet that suggest granting permissions to the Public role. They are wrong!

Finally, we down into the realm of “only slightly better” but still way over the top for the requirements. This last method uses the “VIEW ANY DEFINITION” way of granting permissions. Now the “ANY” key word actually scopes the permissions to every database on the instance. Let’s clean up the last example by revoking the permission to public so we have a blank slate.

With permissions, revoked, I will now grant View Any Defintion to the test user. Why? Well, because I don’t have that role created in every database (more on the benefits of that approach later). When setting this permission, it is server scoped so make sure you are in the master database else you chance seeing this error.

Msg 4621, Level 16, State 10, Line 22
Permissions at the server scope can only be granted when the current database is master

USE master;
GO
GRANT VIEW ANY DEFINITION TO testvwdef;

Yup, that just made my blood curdle a bit. Let’s check out the perms now in the DBA database (reminder that the scope of the permission change was the master database and is a server scoped change).

EXECUTE AS LOGIN = 'testvwdef'
EXECUTE sp_msforeachdb 'use [?]
SELECT *, ''?'' AS DBName, SUSER_NAME() AS UserName
FROM sys.fn_my_permissions(NULL, ''Database'')
where permission_name = ''View Definition'';'
REVERT

As we can see here, all databases now have the View Definition permission assigned to testvwdef. If there happens to be a sensitive database, you may have exposed yourself to a risk by doing this.

Performing the same tests as previously done would reveal similar results – except on a broader scope.

There are some caveats to help reduce this risk but they do not undermine the need to be responsible or the need to avoid using the “ANY” keyword. Recall that I granted the View Any to the individual user instead of the role? That was simply due to me not having that role in each database. If we grant permissions to roles, and the role does not exist in the master database while trying to grant permissions from that scope, then we see this nifty little message.

Msg 15151, Level 16, State 1, Line 25
Cannot find the login ‘BusinessViewDef’, because it does not exist or you do not have permission.

That is one measure to help prevent this over-permissioning problem. The next possible caveat is a minor stop-gap and can be easily overlooked should the user ever be added to the database in the future. For “Any” to work in its full splendor, the principal to which it was granted must have already been granted “connect” to the database.

The Wrap

This article has explored various different options for minimizing risk to over extending permissions to users. With a little planning and care, we can find various ways to fulfill user requests without compromising the environment.

Every now and then, it will require extra effort in trying to get the pertinent details from the requester. That said, with that extra effort you will find satisfaction and calm knowing your environment is secure and that you are providing quality service to your customers.

This has been another post in the back to basics series. Other topics in the series include (but are not limited to): Backups, backup history and user logins.