Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am working on a DB where almost every technical user has full read / write permissions on all tables. We are working towards removing permissions, gradually, from certain users, and I had hoped that implementation of some new code on a new schema would be able to be strictly implemented from day 1.

However, I have realised today that most of our users have been granted db_datareader and db_datawriter role membership, which means that they all have access to this new schema by default. Therefore, I want to find a way forward that gets this schema secure without creating a load of additional work.

My idea is:

Create a new 'generalReader' role which has SELECT privilege on our huge dbo schema, and a couple of other schema we have (but are not being changed right now);

Create a new 'generalWriter' role with UPDATE, INSERT, DELETE privileges on the dbo and existing schema;

Drop the db_* roles from users, and replace them with the new generalReader and generalWriter roles;

Add new roles to control access to the new schema - I think most people could be allowed to SELECT from it, where only a few should be granted INSERT privileges. (e.g. I would create an LoanAccountReader and LoanAccountInserter roles)

Does this sound like a broadly appropriate plan to move forward - or am I missing some tricks or some security issues?

Before coming up with the above, I did some experimentation with roles and could create roles DENYing write access (that overrides the database writer role), but could not then re-enable that permission with a 'writer' role.

thanks - it was actually your answer on one of those links "Personally, I don't really use DENY. Here's why:..." that lead me to realise I needed to do something with the db role assignments.
–
NijFeb 24 '12 at 10:30

@Nij you're welcome. Good to see security being taken seriously!
–
gbnFeb 24 '12 at 10:34

Also, one note - an explicit DENY at any level of the security heirarchy will always override anyGRANT. This is different to a REVOKE, which nullifies a GRANT but doesn't do the "Haha, no, you're not getting that. Ever." effect of a DENY. (So you could have a GRANT SELECT at the database level, a REVOKE SELECT at the schema level, and a GRANT SELECT again at the table level - that'd give you select on that one table, and everything else in the database not in that schema (everything else in the schema aside from that table comes under the REVOKE). If you replaced that REVOKE SELECT with a DENY SELECT then you'd lose select access to the entire schema, no exceptions*.)

gbn's answer here is a wonderfully concise intro to the various permissions you can apply to a single database object (note that some of them don't necessarily apply to all objects, the most obvious one being REFERENCES). For an overview of permissions from the domain level down to the individual objects within a schema, see here.

(* I lied, there is one exception: Ownership chaining. Permissions are not checked in the case of an ownership chain. In the simplest case, if Bob grants Jane select access on a view that he owns, and that view only references tables that Bob owns, then Jane's permissions on those tables are not checked, because the permissions are chained from the view to the underlying objects (nb: ownership chaining can get a lot more complex than that). Ownership chaining is incredibly powerful and incredibly useful, but it also has an incredible power to bite you in the ass if you're not careful. If you're running an environment where precisely defined permissions are critical, then always test your permissions before you deploy them.)

(* I lied again, there's one more exception - db_owner [and users aliased to dbo] and sa_role (which is effectively db_owner on every database, plus server-level stuff). Permissions are never checked for these, it's assumed they have full rights to everything.)

Also, as gbn noted, if you're going to use schemas, then you may as well go the whole hog and permission the schemas rather than individual objects within a schema (the primary use case for schemas is for grouping objects with the same permissions together so you can manage permissions on one thing - the schema - rather than a whole bunch of objects).