October 4, 2016 at 11:31 am
I "know" the answer to this question, I think. But I've never put this into practice before, so I want to double-check before I move forward.
User A (windows login) has db_datareader and db_datawriter in Database X. User A wants Bulk Insert permissions to pull data into R, which would require me giving him the server-level BulkAdmin role. Would adding User A to BulkAdmin give him permissions outside of Database X?
I was in the process of doing an experiment with a SQL Login to test this when I discovered something semi-terrifying. A SQL login can read data on master and msdb even without permissions on those databases. Using the logins from SQL 2008 BOL under the EXECUTE AS entry, I did this:
USE MyDB;
GO
CREATE LOGIN login1 WITH PASSWORD = 'J345#$)thb';
GO
CREATE USER user1 FOR LOGIN login1;
GO
EXECUTE AS USER = 'user1'
BEGIN
SELECT TOP 10 * FROM dbo.MyTable;
SELECT SUSER_NAME(), USER_NAME();
SELECT TOP 10 * FROM master.sys.databases;
SELECT SUSER_NAME(), USER_NAME();
SELECT TOP 10 * FROM msdb..restorehistory;
SELECT SUSER_NAME(), USER_NAME();
SELECT TOP 10 * FROM OtherDB.dbo.MyTable2;
SELECT SUSER_NAME(), USER_NAME();
END
REVERT;
As expected, I got an error when trying to select from OtherDB. But this newly created login/user set was easily able to read from master.sys.databases and msdb..restorehistory even though I never granted it permission on master or msdb. Now I'm wondering if we have to go through actively denying access to all our system DBs on all plain user accounts. Yet another maintenance headache that I didn't want to deal with.
Also, I'm newly worried about what granting a server-level role to a windows login on a single database will do.
Now I have two questions. 1) Should I go ahead and grant BulkAdmin (my initial thought is no), risking other data getting away from us? And 2) how worried should I be that users can pull information from system DBs when they only have permission to a single user DB?
October 4, 2016 at 1:59 pm
Well, on #2 I did some checking on one of my SQL2012s. Turns out that the GUEST User is enabled on both MSDB and MASTER. I spot checked one of the tables in your query in Master (sys.databases) and GUEST has SELECT permissions to it. Now, I *KNOW* I didn't set this up, and I installed this server and have been the only sysadmin user to touch it, so I'd suspect this is by default from MS.
Most likely so that when someone connects with SSMS who is not a sysadmin, they still get the list of databases.
As for #1, I'd be leery of adding someone to the BulkAdmin role on a server with databases they aren't supposed to be able to touch / access.
October 5, 2016 at 5:15 am
jasona.work (10/4/2016)
Well, on #2 I did some checking on one of my SQL2012s. Turns out that the GUEST User is enabled on both MSDB and MASTER. I spot checked one of the tables in your query in Master (sys.databases) and GUEST has SELECT permissions to it. Now, I *KNOW* I didn't set this up, and I installed this server and have been the only sysadmin user to touch it, so I'd suspect this is by default from MS.Most likely so that when someone connects with SSMS who is not a sysadmin, they still get the list of databases.
Ahhh. I wonder what will happen if I disable Guest. I think I will try this in Dev and ask people with limited access to let me know what they do and don't see.
As for #1, I'd be leery of adding someone to the BulkAdmin role on a server with databases they aren't supposed to be able to touch / access.
That's where I'm landing, but given the puppy dog eyes of the user in question, I kind of got wrapped up in trying to help. I've recommended he consider using SSIS to load R instead (automate the whole process instead of running it manually), but User A really is leery of "wasting more time" on finding another solution. So the user may just get stuck with running the inserts the old fashioned way.
October 5, 2016 at 11:38 am
FWIW - you may want to be careful if you disable guest. When some of what you are seeing was kind of going around on various forums, there actually were recommendations to disable it. There were some MS blogs recommending disabling it. This led to a lot of problems and Microsoft fast published a KB article recommending not to disable it for msdb and then issued this article:
https://support.microsoft.com/en-us/kb/2539091
Likely whats most important is to make sure its disabled in all user databases - due to the side effects of what you just saw.
Sue
October 5, 2016 at 12:24 pm
Sue_H (10/5/2016)
FWIW - you may want to be careful if you disable guest. When some of what you are seeing was kind of going around on various forums, there actually were recommendations to disable it. There were some MS blogs recommending disabling it. This led to a lot of problems and Microsoft fast published a KB article recommending not to disable it for msdb and then issued this article:https://support.microsoft.com/en-us/kb/2539091
Likely whats most important is to make sure its disabled in all user databases - due to the side effects of what you just saw.
Sue
BAH! I don't want users seeing this stuff.
Thanks for the link, Sue. I know guest is disabled on user dbs.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy