'dbo' user should not be used for normal service operation

Question

When I scan my database, it shows one of the result like VA1143 'dbo' user should not be used for normal service operation in A Vulnerability Assessment scan

They have suggested to "Create users with low privileges to access the DB and any data stored in it with the appropriate set of permissions."

I have browse regarding the same to all form but cannot get the correct suggestion yet. Could you please suggested your idea or where i have to create the user and grand the permission. Since we have only one schema structure in our DB.

Streamy

Does your application use a user that is in the database owner role? I imagine this is what it is flagging. – Presa

Yes, we have only one user and he has all the permission to all the database – Streamy

Answer 1

About "Create users with low privileges to access the DB and any data stored in it with the appropriate set of permissions.", the first thing you should know is the Database-Level Roles.

Create users with low privileges means that the use does not have the alter database permission.

When we create the user for the database, we need to grant the roles to it to control it's permission to the database.

For example, bellow the the code which create a read-only user for SQL database:

--Create login in master DB
USE master
CREATE LOGIN reader WITH PASSWORD = '<enterStrongPasswordHere>';

--create user in user DB
USE Mydatabase
CREATE USER reader FOR LOGIN reader;  
GO
--set the user reader as readonly user
EXEC sp_addrolemember 'db_datareader', 'reader';

For more details, please reference:

1. Authorizing database access to authenticated users to SQL Database and Azure Synapse Analytics using logins and user accounts

Hope this helps.

Screwworm

Thanks, It resolved the issue. I have another one question, When duplicate this database (means copy paste) role permission also will carry? – Streamy

Actually, when migrate / copy database, we need to re-create the use role permission. Database migrate documents all mentioned this, such as DMS/DMS, even export to the dacpac file, – Screwworm

We have multiple database in Elastic pool,Possible to apply this role to all the DB means when create new DB or possible to create the User from C# using entity framework – Streamy

Hi @Manikandan, I'm sorry. I'm not sure if it's possible, I haven't tried that before. – Screwworm

Answer 2

When designing and building databases, one the principal mechanisms for security must be the "least privilege principal". This means that you only give permissions that are absolutely necessary. No application should need to be the database owner in order to operate. This role should be highly restricted to only administration types. Instead, you create a more limited role for the application. It can include access to every single table, all the procedures, but it won't be able to do things like, for example, drop the database.

This is step one to a defense in depth of your system in order to properly and appropriately secure it. It helps with all levels of security issues from simple access to SQL Injection. That's why it's included as part of the vulnerability assessment. It's a real vulnerability.

Komsomolsk

thanks for the update, I am just checking the sample code for create least privilege role in DB, Could you please suggest any one – Streamy

I don't know what you mean, I'm sorry. You create a ROLE other than DBO. You assign permissions to that role. You add users to the role. That's it. You may create more than one ROLE with different permissions. The core concepts are simple and clear. It's just work to define the stuff. – Komsomolsk

Answer 3

Yes resolved the issue after creating the least privilege role and assigned to the user. But its leading to different below vulnerable issue's for the newly added user with least privilege role. Any lead will be helpful on this

1.VA2130 Track all users with access to the database 2. VA2109 - Minimal set of principals should be members of fixed low impact database roles

Anarchism

Hi=) Did you follow up on this or just added the new least-privileges user as baseline? Because I got the same VA after resolving the one in the title so I don't know if I have to just ack them or I created the user in the wrong way :D – Infectious

Answer 4

Note that on a newly created clean database this rule will fail until additional roles are created.

This is from the list of all volunerability assessment rules - head to issue # VA1143.

I work in a small company, had to make myself an administrator of an Azure Server, and received the "Vulnerability Assessment scan results" to my email. So yes, the newly created databases which nobody has access to yet - all return this error. And the scan was done couple of days ago, that's why it returns an error, even for the database which as a role of "Read Only on a Specific schema".

Mitten