SQL Server grand Server level permissions and database level permissions to all new users, which is a security issue. In this post I will present some of the permissions which any new login gets and how we can revoke or deny these permissions. This post based on the question asked in the MSDN forum
and presents some theoretical options. Whether you should do it in production is another discussion...
In this post, I will only discuss the second part of the question, which is more problematic and worth a much deeper discussion😃
>> "Client would like to make sure that a non-DBA not assigned a system database causing a security breach".
This post is a bit long... I will write what come to my mind now, and I will organize it better in the future when I will have time, but this should give a good base to understand the available options.
Principals are entities that can request SQL Server resources. There are Server level principle (SQL Server authentication Login, Windows user, Windows group, Active Directory user, Active Directory group, Server Role) and Database level principle (Database User, Database Role, Application Role).
SQL Server provides 8 built-in server roles, which has fixed permissions.
In addition the server provides a built-in server role named "PUBLIC", which his permissions can be changed (granted, denied, or revoked). By default, the PUBLIC role has GRAND permissions "VIEW ANY DATABASE", and CONNECT permission to ENDPOINT (including "TSQL Local Machine", "TSQL Named Pipes", "TSQL Default TCP", and "TSQL Default VIA"). This mean that by default PUBLIC able to view any existing database and connect to the server using various endpoints.
Any new LOGIN which we create automatically get one SERVER permission named "CONNECT SQL", which mean that any LOGIN can connect to the server. In first glance you might think that this is the only permission a LOGIN has by default. In reality every SQL Server login belongs to the PUBLIC server role. This mean that by default any LOGIN inherits the permissions granted to PUBLIC.
In each database we can have a single USER which is based on specific LOGIN (there are other types of users as well). In each new database a new USER named "GUEST", which is based on the PUBLIC Server role, is created.
When you login to the Server, it first verifies LOGIN authentication. Next, if login succeeds, SQL Server checks whether your LOGIN has a database USER in the database that you attempting to connect.
If a USER exists then you get the GRANT permissions of the USER. If there is no related USER then you get the GUEST permissions. This includes the MASTER database! This mean that even if you do not have a USER in the MASTER database, anyone who can connect the Server can enter the master database as GUEST. Since the GUEST USER based on the PUBLIC LOGIN the USER has all the PUBLIC permissions and unfortunately by default the PUBLIC has many un-wanted permissions!
⛔ Note: Any login can see their own login name, the system logins, and the fixed server roles.
⛔ Note: You cannot disable access of the guest user in master or tempdb.
🗨 Several years ago when Microsoft added the new security features in SQL Server 2016, I published few posts about how to crack the new security features. There are also online recordings in English and in Hebrew of lectures which I gave in several conferences and user groups around the world. IT IS HIGHLY RECOMMENDED TO CHECK IT!
You can start with my post here, or check the recording of English lectures at PASS user group: part 1 here and part 2 here).
Why this is relevant to this post?
In order to improve my crackling, I used the fact that even a USER which did not get any permissions can connect the server, use the tempdb database to store data, and he can get information from system tables and other system entities which can help him to crack the data!
This is why this post is so important!
Best Practices: Revoke the guest user permission to access the user database if it is not required, by executing "REVOKE CONNECT FROM GUEST", on all the user databases.
✔ For more information about the permissions Granted to All Users By Default, you can check Brian Kelley post here.
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 DENY 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 DENY permissions to the new role.
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 1
-- do the same on the tempdb database
CREATE ROLE [RonenDatabaseRole01]
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]
-- 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]
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'
✅ It is HIGHLY recommended to read my post about: CREATE DDL TRIGGER ON CREATE_LOGIN to configure default parameters for any new LOGIN. The information in that post is directly related and can help you secure your database and do the actions we speak here, automatically on any USER/LOGIN.
👀Do you think this is all?
Actually this is only a test of the problematic default behavior of permissions.
This post does not meant to help people to crack the database but to secure (hack) the database and understand what should be done.
I hope this was useful 😀