July 26, 2011 at 1:17 pm
Hello,
Using SQL2K8R2 and we have a database with several schemas. This is the first time we've used schemas to segregate objects. Previously, everything was separated into databases.
The devs are requesting new logins for apps that will have EXEC rights only, no table access. I'm thinking the following should work, but want confirmation before proceeding:
1) All SCHEMAS have no permissions assigned.
2) All schema owners are set to DBO.
3) Each app login gets: GRANT EXEC ON SCHEMA::<SCHEMANAME> TO <Loginname>
4) repeat step 3 for each schema the login needs to access.
Is this the best way to handle this? Previously we use a database role to do this, but it looks like we would need many different roles to segregate access, so app login seems like it might be the best approach.
Regards,
Dan
July 27, 2011 at 1:08 pm
Dan explains a one-size-fits-all approach that I think is elegant and is the one I use and recommend unless "granular or complex" security requirements exist:
Keep Schema and Ownership Simple
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 27, 2011 at 1:15 pm
Regarding item 3: granting permissions at the schema level simplifies things significantly. If your requirements allow it I would do that rather than going to the object level.
Regarding item 4: by "role" do you mean "Database Role" or "Application Role"? If "Database Role" I would still look to grant all permissions to a "Database Role" as opposed to a "Database User", and just add the DB User to the DB Role.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 27, 2011 at 5:03 pm
opc.three (7/27/2011)
Regarding item 3: granting permissions at the schema level simplifies things significantly. If your requirements allow it I would do that rather than going to the object level.
That's the plan. Each application has it's own login and gets access to whatever combination of schemas it needs to function. At this point, all rights are EXEC and possibly REFERENCES only. No users will have direct access to the production databases.
opc.three (7/27/2011)
Regarding item 4: by "role" do you mean "Database Role" or "Application Role"? If "Database Role" I would still look to grant all permissions to a "Database Role" as opposed to a "Database User", and just add the DB User to the DB Role.
Previously, we created a Database Role that was granted EXEC, READ and WRITE. All application logins were assigned that role. The problem now is that there is no single role that we could use since each app requires access to different schemas. We may be able to simplify it further as we get a better handle on what schemas are "always required" and which are app-specific. The nice thing about this method is that changes to user rights easily fit into our change control process.
Regards,
Dan
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply