Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rinksrinks
Starting Member
1 Post |
 Posted - 2008-10-21 : 18:54:42
|
| Hello all, I need both advise and answer to my questions.Scenario: We use an application which is accessed by multiple domain users. A request was submitted to enable security and request was to break up security into groupings of “All rights”, “select/update/insert”, “read only.” Since the application uses SQL server 2005 as database, I thought it would be an easy task. However I was mistaken and part of the problem is the way how the application functions. Some info on Database:Database consists of a Schema and all objects used by the application are under this schema. My design idea:Create three database role names:OWNERSELECTSELECT_UPDATE_INSERTThen Under Database->Schema->PropertiesAdd each role and set their permissions accordingly. So for example, SELECT role, I could grant only SELECT, REFERENCE, EXECUTE permission and deny everything else. And reason I decided to grant permission at SCHEMA level is because it encapsulates all the objects without me having to select each individual object and grant it permission.However there was a problem with this and it is because of the way how the application works. When the application authenticates the user it performs a DELETE operation on a table called ARLOCK, which I believe is a way for it to source control, for the lack of better term. So, going to my example with granting permission for SELECT role, the user (added to this role) does not get authorized because s/he does not have DELETE permission. I also tried adding just the table on which the delete is performed and granted it DELETE permission. This also didn’t work. This table exists within the above schema I have talked about.MY question:Is there a permission priority such that I can grant permission at SCHEMA level, but overwrite permission at the table level? Is this even possible?? For example, Under Schema properties->securable ->grant “SELECT role” all permission except DELETE, UPDATE, INSERT. -> Add table from this schema and grant it DELETE permission. If nothing else works then I would have to go with the approach of adding each table individually and this is just too cumbersome. |
|
|
|
|
|
|
|
isolating permission schema and tables within sche