Hey guys!!!
In this article I would like to demonstrate how to improve the security of your SQL Server instances very simply and using a combination of Concealment and Access Restriction techniques (remembering that in Security we have 3 main techniques: Concealment, Access Restriction and Encryption) .
What I would like to discuss in this article is about the VIEW ANY DATABASE privilege, granted by default for role public, which as the name suggests, allows all instance logins to be able to view all databases that are created on it.
Through the Access Restriction technique we will remove this privilege from role public, so that through concealment, a potential attacker will not be able to identify the name of the instance databases, making the success of their attacks very difficult.
A very common scenario that occurs in everyday life is the same instance housing several distinct applications. You are worthless protecting your application and your bank using all good practices, if one of these server-sharing applications has vulnerabilities and the database connection user has the sysadmin profile, for example, or even has restricted access. but enter the name of all databases in the instance.
To demonstrate that this really exists, you can use the query below to identify users who have this permission in the bank:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT A.[name], A.[sid], A.[type_desc], A.is_disabled, B.[permission_name], B.state_desc FROM sys.server_principals A JOIN sys.server_permissions B ON A.principal_id = B.grantee_principal_id WHERE B.[permission_name] = 'VIEW ANY DATABASE' AND B.[state] IN ('G', 'W') |
Result:
Or if you only believe in practice tests, come on:
|
1 2 3 4 |
USE [master] GO CREATE LOGIN [teste_view_any_database] WITH PASSWORD=N'teste123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO |
I created the login "teste_view_any_database" with just the above command, without giving ANY permission to this user. I'll connect to the instance with him and let's see what I can do:
Without any permission, I was able to list all databases and their properties. Now I will remove VIEW ANY DATABASE permission from role public and I will test again:
|
1 2 |
REVOKE VIEW ANY DATABASE FROM [public] GO |
Now I will allow read access on one of the bases:
|
1 2 3 4 5 6 7 8 |
USE [dirceuresende] GO CREATE USER [teste_view_any_database] FOR LOGIN [teste_view_any_database] GO ALTER ROLE [db_datareader] ADD MEMBER [teste_view_any_database] GO |
Now I will test if the user can query the data normally in the tables and if he can query information from this database:
As you could see above, after removing this permission, the user is able to normally consult the data. What he will not be able to do anymore, is to consult instance information by the DMV's sys.databases and sys.sysdatabases, nor open the list of databases by SQL Server Management Studio or in Object Explorer:
Even WITHOUT VIEW ANY DATABASE permission, the login is able to use the USE [database] statement to switch between the active databases of the session in which he has access (CONNECT permission). When changing the session bank, you will be able to consult the data of this database in the DMV's sys.databases and sys.sysdatabases normally:
Importantly, if this user is the owner of the database (db_owner), he will be able to list this database even without VIEW ANY DATABASE permission:
However, I understand that for a user who makes queries at the bank, this can hinder and greatly reduce their productivity. So a compromise would be to remove this permission from role public and create a new role that has this permission, but only people users (no system users) are part of this role:
|
1 2 3 4 5 6 7 8 9 10 11 |
USE [master] GO CREATE SERVER ROLE [Acesso_ViewAnyDatabase] GO GRANT VIEW ANY DATABASE TO [Acesso_ViewAnyDatabase] GO ALTER SERVER ROLE [Acesso_ViewAnyDatabase] ADD MEMBER [teste_view_any_database] GO |
In many companies, systems often use logins with SQL Server authentication and people connect using AD authentication. In this scenario, administration can be made even easier by adding the default AD group “Domain Users” in the Logins tab and releasing the VIEW ANY DATABASE permission for that AD group. In this way, all users of people will be able to view the databases, but no system will have this access. The downside of this approach is that ALL AD users will have access to your database.
And lastly, it is important to note that I suggest not using DENY VIEW ANY DATABASE in the role public, since DENY overrides the GRANT permission and even releasing specific access for users, the user will not be able to list the databases.
Remembering that sysadmin users can list databases normally even with REVOKE or DENY of this permission for role public.
IMPORTANT: BEFORE removing this permission in production, TEST your systems multiple times to ensure they will have no impact. If your system needs to list the instance databases for any operation, it will certainly have an impact (unless it is db_owner, but then we have a permission problem, huh ...)
Well guys, I hope you enjoyed this post.
I believe that with small measures, when applied together, we can improve the security of our environment and make the “work” of possible invaders quite difficult.
A big hug and even more.
Good afternoon Dirceu, a command that helped me do something like this would be:
deny view any database to [user]
GO
use [database]
GO
drop user [user]
GO
change authorization on database :: [database] to [user]
Great tip.
I will add to the post