次の方法で共有

db_securityadmin is very powerfull and … dangerous.

  • [アーティクル]

Few days ago I worked for one client. He uses the following business model:

- dbo usually responsible for high level database design and maintenance;

- all database users organized in additional security groups for security purposes;

- dedicated person is responsible for user’s security maintenance, (s)he is able to assign user to explicit (one or more) application security group. This dedicated person is a department’s secretary, (s)he is trusted person, has only right to run simple SELECT statement on some tables, but (s)he is member of db_securityadmin database role.

Firstly it seems to me that security rules are appropriate and nothing can happen. Secretary is unable to maintain built-in database roles like db_datawriter etc.

Please login under account – member of db_securityadmin to check

SELECT * FROM fn_my_permissions(N'db_name', N'DATABASE')

you will get the following list of privileges (according to BOL).

-- ALTER ANY APPLICATION ROLE
-- ALTER ANY ROLE
-- CREATE SCHEMA
-- VIEW DEFINITION

But if you run

GRANT CONTROL TO <secretary> -- grant database control to self

and try to run

SELECT * FROM fn_my_permissions(N'db_name', N'DATABASE')

after that to ensure <secretary> now become dbo!

Please be careful to use odd database security groups. Also only trusted person should be assigned to this group. As for now (SQL Server 2005 member of db_securityadmin) equal to database owner.

Next article I will show you potential problems in more details.

Comments

  • Anonymous
    March 26, 2007
    When we should expect this to be fixed...?

  • Anonymous
    March 26, 2007
    I don't think this will be fixed soon (or at all). Security team responded that a member of "db_securityadmin" should be a trusted person.

  • Anonymous
    March 27, 2007
    They should try to "sell" it to business users, who will be concern about security of data... That's not the answer, they have to admit this is security issue and it needs to be fixed... Nobody should be able to elevate own privileges, otherwise it doesn't make sense, to have security based only on trustiness.. What could happen, if this account will be compromised and someone will be aware about this possibility of self elevating privileges..? I'm kind of positive they may fix it silently during next service packs or fixes..

  • Anonymous
    March 27, 2007
    Well, I'm not selling any tool to fix any issues or threats you have alread in your SQL Server environment.

  • Anonymous
    November 03, 2013
    This does not apply to SQL Server 2008R2.