none
hardening SQL server databases preventing non-DBA user assignment to system database RRS feed

  • Question

  • Hello all,

    I have a client that would like to implement that non-DBA users should not have a system database as a default. Client would like to make sure that a non-DBA not assigned a system database causing a security breach. Any way to prevent this? Is assigning a security risk? Is this at all possible? Any links to documentation would be helpful.

    Friday, October 11, 2019 10:51 PM

All replies

  • It could be done with Policy-Based Management, but only as a reporting rule. That is, you cannot prevent new logins from being set up this way through PBM, but you can run the rule regularly to check for violations. But if the client is not already using Policy-Based Management, I see little reason to start with now. I can't say that I am a great fan of this feature.
    You could also do it with a server-level DDL trigger.

    Whether you should do it is another matter. One challenge is how to distinguish DBA logins from non-DBA logins. One possibility is to check against membership in a certain AD group. Then again, how often do you add DBA logins? Simply disabling trigger in that case may work just as well. The purpose of the trigger would only be to remind you to explicitly set a default database when you create a user.
    Is there a security problem with non-DBA users having a system database as their default database? From a security perspective, I can't see that it would be an issue if they have master or tempdb as their default database. They can always access these databases anyway, since guest is enabled in these databases, and this can't be turned off. (model and msdb are another matter.) You could argue that from a convenience perspective that it is better that they have the default database set to an application database. But if that database is dropped for some reason, they can get problems with logging in. For that reason, I may prefer tempdb.

    When it comes to DBA logins, I reaally recommend setting their default database to tempdb rather than master. This reduces the risk that they go and create objects in master which should have been created elsewhere.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, October 12, 2019 9:44 AM
  • When it comes to DBA logins, I reaally recommend setting their default database to tempdb rather than master. This reduces the risk that they go and create objects in master which should have been created elsewhere.

    +1. Although tempdb is technically a system database, the client probably just wants to avoid master as the default. By using tempdb for DBA users, a server-level DDL trigger need only check for master ast tje default database without distinguishing between DBA and non-DBA logins.

    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Saturday, October 12, 2019 2:37 PM
    Moderator
  • The reason the default database is a system database is so that on connection there is assured to be a database available. Have you considered what will have to the login should the user database (which has been made the default) is inaccessible? Give it a shot and see what happens.

    Besides for someone looking to cause trouble even the public role is sufficient to cause a server crash as I show here.

    https://www.youtube.com/watch?v=5FbK3UvoSkY

    System databases access will still be granted to principals regardless of the default database so your not really securing anything by changing the default DB. 

    Try creating a login as you wanted and then run 

    select * from master.sys.syslogins.



    Jayanth Kurup - www.enabledbusiness.com - If the post was helpful or answered your query please press the "Vote as helpful" or "Propose/Mark as Answer" within the Post. - All we can try and do is be polite and helpful.

    Saturday, October 12, 2019 4:10 PM
  • Good day <?!?>

    >> I have a client that would like to implement that non-DBA users should not have a system database as a default.

    We can simply change the default database of the new created LOGIN by using DDL TRIGGER on the event CREATE_LOGIN.

    This step is pretty simple:
    http://ariely.info/Blog/tabid/83/EntryId/240/CREATE-DLL-TRIGGER-ON-CREATE_LOGIN-to-configure-default-parameters-for-any-new-LOGIN.aspx

    >> Client would like to make sure that a non-DBA not assigned a system database causing a security breach.

    This is more problematic and worth discussion😃
    * it is a bit long... I will write what come to my mind now and will make more order if/when I will have time, but this should give you basically all you need/asked for

    Any LOGIN can connect the master database.

    If there is no USER in the master database then the LOGIN will be connected as "guest" USER using the "public" policy.

    You cannot disable access to the guest user in master or tempdb.

    To clarify!!!
    EVERYTHING THAT I WRITE FROM THIS POINT FOREWORD DESCRIBE WHAT CAN BE DONE AND NOT WHAT YOU SHOULD DO.
    For Most cases this is highly not recommended!

    What can be done?!?

    Option 1: You can REVOKE or DNY permissions from the "public" role or the guest USER in the master and in the tempdb databases.

    Option 2: A preferred option is to (1) for each LOGIN create new user in the databases master and temptdb, (2) create new database role in these databases, (3) add add the users to those roles, (4) add DNY permissions to the new role.

    Note: remember that temptdb is created each time the server restart.

    More information:

    Note: All these actions you can execute inside the DDL TRIGGER above, so it will be executed automatically on any new LOGIN.

    Note: For most cases I will NOT recommend to change permissions for public or guest. Instead you can create roles and assign those to the USER/LOGIN.

    Option: For each LOGIN you can create a USER in the master and in the tempdb, so the clients will not use the default "guest". The "public" policy will still be enforced on them by default.

    Note! REVOKE and 

    Example/Demo Option 1

    >> You can "REVOKE VIEW ANY DATABASE TO public". This will make it harder to watch which databases you have in the system and what are the databases properties using "SELECT * FROM sys.databases". In this case only the master and temptdb returns in the query.

    >> You can "REVOKE VIEW ANY COLUMN ENCRYPTION KEY DEFINITION TO public".

    >> You can "REVOKE VIEW ANY COLUMN MASTER KEY DEFINITION TO public".

    >> You can REVOKE SELECT on specific sys tables/views like for example (IMPORTANT! this should be done for each database meaning on master and on tempdb separately)

    • REVOKE SELECT on sys.databases TO public
    • REVOKE SELECT on sys.all_objects TO public
    • and so on....

    You can also DNY permissions to the role public.

    Note! don't make mistake, DNY and REVOKE are not the same. DNY adds/changes security permission, while REVOKE remove the security permission. REVOKE can be used to remove GRANT permissions or DNY permissions for example.

    Example/Demo Option 2

    -- do the same on the tempdb database
    USE [master]
    GO
    
    CREATE ROLE [RonenDatabaseRole01]
    GO
    ALTER ROLE [RonenDatabaseRole01]
    	-- create a USER for each LOGIN in the master and in the tempdb
    	-- add all your clients' users to the role here
    	ADD MEMBER [TestSecurityUser1]
    GO
    
    -- Add DNY permissions to the new ROLE for all entities you want. For example:
    
    -- DNY SELECT on system tables
    DENY SELECT ON [sys].[columns] TO [RonenDatabaseRole01]
    DENY SELECT ON [sys].[all_views] TO [RonenDatabaseRole01]
    DENY SELECT ON [sys].[all_objects] TO [RonenDatabaseRole01]
    DENY SELECT ON [sys].[databases] TO [RonenDatabaseRole01]
    DENY SELECT ON [sys].[all_columns] TO [RonenDatabaseRole01]
    DENY SELECT ON [sys].[all_objects] TO [RonenDatabaseRole01]
    -- You can DNY on SCHEMA or other entities
    DENY SELECT ON SCHEMA :: dbo TO [RonenDatabaseRole01]
    GO

    Conclusions: I totally agree that this is a security breach. In fact I showed how to use this information from the master database and the ability to create temp tables in order to crack the SQL Server security features. I wrote an article on the TechNet Wiki, and I published posts in my blog, and I lecture about this topic in English and in Hebrew in several User Groups and events. You can find the recording online, or the blog, or the article.

    With That being said, I do not recommend to change the permissions of the guest USER or the public ROLE.

     

    Off-Topic Note: by the way, in Azure Database the guest does not have permissions to CONNECT master, since this is not a real master but a logical server. This is more secure since you must have a USER in the master. If you create new LOGIN without a USER then you will get the error "Cannot open user default database. Login failed." since the master is the default database but the LOGIN does not have USER in this database.

    You can execute the following query on premises and on azure when you connect to the master as sysadmin, and see the difference

    SELECT dpr.name, dpr.type_desc, dpe.permission_name, dpe.state_desc, dpe.type, dpe.state_desc
    FROM sys.database_principals dpr LEFT JOIN sys.database_permissions dpe
    ON grantee_principal_id = principal_id
    where name = 'guest'
    GO

     


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]




    • Edited by pituachMVP Saturday, October 12, 2019 10:41 PM
    Saturday, October 12, 2019 5:28 PM