Data Driven Security

Question

Data Driven Security

andre.quitta

SSCommitted

Points: 1921
More actions
July 30, 2014 at 12:07 am

#397137

Comments posted to this topic are about the item Data Driven Security

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

arthur.bekker SSC Rookie Points: 45 More actions · Answer 1

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 SSC Guru Points: 442467 More actions · Answer 2

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.

andre.quitta SSCommitted Points: 1921 More actions · Answer 3

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 SSCommitted Points: 1671 More actions · Answer 4

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 SSC Guru Points: 442467 More actions · Answer 5

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.

andre.quitta SSCommitted Points: 1921 More actions · Answer 6

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 SSCertifiable Points: 5407 More actions · Answer 7

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!