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

user_2332059 0
Viewed 4263

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.

mssql-jdbc azure-sql-database sql-server sql
3 Answers

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.

user_7938960

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.

user_105492810

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

user_24971610
Related