SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Data Driven Security


Data Driven Security

Author
Message
andre.quitta
andre.quitta
SSC Veteran
SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)

Group: General Forum Members
Points: 251 Visits: 395
Comments posted to this topic are about the item Data Driven Security
arthur.bekker
arthur.bekker
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 46
I find it strange that in today's day and age individual logins are being created. We used to do it ourselves, when we were in Sybase.

The easiest way to get around dealing with creating each user is to attach security to Active Directory groups. Since there's more to security than DB security, by having the users setup in AD groups, the UI security can also take that information and use it to display application options.

It's a great idea to keep the information in tables and run the scripts to provide the appropriate rights. By having AD groups, the inserts/updates to those tables will be rare and the scripts will run quicker as well. On the other hand, since there will be so little changes, the tables would not be necessary and scripts can be created with the SQL needed and will be updated once in a blue to add the new AD group that needed some specific rights.
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51364 Visits: 38684
Interesting article even though I have only skimmed it so far. I did read the introduction.

Only thing I saw as quickly scanned the code that I would change are the data types of your table. If you do any queries between that table and the system tables you will get implicit data conversions that could affect performance. Things like schema names, database names I would use the sysname or nvarchar(128) data types not a varchar.

What you wrote is actually timely out here in Afghanistan. Even though we are drawing down, we are finally implementing processes and procedures that the company will be rolling out to other sites worldwide that will make managing the different environments easier to support, especially as new people rotate in and out. Currently when experienced staff rotate home and new people rotate in we lose theater knowledge that we are now capturing and documenting. Yes, you would have thought this would have been done when manning was higher but it wasn't.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
andre.quitta
andre.quitta
SSC Veteran
SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)

Group: General Forum Members
Points: 251 Visits: 395
We also used AD groups in this implementation. The system is set up in such a way that any approach is possible. It can grow or shrink as needs arise.

Thanks for the feedback.
Andre
sarmistead
sarmistead
SSC-Enthusiastic
SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)

Group: General Forum Members
Points: 153 Visits: 602
Nice work; you've defined a mechanism for storing and managing server and database privileges in an environment-adaptive fashion. I imagine many of us can utilize this framework.

Now if only there was a proc to pull all existing security settings on an instance into that table (hint hint)

Steve
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51364 Visits: 38684
sarmistead (7/30/2014)
Nice work; you've defined a mechanism for storing and managing server and database privileges in an environment-adaptive fashion. I imagine many of us can utilize this framework.

Now if only there was a proc to pull all existing security settings on an instance into that table (hint hint)


Sounds like a good exercise instead of waiting for someone else to do it.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
andre.quitta
andre.quitta
SSC Veteran
SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)

Group: General Forum Members
Points: 251 Visits: 395
Agreed about scanning existing permissions. I had considered adding this to the article but it was already getting lengthy. Plus there are a ton of postings on how to do this.

What did they used to say in the old textbooks? "That will be left as an exercise to the reader."
Misha_SQL
Misha_SQL
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1190 Visits: 1009
I just love anything that's is data-driven since it allows for flexibility and centralization at the same time. Thank you for the article!



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search