locked
Securing database (SQL Server 2012) RRS feed

  • Question

  • Hi all,

    I just got started installing SQL Server 2012.

    Now, I am trying to create a database and a user (called Test atm) which has permissions to alter the database in any way or form (except adding new users or changing security permissions). Naturally, it is critical that this user gets default access to any and all tables created inside the database by any other user.
    Also, I'd like to create some other users which only has access to modify data in specific tables (update, insert, delete), but not modify the table designs in any way (ie, create, drop or alter tables).

    However, I seem to be stuck at on how to achieve this. I have managed to create a Test user and connect to the database, but I am stuck on how to properly assign permissions on this.

    Any help would be appreciated.

    Saturday, April 14, 2012 10:55 AM

Answers

  • All users should have membership in db_datawriter and db_datareader. The first user, should also be member of db_ddladmin.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Athena Saki Saturday, April 14, 2012 12:17 PM
    Saturday, April 14, 2012 11:56 AM

All replies

  • All users should have membership in db_datawriter and db_datareader. The first user, should also be member of db_ddladmin.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Athena Saki Saturday, April 14, 2012 12:17 PM
    Saturday, April 14, 2012 11:56 AM
  • Thanks! That works perfectly.

    So, another question: how does one go about restricting users to specific tables? I figure that might be good to know, too.

    Saturday, April 14, 2012 12:18 PM
  • If you want users to be able to only access specific table, you should not add them to the catch-all group db_datareader, but you need to grant them access per table. However, since you might want the same users have the same set of access, it's better to create a role, add users to that role and then grant access to that role:

    CREATE ROLE myrole
    go
    ALTER ROLE myrole ADD MEMBER Helen
    ALTER ROLE myrole ADD MEMBER Patrick
    ALTER ROLE myrole ADD MEMBER Steven
    go
    GRANT SELECT, INSERT, UPDATE, DELETE ON this_table TO myrole
    GRANT SELECT, INSERT, UPDATE, DELETE ON thar_table TO myrole

    If you want to permit users to access table in a specific schema, you can say:

    GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::myschema TO myrole

    As the examples suggest, you can grant for instance only SELECT rights on a table.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, April 14, 2012 4:22 PM
  • Hi,

    One thing to be aware of, is that if you allow the user to have the db_ddladmin privilege then they quite easily elevate themselves to be a sysadmin in SQL Server.

    If at all possible, I'd suggest that you request the user supplies you, or the DBA, the TSQL code for any required DDL changes in the Production database and then you or the DBA test and apply it.

    If that’s not possible or feasible, the next best thing is to ensure that you have monitoring and/or steps in place to detect/prevent this elevation scenario.

    Saturday, April 14, 2012 8:53 PM
  • Ah, that is not such a good thing, is it?

    Then, perhaps there is a way to create a new role that allows for being admin of a database without allowing them to elevate themselves to sysadmin?

    Saturday, April 14, 2012 8:55 PM
  • Hi,

    Unfortunately, as soon as you start increasing a user’s privileges the risks to the data and SQL Server also increases.

    I understand it’s not always possible for the DBA to perform all elevated tasks and at the end of the day the only thing the DBA can do is rely on good monitoring, regular auditing and basically trusting your users.

    Here is just one example of what I'm talking about, which is well known, warned about in BOL and also applies to the db_ddladmin role http://akawn.com/blog/2012/02/why-you-should-be-cautious-with-the-dbo_owner-role/

     

    Saturday, April 14, 2012 9:20 PM
  • I am fully aware of security risks and the principle of least privilege. It is definitely something I will keep in mind when working with business databases and such (at least disable the ability for users to create triggers).

    It is probably also a good idea to stick to the least privilege principle as much as possible, and as such, even though they may be owners of a database, restrict the ability to say, create triggers, or other dangerous things that allow them to elevate themselves to sysadmins. Increased security is always good, right?

    At least, for my purposes, it is enough for them to have the ability to create, drop and modify tables.
    • Edited by Athena Saki Saturday, April 14, 2012 9:33 PM
    Saturday, April 14, 2012 9:29 PM
  • One thing to be aware of, is that if you allow the user to have the db_ddladmin privilege then they quite easily elevate themselves to be a sysadmin in SQL Server.

    Really? I don't even directly see how you can elevate from db_ddladmin to db_owner, but I have not analysed that in detail, so maybe that can be done.

    But from db_owner to sysadmin there is a long way - at least as long as you keep away from that TRUSTWORTHY setting on the database.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, April 14, 2012 10:07 PM
  • Yes minimal security is always best and Erland should hopefully be able confirm this, possibly not so well known, elevation scenario exists.

    Also remember, if a user doesn't require access to the SQL Server instance or even the server it is hosted on, don't give it. Don't be lulled into the false notion that read-only access to objects is secure, as I'm sure it's also known even an enabled login to SQL Server 'with no access to any databases' can with a few simple queries cause an outage to the SQL Server instance & possibly even the Windows server. No, I won't be saying how.

    As always, DBAs basically require good monitoring, auditing, maintenance, security policies and a solid DR recovery plan to fall back on, as these are the main things you can control.

    Sunday, April 15, 2012 1:49 AM
  • There is no denial: don't grant more permissions than necessary. And if you can keep users out from direct access to SQL Server, even better.

    Read-only access to data, means that they might be able to see data they should not see. And even with access only to tempdb, that's enough to fill up the disk or run the query from hell if you are evil - or stupid - enough.

    Then again, different environments have different requirements. On the server at work to which I connected to most of the time I run as sa, and soe does everyone else. Yes, it's a development server, and there is not much to protect. For instance, there is no reason to prevent people from changing table definitions, because what's in these database don't count anyway. What counts is what's in the version-control system.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, April 15, 2012 9:43 AM
  • All of your replies are awesome.

    You are all the best! Thanks.

    Sunday, April 15, 2012 9:56 AM