12

I have several users who share an MS SQL server 2017 under my administration. They should not see (or even be aware) of the other users and their data on that server. Each user has his own database. They can do whatever they want with their database.

I'm using SQL Server's Partial Containment feature to lock the users in place. The logins are created inside the database. This works well, as they don't see other user accounts or databases this way. The DB logins are added to a Database Role that I create with this command:

USE dbname
CREATE ROLE dbrole
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TABLE, CREATE VIEW, ALTER ANY SCHEMA TO dbrole
DENY EXECUTE TO dbrole

I freshly create a db login account and only add it to said role. The user has no other permissions (that I'm aware of).

The only problem left is that SSMS is still able to browse the server's file system. If I right click on the database and choose Tasks -> Restore -> Database, then select Device: -> [...] and Add a file. This allows SSMS to browse the server's file system, which I would like to deny. The user cannot actually restore the DB, but he can browse the file system.

This question here suggests that SSMS is using the stored procedures xp_fixeddrives, xp_dirtree and xp_fileexist. However, those stored procedures return empty results when executed as a user with the permissions of said group. I've read that this is the behaviour when a user is not a member of the sysadmin role. This already confuses me slightly since I explicitely deny EXECUTE to the dbrole, yet the user can still execute the stored procedures. But still, when browsing the file system through SSMS, it is not empty.

Where does SSMS get the file system information from and how can I prevent this?

edit: I also just noticed that SSMS is able to retrieve a list of all DB backups existing on the server for all databases. Again, I don't how it gets this information and how I can prevent it.

0

2 Answers 2

11

Tracing the queries

When tracing the queries executed, below query is found that lists the folders on the drives one by one.

declare @Path nvarchar(255)
declare @Name nvarchar(255)


select @Path = N'D:\'


select @Name = null;


        create table #filetmpfin (Name nvarchar(255) NOT NULL, IsFile bit NULL, FullName nvarchar(300) not NULL)
        declare @FullName nvarchar(300)  
        if exists (select 1 from sys.all_objects where name = 'dm_os_enumerate_filesystem' and type = 'IF' and is_ms_shipped = 1)
        begin 
          if (@Name is null)
          begin 
              insert #filetmpfin select file_or_directory_name, 1 - is_directory, full_filesystem_path from sys.dm_os_enumerate_filesystem(@Path, '*') where [level] = 0
          end 
          if (NOT @Name is null)
          begin 
            if(@Path is null) 
              select @FullName = @Name 
            else
              select @FullName = @Path  + convert(nvarchar(1), serverproperty('PathSeparator')) + @Name 
              create table #filetmp3 ( Exist bit NOT NULL, IsDir bit NOT NULL, DirExist bit NULL ) 
              insert #filetmp3 select file_exists, file_is_a_directory, parent_directory_exists from sys.dm_os_file_exists(@FullName) 
              insert #filetmpfin select @Name, 1-IsDir, @FullName from #filetmp3 where Exist = 1 or IsDir = 1 
              drop table #filetmp3 
          end
        end 
        else      
        begin         
          if(@Name is null)
          begin
            if (right(@Path, 1) = '\')
              select @Path= substring(@Path, 1, len(@Path) - charindex('\', reverse(@Path)))
            create table #filetmp (Name nvarchar(255) NOT NULL, depth int NOT NULL, IsFile bit NULL )
            insert #filetmp EXECUTE master.dbo.xp_dirtree @Path, 1, 1
            insert #filetmpfin select Name, IsFile, @Path + '\' + Name from #filetmp f
            drop table #filetmp
          end 
          if(NOT @Name is null)
          begin
            if(@Path is null)
              select @FullName = @Name
            else
              select @FullName = @Path +  '\' + @Name
            if (right(@FullName, 1) = '\')
              select @Path= substring(@Path, 1, len(@FullName) - charindex('\', reverse(@FullName)))
            create table #filetmp2 ( Exist bit NOT NULL, IsDir bit NOT NULL, DirExist bit NULL )
            insert #filetmp2 EXECUTE master.dbo.xp_fileexist @FullName
            insert #filetmpfin select @Name, 1-IsDir, @FullName from #filetmp2 where Exist = 1 or IsDir = 1 
            drop table #filetmp2
          end 
        end 



SELECT
Name AS [Name],
IsFile AS [IsFile],
FullName AS [FullName]
FROM
#filetmpfin
ORDER BY
[IsFile] ASC,[Name] ASC
drop table #filetmpfin

The main function used is sys.dm_os_enumerate_filesystem, for each folder that is opened, it goes a level deeper, an example of a second level:

select @Path = N'D:\Data\'

For regular logins

For regular logins it is as easy as denying the select permissions on this TVF to make the user not able to list the folders.

DENY SELECT ON master.sys.dm_os_enumerate_filesystem TO [Domain\LoginName]

When trying to select a backup, the user should see this message:

enter image description here

The user will then be able to only see the drive letters.

enter image description here


For contained users

For the contained user, denying the select on the TVF directly does not work

The contained user can succesfully run the next query example

declare @Path nvarchar(255)
declare @Name nvarchar(255)


select @Path = N'D:\'
select file_or_directory_name, 1 - is_directory, full_filesystem_path from sys.dm_os_enumerate_filesystem(@Path, '*') where [level] = 0

And.... this does not work:

use [PartialDb]
GO
DENY SELECT ON [sys].[dm_os_enumerate_filesystem] TO [PartialUser];
GO

Msg 4629, Level 16, State 10, Line 34 Permissions on server scoped catalog views or system stored procedures or extended stored procedures can be granted only when the current database is master.

Below statements work but they do not restrict the user, even if it is not part of the dbrole role

DENY VIEW DATABASE STATE TO [PartialUser];

DENY VIEW DEFINITION ON SCHEMA :: information_schema TO [PartialUser];

DENY VIEW DEFINITION ON SCHEMA :: sys TO [PartialUser];

DENY SELECT ON SCHEMA :: information_schema TO [PartialUser];

DENY SELECT ON SCHEMA :: sys TO [PartialUser];

What does work? In theory

Since the contained user uses the guest account / public role to connect and select from dmv's, (the public role has access to certain objects by default) we could try restricting the public role.

This is not ideal due to a number of reasons. For example, deny > grant and as a result only members in the sysadmin role will be able to select from this TVF.

Another important thing to note is that changing the guest user / public role can have unknown side-effects on the instance or certain functionalities.

USE MASTER
GO
DENY SELECT ON [sys].[dm_os_enumerate_filesystem] TO public;
GO

Changing public / guest permissions is not an ideal scenario.

For example, disabling the guest user can break the msdb database.

Rerunning the select in the context of the contained user:

Msg 229, Level 14, State 5, Line 7 The SELECT permission was denied on the object 'dm_os_enumerate_filesystem', database 'mssqlsystemresource', schema 'sys'.

There may or may not be a way around this far from ideal approach, I have not found it.

An example of the public role's permissions:

enter image description here

These are granted for a reason, as such certain functionalities might break when denying / revoking these objects. Proceed with caution.

Some more information on the guest user / public role here

4
  • 1
    Is there a gotcha to creating a new role in master for this purpose (as opposed to public)?
    – Jacob H
    Sep 2, 2019 at 20:23
  • @JacobH That would be ideal, the main problem is that the contained user has no corresponding user in master (or a login on the instance) and as a result Defaults to guest / public. I don't think that it is possible to control the user as it connects to master / msdb. disclaimer: I am not by any means a contained database expert. It looks to me that the lowest granularity is on the guest user / public role, which is not ideal. Sep 2, 2019 at 20:43
  • 1
    For reference, I'm using the following permissions to hide information about other DBs / Backups / Filesystem from contained database users: USE MASTER; DENY SELECT ON [sys].[dm_os_enumerate_fixed_drives] TO public; DENY SELECT ON [sys].[dm_os_enumerate_filesystem] TO public; USE msdb; DENY SELECT ON msdb.dbo.backupset TO public; So far, I have not encountered problems with this, but I haven't done intensive testing either.
    – final
    Sep 3, 2019 at 7:14
  • @final Great, thanks for posting an update :). Sep 3, 2019 at 13:48
3

I keep finding more tables that leak information about other databases, hence I decided to post this collection of things that I block to the public role. They do not seem to affect any db functionality, though I take no responsibility on that as I'm only using a small subset of what SQL Server actually offers. It could very well be that this breaks something I'm unaware of.

USE MASTER
GO
DENY SELECT ON [sys].[dm_os_enumerate_fixed_drives] TO public
DENY SELECT ON [sys].[dm_os_enumerate_filesystem] TO public
GO
USE msdb
GO
DENY SELECT ON msdb.dbo.backupfile TO public
DENY SELECT ON msdb.dbo.backupfilegroup TO public
DENY SELECT ON msdb.dbo.backupmediafamily TO public
DENY SELECT ON msdb.dbo.backupmediaset TO public
DENY SELECT ON msdb.dbo.restorefile TO public
DENY SELECT ON msdb.dbo.restorefilegroup TO public
DENY SELECT ON msdb.dbo.restorehistory TO public
GO

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.