0

I have an Azure Web App connected to an Azure SQL database. Let's call the app ACME. The application has it's own Role and User called ACME, and I gave that User db_owner principal roles. The authentication of the Web App is not Active Directory based, but it uses its own custom ASP.NET MVC based User and Role registry. Therefore the Web App creates Users and manages roles of those users, but those roles are technically not native SQL roles, but custom managed AspNetUserRoles. Seldom there are some schema changes and I perform the schema upgrades as ACME.

Recently the Vulnerability Assessment rules got changed and V2130 got changed: it accepted dbo as db_owner by default before, but now it lists it if it's not part of the baseline. ~~Last year only ACME got added to my baseline~~. Now I need to decide:

  1. Should I revoke dbo's db_owner rights? That makes me feel weird because it's such a core part of SQL that by default SQL doesn't even let you revoke dbo's db_owner rights. This is only possible if you have at least one other User who has such rights. In my case I have ACME.
  2. Should I add dbo as a db_owner into my baseline besides ACME?
  3. Should I add dbo as a db_owner into my baseline and curtail ACME's principals to not be so powerful as a db_owner. This is to adhere to the least privileges basic security rule. So should I downgrade ACME to for example to a db_ddladmin. I was thinking about DDL admin because of the occasional schema changes.

More generally: what could be the most secure and best practices (regarding User privileges and principles) for this common scenario?

Exhibit A: VA2130 query before 2020 December, watch the principal_name != 'dbo':

WITH UsersAndRoles (principal_name, sid, type) AS 
(
    SELECT DISTINCT prin.name, prin.sid, prin.type 
    FROM sys.database_principals prin 
        INNER JOIN ( SELECT *
                     FROM sys.database_permissions
                     WHERE type = 'CO' 
                        AND state IN ('G', 'W')
        ) perm 
            ON perm.grantee_principal_id = prin.principal_id 
        WHERE prin.type IN ('S', 'X', 'R', 'E', 'G')
    UNION ALL
    SELECT 
        user_name(rls.member_principal_id), prin.sid, prin.type
    FROM 
        UsersAndRoles cte
        INNER JOIN sys.database_role_members rls
            ON user_name(rls.role_principal_id) = cte.principal_name
        INNER JOIN sys.database_principals prin
            ON rls.member_principal_id = prin.principal_id
        WHERE cte.type = 'R'
),
Users (database_user, sid) AS
(
    SELECT principal_name, sid
    FROM UsersAndRoles
    WHERE type IN ('S', 'X', 'E', 'G')
        AND principal_name != 'dbo'
)
SELECT DISTINCT database_user AS [User], sid AS [SID]
    FROM Users
    WHERE sid != 0x01

Exhibit B: VA2130 query after the 2020 December rule changes:

WITH UsersAndRoles (principal_name, sid, type) AS 
(
    SELECT DISTINCT prin.name, prin.sid, prin.type 
    FROM sys.database_principals prin 
        INNER JOIN sys.database_permissions perm 
            ON perm.grantee_principal_id = prin.principal_id 
        WHERE prin.type in ('S', 'X', 'R')
    UNION ALL
    SELECT 
        user_name(rls.member_principal_id), prin.sid, prin.type
    FROM 
        UsersAndRoles cte
        INNER JOIN sys.database_role_members rls
            ON user_name(rls.role_principal_id) = cte.principal_name
        INNER JOIN sys.database_principals prin
            ON rls.member_principal_id = prin.principal_id
        WHERE cte.type = 'R'
),
Users (database_user, sid) AS
(
    SELECT principal_name, sid
    FROM UsersAndRoles
    WHERE type in ('S', 'X')
)
SELECT DISTINCT database_user, sid
    FROM Users
    WHERE sid != 0x01

An extra note: I'm aware of Should I drop dbo from the database role db_owner? however I think it's kinda negligent to just add dbo as a baseline and move on without thinking about and discussing the ACME User which many developers have in case of ASP.NET MVC and other apps.


Note: I was confused looking through multiple Vulnerability Scan items, and realized that ACME user thankfully does not have any kind of special database role!


Related to VA2108: https://github.com/MicrosoftDocs/azure-docs/issues/70391

1

As you saw my answer and Tibor's to Should I drop dbo from the database role db_owner? you shouldn't be too concerned about the legacy dbo User.

For your ACME User, I agree you should follow the least privileges security principal and therefore if it doesn't need as wide of access as a db_owner then remove it from that role and add only the roles or individual privileges that your ACME user does need.

So I would advise your third option as your path forward.

1
  • 1
    I got confused by looking at multiple VAxyz rules and fortunately ACME user is regular SQL_USER, doesn't have any special role like db_owner.
    – Csaba Toth
    Feb 11 at 20:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.