August 12, 2010 at 6:01 am
Currently trying to implement security on my database.
To implement row level security I basically decided to give my users only access to views and not any tables (see also one of my other posts, this part was easy, works like a charm).
In order to keep things as simple as possible I'm seriously considering to create a mumber of new schema's just for the views to grant select, update, delete and insert to my views in one go, instead of
specifically specifying this view by view:
CREATE ROLE [RoleA] AUTHORIZATION [dbo]
GRANT DELETE ON SCHEMA::[SchemaAView] TO [RoleA]
GRANT INSERT ON SCHEMA::[SchemaAView] TO [RoleA]
GRANT SELECT ON SCHEMA::[SchemaAView] TO [RoleA]
(No real names are used here, just for illustration)
Is this the best way to tackle things? I couldn't really find any info on this, but it seemed logical.
Is it OK to use separate schema's for just the views or would you just use REVOKE statements for all the tables? How would you guys apprach this?
I was by the way quite surprised that there is no standard way of getting this part scripted, very unlike most other database objects. I found a couple of scripts, but none of them worked for the full 100%. Somehow there seemed to be always something lacking.
Henk
August 12, 2010 at 8:05 am
If all the users only have access to views why not just make the db readonly?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 12, 2010 at 12:32 pm
Henk de W (8/12/2010)
Currently trying to implement security on my database...... just for the views to grant select, update, delete and insert to my views in one go
My question would be... if you are able to build security by granting privs on views, what prevents you to do the same granting privs on base tables?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 13, 2010 at 1:27 am
PaulB-TheOneAndOnly (8/12/2010)
Henk de W (8/12/2010)
Currently trying to implement security on my database...... just for the views to grant select, update, delete and insert to my views in one go
My question would be... if you are able to build security by granting privs on views, what prevents you to do the same granting privs on base tables?
During the (further) development of the database tables might be added. It is easy to overlook tables/forget to include them in your security plan. If a rule also suffices for further development, this makes things easier, less chance of making mistakes. In general I would like to keep things consistent and not any more difficult than strictly necessary. At least those were my thoughts.
On the other hand I do not like the thought to create separate schemas for views too much either, so I'm in doubt here, and that's why I posted this message.
Henk
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply