Worst Security hole I have ever seen
-
I have trouble with blanket statements such as "Stay away from public." In some, perhaps many cases, that is true. But sometimes it's not.
Public is used to set the minimum acceptable level of permissions. Some of our databases that are accessed through our intranet interfaces with MTS objects will have nothing or almost nothing for public selected. But I have a very secure client server database application that uses NT groups and Windows authentication. I use the public role for the base access stored procedures that return a group sum that the application uses to enable and disable menu choices, as well as all the basic list box fills.
Since no one is public who isn't authorized to be in the database, this works very well to establish the permissions that apply to everybody, and without which would have to be repeated in every individual group.
-
You always have an option of creating a role and putting the users (Windows groups still map to a database user) in the role and granting permissions to the role. The catch with public is that if you come across a situation where suddenly not everyone has to have access to something, then you're stuck and are re-engineering to build a similar structure any way (see Andy Warren's article for a good example). Also, you run into the case where if someone accidentally (or intentionally) enables the guest user, then anyone who has access to that particular SQL Server, regardless of whether or not they have explicit access to the database in question, now has access to said stored procedure or data.
In your case it sounds like you've thought it through. However, I cannot say using the public role will ever be considered a best practice. The fact that requirements change combined with the possible activation of the guest user is enough for me to create a role. And remember, you can nest roles if you need to.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley -
I'm one of those developer/systems analyst/help desk/DBAs in a very small office, and about the only areas I'm NOT involved in are web design and the network. So obtaining DBA skills are definitely constrained by my limited time, but nevertheless security is one of my most important responsibilities and I've made it a priority to learn as much as I could from books and this forum.
I do use the Public role, but grant it only select (read) access to tables, views, and Select-only stored procedures. Other roles, which are limited to subsets of people according to their jobs, grant limited insert, update, and delete privileges. Some of our people are in multiple roles, which makes it very easy to precisely grant security access by individual stored procedure. No one except me and the two other administrators have any sysrole rights to create tables or databases, etc. So far this has worked very well.
Incidentally, no one but the administrators has update, insert, or delete rights to ANY of our tables. The only way to change data is through a stored procedure; thus someone who is querying the database cannot inadvertently delete or modify any of the data.
quote:
Heros help; Bullies bomb. Stop the war in Iraq!
Dana
Connecticut, USA
Dana -
I think we can all agree that with a good understanding of the roles your assigning, security can be maintained. Whether you use a public role or create your own, so long as you are aware of what is accessible, any method that works well for your situation could very well be the "perfect solution".
I believe that the security hole is not the role itself, but how that role tends to be used. As with anything, if its used in an unintended method, or without the proper level of understanding of whats going on behind the scenes, it will be trouble.
-
Haha! I got you guys all beat! Wait, why am I acting like I'm happy about all this. Hmmm.
This is a recent project I walked into, and am still at. I manage(loosely) about 1.5TB of an Adhoc data mining and analysis system. It's outside the "scope" of IS control and IS hates that, but the department in question is extremely interested in "flexibility" and therefore...get ready for this...
...made everyone who has access to the server SA and db_owner.
Needless to say, I mostly corrected this. But there are still people(read: managers who think they are DBAs) that run DBCC commands and set databases to autoshrink, etc. Can't exactly tell your bosses they shouldn't be doing that stuff, I happen to like the job. 😉
Their reason for making everyone a member of the sys_admin role? Because they didn't want to mess with configuring the security....it was too complex.
-
I am mainly a developer who started out in DBASE, RBASE, and Paradox many years ago but I have been working as a DBA for about the last 5 years, though a great deal of my time is still spent doing development. I have a similar problem with my network Admin he uses 1 Windows account for all general workstations, managers being the exception. I have tried to point out to him that this is a BAD idea but he will not listen. We are a small company and he is also my boss and his boss, the CEO, knows enough about computers to turn on his machine on a good day. Any ideas as to a way to approach the argument for individual ID's.
-
Trying your original code, I think I have found a flaw in the sp_helprotect stored procedure! My setup is to never grant database level permissions for any login or group. I rely on the sa account (never the NT Account) for database creation. I do grant object level permissions to roles. now when I EXEC sp_helprotect NULL, NULL, NULL, 's' I receive Server: Msg 15330, Level 11, State 1, Procedure sp_helprotect, Line 346
There are no matching rows on which to report.
Shouldn't this stored procedure return an empty result set instead of an error?
Using the @permissionarea = 'o s' or 'o' works as expected.
What do you think, is it a flaw?
-
David, Microsoft as a rule uses RAISERROR to ouput progress data, and errors, even where there are no errors. As the sp_helprotect uses this method it will raise an error when it finds no data. It is not a flaw just another one of those infamous "Works as designed" situations. Your alternative of using the PERMISSIONS function is actually cleaner, just did not think of it at the time. thx.
Tim C.
//Will write code for food
Tim C //Will code for food -
Wow, light bulb just kicked in after re-reading your post a second time. By
using 's' only I was asking for only statement permissions. And since your DB
does not have any, the sp_helpprotect raises an error. Interestingly enough as
you pointed out when asking for the object permissions as well the error is not
raised. Instead you get at the top of your permissions :
Warning: Null value is eliminated by an aggregate or other SET operation.Then comes the object permissions....
Tim C.
//Will write code for food
Tim C //Will code for food -
Interesting topic!
The worst I have had and i own up to some of it being my doing was that fact that we had 12 logins set as SA. All 12 were active. The reason...these were all created in the early stages of me using SQL Server...Had no idea what I was doing. Oh and a colleague continually used sa within connection strings hardcoded into DTS ActiveX scripts as well as ASP pages or VB apps! Fortunately, that is no more. *PHEW*
I think I am in a good position with NT Admin. Basically we are not allowed to touch the server. Anything to be installed, patches, reboots have to be done by them...on the flip side, anything they want to do, they have to pass by me. So i pretty much know everything that goes on with the server....
I think if i was in a position where NTAdmin or Management dictated to me how the server should be run and allowed holes such as every user is SA or similar, I'd be digging my resume out and looking for a new position. At the end of the day, if anything goes wrong, it would all fall back on me!!
Clive Strong
Viewing 10 posts - 31 through 39 (of 39 total)
You must be logged in to reply to this topic. Login to reply