Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Building a Security Philosophy


Building a Security Philosophy

Author
Message
Lian Pretorius
Lian Pretorius
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1317 Visits: 1250
We give datareader access on the read-only reporting db to some staff who need to run ad-hoc queries. None of our business logic is in the database (Views, stored proces, etc) - So execute rights is probably not applicable.

Setting up a roll to view specific tables (just over 1000 - 1 schema) can be a bit tedious, but after Steve's post it makes a bit more sense - Will investigate a bit further now...
ALZDBA
ALZDBA
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6972 Visits: 8839
Ross McMicken (8/12/2008)
Do you give sysadmin rights to a DBA's regular LAN ID? We only grant admin rights to special ID's that aren't used for anything else. That prevents a bunch of mistakes, and provides the appropriate SOX cover as well. We make sure that no ID has admin rights on both a test/development box and a production box, which prevents anyone from accidentally running tests against a production database. We also have a separate acceptance environment that is set up hte same as a production box, with limited access for developers and support personnel. All actions on the aceptance environment are done by a DBA/Server admin just as would occur on a production box. This ensures that installs and upgrades to apps and databases will work. Nothing goes straight to a production box - all changes must go through acceptance first.



FYI we have at least 3 windows userid per DBA/Sysadmin person.
1) regular ID (mail, ... and sysadmin for DEV)
(even for dev plans are to use a separate win. userid)
2) QA_admin windows user
3) Prod_admin windows user

All activity is logged for SOx related servers.

cfr my article: http://www.sqlservercentral.com/articles/Security/3203/

Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution" :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Andy Warren
Andy Warren
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: Moderators
Points: 7225 Visits: 2679
Ross, I tried multiple ID's and the only way it worked (for me) was to have separate machines, one logged in as powerless me, other as SA me. Trying to switch back and forth just annoyed me. Part of being a DBA is never working without a net - thinking before executing and having a fall back plan.

Not saying I recommend this approach, but its worked for me better than the multiple ID approach.

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Andy Warren
Andy Warren
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: Moderators
Points: 7225 Visits: 2679
David,

I like the approach, but be open to other options if it seems like that is what the company needs. For OLTP as far Im concerned it doesnt get any better than only using SP's for data access and only granting read only on the minimum tables needed to build dynamic queries.

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Ross McMicken
Ross McMicken
Old Hand
Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)

Group: General Forum Members
Points: 388 Visits: 2195
Andy Warren (8/12/2008)
Ross, I tried multiple ID's and the only way it worked (for me) was to have separate machines, one logged in as powerless me, other as SA me. Trying to switch back and forth just annoyed me. Part of being a DBA is never working without a net - thinking before executing and having a fall back plan.

Not saying I recommend this approach, but its worked for me better than the multiple ID approach.

Our usual method for priovileged access is to use a Terminal Server session to do the work. We login to Citrix with the alternate ID and do whatever we need to do. That works well, and minimizes the risks as much as possible. There is a separate TS farm set up just for this sort of work.

Another option is remote desktop connections to tools on the server itself. We are on SQL 2000, so I don't know how that would work on a later version of SQL Server

I can understand the difficulties with two machines, or having to log in/out just to do a simple task. That was one of the drivers behind our TS and RDC options.
JJ B
JJ B
Old Hand
Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)

Group: General Forum Members
Points: 327 Visits: 2846
My favorite line in the article is "...they don't get the job unless I trust them." I know I take that in a different way than the author meant, but it is a great reflection of my security philosophy. I'm not a lazy programmer. I just firmly believe in a level of trust that is beyond what many "security" people believe in.

I'm the only developer/DBA in my "shop." I gave it careful consideration and decided I should have all the privileges I needed to do my job. :-) I trust myself a great deal.

The rest of security is a matter of figuring out permissions to give to my users. At present (this is expected to change in the future and so I appreciate learning other approaches), the only apps I have are desktop apps and the only users I have are employees of our agency. The employees of our agency generally do not have tools that let them directly access the databases. They only access the data through the front-ends that I provide.

We use windows authentications. Domain users are assigned to windows groups and those groups are assigned as server logins and then database users. The groups are assigned to a small set of roles that I create for each database. The roles are usually along the lines of RegularUser, ReadOnlyUser, PowerUser. The permissions are assigned to the roles for tables, stored procs, functions, etc.

I DO assign rights directly to the tables via the roles as makes sense for the application. I've yet to read an argument (and I've read and considered and re-considered them all again and again) that has convinced me that my strategy *for my environment* is an unacceptably insecure one. They are our employees. If we didn't trust them, we wouldn't hire them. That doesn't make me foolish. I know employees can go bad and/or make mistakes. (But hey, so could that junior DBA that was talked about in the article.) My users do not get administrative privileges. They just get access to the data that they need to do their jobs.

I don't use any of the built-in rolls. I prefer to define everything myself for a small set of user-defined rolls for each database.
Anipaul
Anipaul
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6275 Visits: 1407
Nice article...



david.gerrard-604066
david.gerrard-604066
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 63
Note I said "for the web"... I'm the fella here who has the happy job of monitoring the ever increasing volume of SQL Injection attacks that hit our sites every passing day. Sad

In general I'd work on the first principal that an account being used by a website shouldn't be allowed to do anything at all, and then relax it from there, if it's really necessary. But again - the first principal ought to be: "I'll need a lot of convincing that it's really necessary".

However - I do like the line I just read (regarding desktop apps) that said "if my company hired them then they must be reasonably trustworthy." That shifts the blame where it really belongs... The HR / Personnel Department!
JJ B
JJ B
Old Hand
Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)

Group: General Forum Members
Points: 327 Visits: 2846
Note I said "for the web"


Exactly! I think I will be tasked with creating a web app in the not too distant future. At that point, the game will change, and I will be using a very different approach to security. I appreciate learning about "web security" because that is a different world than I currently live in. I wouldn't trust a web user as clearly as I could see her.
timothyawiseman
timothyawiseman
SSC Eights!
SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)

Group: General Forum Members
Points: 800 Visits: 920
Steve Jones - Editor (8/12/2008)
I'd agree with Andy. The issue I have with datareader is that it automatically gives rights to all tables. So if I add a table to store anything, meta information about your database, performance, perhaps at the request of someone to store something else, everyone in that role gets rights.

It means you're providing automatic access, and you might not want to. You should explicitly grant a role access if you want it, not have security setup to do the grants for you. That's the mindset that gets people into trouble.



I would respectfully disagree about datareader (though I'm wary of datawriter). While you should only use it when you know you want to person/group you are giving it to to genuinely be able to read all tables within a database including those that do not exist, there are definitely occasions when this is appropriate.

One example is if you have a developer that can be trusted with all information in the database (or if the information is not particularly confidential to begin with), they would have full rights on the development server and then datareader on the production version. Having datareader makes it easier for them to troubleshoot if there is an error or a question. This is especially relevant if the application was a rapid development project and may need to be put into in house use with less than complete testing. Of course, you could readily argue that this should not happen, but there are times when it is unavoidable.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
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