{"id":77598,"date":"2018-03-19T00:16:31","date_gmt":"2018-03-19T00:16:31","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=77598"},"modified":"2019-01-22T17:21:44","modified_gmt":"2019-01-22T17:21:44","slug":"securing-access-administrator","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/securing-access-administrator\/","title":{"rendered":"Securing access to data for admin and dbo"},"content":{"rendered":"

In this blog, I want to explore what you can do to block the owner of a database from doing stuff in the database they “own”.\u00a0 Own is a strange term, because really there is just one user that is listed as owner, but there are there are three users who essentially are owner level, super-powered users in a database:<\/p>\n

1. A login using server rights, usually via the sysadmin server role (or a server permission to view all data)
\n 2. The user dbo in a database, acquired either as a sysadmin, or as being the user listed as the owner of the database
\n 3. Members of the db_owner database role<\/p>\n

Sometimes, in the context of a database, these all start to blur together. But they are definitely all three independent things. Let’s write some code and see the differences, and one of the cases may be surprising to you. To do this, I will just be using the SELECT permission on a single table, but other rights will generally behave similarly. Note that another tool in your toolbox is\u00a0Row Level Security in SQL Server 2016+. It is different, in that you can include a predicate that excludes the dbo, which you can read more about here<\/a> in some of my earlier blogs.<\/span><\/p>\n

For our tests, we will use the following security objects.\u00a0<\/p>\n

The sysadmin role is generally consider the super, ignore all other security user, and this is generally the case. Let’s start the example by\u00a0creating a server standard login, and make it a part of the sysadmin server role. I am using a standard loging to cover pretty much all typical cases, including how things work for you using a domain account in Windows Authentication, in Linux, etc. If you are working on a laptop where you log in using a hotmail\/outlook account to log in, things behave in an atypical manner, so the standard login is the simplest.<\/p>\n

--If you wish, you can use the user you are logged in as instead of the TypicalAdminLogin, but this works for any case<\/code>
\n CREATE LOGIN TypicalAdminLogin WITH PASSWORD = '33nqq0u2cnX$v3';\u00a0<\/code>
\n GO<\/code>
\n ALTER SERVER ROLE sysadmin ADD MEMBER TypicalAdminLogin;<\/code>
\n GO<\/code><\/p>\n

A second login will be used to show what happens when a user that is not the owner of the database, but is sysadmin tries to access the data. We will use this login to try out the server level permissions also<\/p>\n

CREATE LOGIN AnotherTypicalAdminLogin WITH PASSWORD = '33nqq0u2cnX$v3';<\/code>
\n GO<\/code>
\n ALTER SERVER ROLE sysadmin ADD MEMBER AnotherTypicalAdminLogin;<\/code>
\n GO<\/code><\/p>\n

If you wish, you can use the user you are logged in or disconnect and relogin as the TypicalAdminLogin login for the demonstration.<\/p>\n

Next create a new db, as that login, so you are logged in as the owner of the database:<\/p>\n

CREATE DATABASE TestDatabaseOwnerSecurity;<\/code>
\n GO<\/code>
\n USE TestDatabaseOwnerSecurity;<\/code>
\n GO<\/code><\/p>\n

To test access, I will create a table named testRights, with a single test row, and will not grant any user SELECT privileges on this table:<\/p>\n

CREATE TABLE dbo.TestRights<\/code>
\n (<\/code>
\n \u00a0 \u00a0\u00a0 value varchar(20) CONSTRAINT PKTestRights PRIMARY KEY<\/code>
\n );<\/code>
\n INSERT INTO dbo.testRights (value)<\/code>
\n VALUES ('Can See'); <\/code>
\n GO<\/code><\/p>\n

To enable easy checking of status of each scenario, I will create the following procedure. It will give me the owner of the db, and the context of the user. I used EXECUTE with a literal to avoid the security aspects of stored procedures, so it is simply shorthand to not repeat a lot of code throughout the blog;<\/p>\n

CREATE OR ALTER PROCEDURE dbo.Security$CheckStatus<\/code>
\n AS<\/code>
\n EXECUTE ('<\/code>SELECT SUSER_SNAME(owner_sid) AS DatabaseOwner,<\/code>
\n \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0 SUSER_SNAME() AS ServerPrincipal,<\/code>
\n \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0 USER_NAME() AS DBPrincipal,<\/code>
\n \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0 IS_MEMBER(''db_owner'') AS memberDBO,<\/code>
\n \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0 IS_SRVROLEMEMBER(''sysadmin'') AS memberSysAdmin<\/code>
\n FROM sys.databases<\/code>
\n WHERE name = ''testDatabaseOwnerSecurity'';<\/code><\/p>\n

SELECT value<\/code>
\n FROM dbo.TestRights;')<\/code>
\n GO<\/code>
\n GRANT EXECUTE ON dbo.Security$CheckStatus TO PUBLIC; --everyone should be able to execute this<\/code><\/p>\n

Now, let’s see the output when the user that created the database is :<\/p>\n

EXECUTE dbo.Security$CheckStatus<\/code><\/p>\n\n\n\n\n\n\n\n
DatabaseOwner<\/span><\/span><\/code><\/td>\nServerPrincipal\u00a0\u00a0\u00a0\u00a0 <\/span><\/span><\/code><\/td>\nDBPrincipal\u00a0\u00a0\u00a0 <\/span><\/span><\/code><\/td>\nmemberDBO\u00a0\u00a0 <\/span><\/span><\/code><\/td>\nmemberSysAdmin<\/span><\/span><\/code><\/td>\n<\/tr>\n
TypicalAdminLogin\u00a0\u00a0 <\/span><\/code><\/td>\nTypicalAdminLogin\u00a0\u00a0 <\/span><\/code><\/td>\ndbo\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0 <\/span><\/code><\/td>\n1<\/span><\/code><\/td>\n1<\/span><\/code><\/td>\n<\/tr>\n
\u00a0<\/td>\n\u00a0<\/td>\n\u00a0<\/td>\n\u00a0<\/td>\n\u00a0<\/td>\n<\/tr>\n
value<\/span><\/span><\/code><\/td>\n\u00a0<\/td>\n\u00a0<\/td>\n\u00a0<\/td>\n\u00a0<\/td>\n<\/tr>\n
Can See<\/span><\/code><\/td>\n\u00a0<\/td>\n\u00a0<\/td>\n\u00a0<\/td>\n\u00a0<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

As expected. We have not granted any rights for this user to have access, so we know that we cannot REVOKE any rights to change anything. So applying a DENY is our only chance to remove the rights of the DBO. So let’s start by attempting to remove rights from the db_owner role:<\/p>\n

DENY SELECT ON OBJECT::dbo.TestRights TO db_owner;<\/code>
\n DENY SELECT ON OBJECT::dbo.TestRights TO dbo;<\/code><\/p>\n

To which you are greeted with:<\/p>\n

Msg 4617, Level 16, State 1, Line 76<\/code>
\n Cannot grant, deny or revoke permissions to or from special roles.<\/code><\/p>\n

Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.<\/code><\/p>\n

The first is an error message, the next a message without error message number, but clearly, that did NOT work. So clearly we cannot revoke the rights from the group directly. What about indirectly? The error message may lean towards the following not working, but let’t DENY access to the object to the public role, since everyone is a member of this role and it will be simpler. Doesn’t have to be public, it can be any other group that the users you are tryi<\/span>ng to DENY are a member of<\/p>\n

DENY SELECT ON OBJECT::dbo.TestRights TO public;\u00a0<\/code><\/p>\n

Ok, so what is this going to do? More than you may expect, actually. Clearly no user who is not one of the special roles is going to have access. But what about the three cases we have set up?<\/p>\n

1. Members of a server role
\n 2. The user dbo in a database
\n 3. Members of the db_owner database role<\/p>\n

Members of a server role<\/strong><\/p>\n

The obvious example, which we will start on is the sysadmin of the server. Later, we will also see how the SELECT ALL USER SECURABLES permission works.<\/p>\n

Let’s try in our current context as the sysadmin\/owner of the database<\/p>\n

EXECUTE dbo.Security$CheckStatus;<\/code><\/p>\n

Perhaps expected, the user can see still the data as they are dbo, sysadmin, and dbowner role.<\/p>\n\n\n\n\n\n\n\n
DatabaseOwner<\/code><\/span><\/td>\nServerPrincipal\u00a0\u00a0\u00a0\u00a0 <\/code><\/span><\/td>\nDBPrincipal\u00a0\u00a0\u00a0 <\/code><\/span><\/td>\nmemberDBO\u00a0\u00a0 <\/code><\/span><\/td>\nmemberSysAdmin<\/code><\/span><\/td>\n<\/tr>\n
TypicalAdminLogin\u00a0\u00a0 <\/code><\/td>\nTypicalAdminLogin\u00a0\u00a0 <\/code><\/td>\ndbo\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0 <\/code><\/td>\n1<\/code><\/td>\n1<\/code><\/td>\n<\/tr>\n
 <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n<\/tr>\n
value<\/code><\/span><\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n<\/tr>\n
Can See<\/code><\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

Now, let’s try as the admin that we created that is not the owner of the database<\/p>\n

EXECUTE AS LOGIN = 'AnotherTypicalAdminLogin';<\/code>
\n GO<\/code>
\n EXECUTE dbo.Security$CheckStatus;<\/code>
\n GO<\/code>
\n REVERT;<\/code><\/p>\n

This returns:<\/p>\n\n\n\n\n\n\n\n
DatabaseOwner<\/code><\/span><\/td>\nServerPrincipal\u00a0\u00a0\u00a0\u00a0 <\/code><\/span><\/td>\nDBPrincipal\u00a0\u00a0\u00a0 <\/code><\/span><\/td>\nmemberDBO\u00a0\u00a0 <\/code><\/span><\/td>\nmemberSysAdmin<\/code><\/span><\/td>\n<\/tr>\n
TypicalAdminLogin\u00a0\u00a0 <\/code><\/td>\nAnotherTypicalAdminLogin <\/span><\/code><\/td>\ndbo\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0 <\/code><\/td>\n1<\/code><\/td>\n1<\/code><\/td>\n<\/tr>\n
 <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n<\/tr>\n
value<\/code><\/span><\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n<\/tr>\n
Can See<\/code><\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

Expected. But now, let’s try creating a user for that login in the database, and use DENY to try to stop access:<\/p>\n

CREATE USER AnotherTypicalAdminLogin FOR LOGIN AnotherTypicalAdminLogin;<\/code>
\n DENY SELECT ON OBJECT::dbo.TestRights TO AnotherTypicalAdminLogin;<\/code>
\n GO<\/code><\/p>\n

Execute the Security$CheckStatus procedure as before, and you will see the exact same results, so sysadmin is sysadmin, no matter any users in the database. If you remove the sysadmin server role from the server user.<\/p>\n

ALTER SERVER ROLE sysadmin DROP MEMBER AnotherTypicalAdminLogin;<\/code><\/p>\n

Then this would be a completely different setup, with this login having access to the database from their user (which is automatically granted the CONNECT database right), and you will see things change to no access to the table, and not a member of any special role:<\/p>\n\n\n\n\n
DatabaseOwner<\/code><\/span><\/td>\nServerPrincipal\u00a0\u00a0\u00a0\u00a0 <\/code><\/span><\/td>\nDBPrincipal\u00a0\u00a0\u00a0 <\/code><\/span><\/td>\nmemberDBO\u00a0\u00a0 <\/code><\/span><\/td>\nmemberSysAdmin<\/code><\/span><\/td>\n<\/tr>\n
TypicalAdminLogin\u00a0\u00a0 <\/code><\/td>\nAnotherTypicalAdminLogin <\/code><\/td>\nAnotherTypicalAdminLogin<\/code><\/td>\n0<\/code><\/td>\n0<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

Msg 229, Level 14, State 5, Line 141<\/code>
\n The SELECT permission was denied on the object 'testRights', database 'TestDatabaseOwnerSecurity', schema 'dbo'.<\/code><\/p>\n

So sysadmin is not affected by a DENY, but what about other server security? For example, using the following 3 GRANT statements, we can configure the user to have access to all of the data on a server, but will the DENY affect this?<\/p>\n

USE Master;<\/code>
\n --These rights are an awesome way to create a read-only admin role<\/code>
\n GRANT VIEW ANY DATABASE to AnotherTypicalAdminLogin; --see any database<\/code>
\n GRANT CONNECT ANY DATABASE to AnotherTypicalAdminLogin; --set context to any database<\/code>
\n GRANT SELECT ALL USER SECURABLES to AnotherTypicalAdminLogin; --see any data in databases <\/code>
\n GO<\/code>
\n USE TestDatabaseOwnerSecurity;<\/code><\/p>\n

Now trying to access the data again:<\/p>\n

EXECUTE AS LOGIN = 'AnotherTypicalAdminLogin'<\/code>
\n GO<\/code>
\n EXECUTE dbo.Security$CheckStatus<\/code>
\n GO<\/code>
\n REVERT;<\/code><\/p>\n

And you will see the very same failure as before, which is exactly what you probably desire. Hence, these rights do NOT override local rights. This is definitely a very fringe case to configure, because it would be very rare to DENY data to public.. so you would need the login to have a user in the database to DENY them, but it is what it is.<\/span><\/p>\n

Removing the DENY from the USER, and even dropping the user will not change anything:<\/p>\n

REVOKE SELECT ON OBJECT::dbo.TestRights TO AnotherTypicalAdminLogin;<\/code>
\n DROP USER AnotherTypicalAdminLogin;<\/code><\/p>\n

Only if you were to REVOKE the deny to public will the AnotherTypicalAdminLogin (now a server login only if you try both of those two statements), will then open up rights<\/p>\n

REVOKE SELECT ON OBJECT::dbo.TestRights TO public;<\/code>
\n GO<\/code>
\n EXECUTE AS LOGIN = 'AnotherTypicalAdminLogin';<\/code>
\n GO<\/code>
\n EXECUTE dbo.Security$CheckStatus;<\/code>
\n GO<\/code>
\n REVERT;<\/code>
\n GO<\/code><\/p>\n

This now allows the user to execute the code:<\/p>\n\n\n\n\n\n\n\n
DatabaseOwner<\/code><\/span><\/td>\nServerPrincipal\u00a0\u00a0\u00a0\u00a0 <\/code><\/span><\/td>\nDBPrincipal\u00a0\u00a0\u00a0 <\/code><\/span><\/td>\nmemberDBO\u00a0\u00a0 <\/code><\/span><\/td>\nmemberSysAdmin<\/code><\/span><\/td>\n<\/tr>\n
TypicalAdminLogin\u00a0\u00a0 <\/code><\/td>\nAnotherTypicalAdminLogin <\/code><\/td>\nAnotherTypicalAdminLogin<\/code><\/td>\n0<\/code><\/td>\n0<\/code><\/td>\n<\/tr>\n
 <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n<\/tr>\n
value<\/code><\/span><\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n<\/tr>\n
Can See<\/code><\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

This is really great news for the system administrator. Using server rights, you can give a login access to either:<\/p>\n

* ALL data on the server as a sysadmin
\n * All un-denied data on the server using SELECT ALL USER SECURABLES and rights to connect to a database.<\/p>\n

--put the DENY back for the rest of the examples<\/code>
\n DENY SELECT ON OBJECT::dbo.TestRights TO public;<\/code><\/p>\n

The user dbo in a database.<\/strong><\/p>\n

The next case we will try is when a user is mapped to the dbo user, specifically. We have already shown that we can’t stop the sysadmin, nor can we deny the dbo specifically. Now, lets attempt to add a login, make it the owner of the db, but no other rights in the database or server, other than ownership of the database:<\/p>\n

CREATE LOGIN NonAdminLogin WITH PASSWORD = '33nqq0u2cnX$v3';<\/code>
\n ALTER AUTHORIZATION ON DATABASE::TestDatabaseOwnerSecurity TO NonAdminLogin;<\/code>
\n GO<\/code><\/p>\n

Now try to access the date using this login, that is the database owner:<\/p>\n

EXECUTE AS LOGIN = 'NonAdminLogin'<\/code>
\n GO<\/code>
\n EXECUTE dbo.Security$CheckStatus<\/code>
\n GO<\/code>
\n REVERT;<\/code>
\n GO<\/code><\/p>\n

<\/code>This user is translated, as are sysadmin role logins, to the dbo user in the database, and as such, has full access:<\/p>\n\n\n\n\n\n\n\n
DatabaseOwner<\/code><\/span><\/td>\nServerPrincipal\u00a0\u00a0\u00a0\u00a0 <\/code><\/span><\/td>\nDBPrincipal\u00a0\u00a0\u00a0 <\/code><\/span><\/td>\nmemberDBO\u00a0\u00a0 <\/code><\/span><\/td>\nmemberSysAdmin<\/code><\/span><\/td>\n<\/tr>\n
NonAdminLogin\u00a0<\/code><\/td>\nNonAdminLogin<\/code><\/td>\ndbo<\/code><\/td>\n0<\/code><\/td>\n0<\/code><\/td>\n<\/tr>\n
 <\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n<\/tr>\n
value<\/code><\/span><\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n<\/tr>\n
Can See<\/code><\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

So the user dbo cannot be denied, either by statement, or by public.<\/p>\n

Members of the db_owner database role<\/strong><\/p>\n

Finally, let’s try one more thing, that intuitively, seems unnecessary to try. I know, at least by name, I expected that “member of db_owner database role”<\/strong><\/em> = “dbo”<\/strong><\/em>. But… is that true? Created a user named TestDbo in the datbase and make it a member of the db_owner role . It has no login to get security from, so this is the only right applied.<\/p>\n

CREATE USER TestDbo WITHOUT LOGIN;<\/code><\/p>\n

ALTER ROLE db_owner ADD MEMBER TestDbo;<\/code>
\n GO<\/code><\/p>\n

Executing the query, you get probably, NOT what you expect (I didn’t!):<\/p>\n

EXECUTE AS User = 'TestDbo'<\/code>
\n GO<\/code>
\n EXECUTE dbo.Security$CheckStatus<\/code>
\n GO<\/code>
\n REVERT;<\/code>
\n GO<\/code><\/p>\n\n\n\n\n
DatabaseOwner<\/code><\/span><\/td>\nServerPrincipal\u00a0\u00a0\u00a0\u00a0 <\/code><\/span><\/td>\nDBPrincipal\u00a0\u00a0\u00a0 <\/code><\/span><\/td>\nmemberDBO\u00a0\u00a0 <\/code><\/span><\/td>\nmemberSysAdmin<\/code><\/span><\/td>\n<\/tr>\n
NonAdminLogin\u00a0<\/code><\/td>\nS-1-9-3-2245412934-107...<\/code><\/td>\ntestDbo<\/code><\/td>\n1<\/td>\n0<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Msg 229, Level 14, State 5, Line 282<\/code>
\n The SELECT permission was denied on the object 'testRights', database 'TestDatabaseOwnerSecurity', schema 'dbo'.<\/code><\/p>\n

Membership in the db_owner role is not exactly the same as being the dbo. The dbouser, like sysadmin server role members, is not subject to any “typical” security, but role members are.\u00a0<\/p>\n

Summary<\/strong><\/p>\n

Security is a MESSY subject, and can be difficult to configure correctly. Users with too much security are very dangerous to the health and accessibility of your data. Too little and people are annoyed, but too much and you may end up sharing more to the user than expected.<\/p>\n

Here we have covered the ways that you can affect a user’s access to data in several ways (Ignoring Row Level Security, which can also be useful)<\/span>:<\/p>\n

Using server rights and roles. Sysadmin logins are all powerful, but using the SELECT ALL USER SECURABLES server permission can be counteracted by using DENY at the database level.<\/p>\n

The user dbo in a database is all powerful, but members of the db_owner database role are not equivalent to the dbo user. These users are subject to a DENY, unlike the DBO.<\/p>\n","protected":false},"excerpt":{"rendered":"

In this blog, I want to explore what you can do to block the owner of a database from doing stuff in the database they “own”.\u00a0 Own is a strange term, because really there is just one user that is listed as owner, but there are there are three users who essentially are owner level,……<\/p>\n","protected":false},"author":56085,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[4619,68855,4183],"coauthors":[19684],"class_list":["post-77598","post","type-post","status-publish","format-standard","hentry","category-blogs","tag-security","tag-sql-provision","tag-t-sql"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/77598","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/56085"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=77598"}],"version-history":[{"count":7,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/77598\/revisions"}],"predecessor-version":[{"id":77605,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/77598\/revisions\/77605"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=77598"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=77598"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=77598"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=77598"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}