Worst Security hole I have ever seen
-
Ok since this is the title of the thread "Worst Security hole I have ever seen" I thought I would include on thing that gets more people into trouble than this but can also be compounded by this. That is creating a linked server with a login account with System Adminstrator or other power user rights. And setting with the "Being made with this security context" as the option. Now you just opened the remote server to everyone who can log in. That is just as bad if not worse than doing whith public what happened here.
Anyway, my shirnk said I should share, that it would help me let go of personal issues, so there you are.
-
In the UK 2 Years ago a SQL DBA could look for work in the £30K - £40K region.
Being a DBA meant something.
Today a SQL DBA will probably see jobs advertised at £25 - £30K.
Someone has looked at the A in DBA and associated Administrator with junior clerk.
In the UK the term DBA has become the computing equivalent of the VISA Platinum card. It used to mean something, now if you can read without your lips moving and can hold down a job you get one.
I have seen SQL DBA jobs advertised that say 1-2 years experience, knowledge
of Transact SQL, experience with triggers would be advantageous!
To the world outside the IT department DBA's have no status.
-
I agree with David re the Uk, but in saying that they are UK jobs advertised at the 40K/50K range and above, espically around London
Steven
-
Thanks for pointing this stuff out Tim C.
I agree with David that training courses are seriously lacking with regard to the security issues of MS.SQL.
After running the sp_helprotect, I notice that the public role has select permissions on a range of objects.
I'm confused (not hard!!). Should I create a new role, and add the users to this new role with it's appropriate permissions?
What should the Public role be used for?
Simon.
-
Andy Warren has an article on the site on why not to use the public role:
http://www.sqlservercentral.com/columnists/awarren/sqlpermissionspublicrole.asp
Best practice when you can do it:
- Assign Windows users to Windows groups.
- Grant groups login rights to SQL Server.
- Map group login to user in the database.
- Create a role in the database.
- Put the mapped user into the role.
- Assign all permissions to the role.
Public role shouldn't be used at all except when you have to. There are some examples in master where this is the case. The guest user is also present in master (and it needs to be there). The reason for this is that you don't typically want to give a user access to master... meaning all users come in through the guest user. The public role applies to everyone, including guest. Hence the reasons permissions are assigned to public in master. This is a workaround, IMHO, and I'm interested to see how Microsoft reworks this (if they do) in Yukon. Stay away from public. Far away.
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 -
quote:
Besides a blank sa password, recently I ran across one of the worst securityholes I have ever seen. While recently helping a dba at one of our clients I ran
across the fact that the public role in a database owned by sa had the following
rights :
Create Default
Create Procedure
Create Rule
Create Table
Create View
Backup Database
Restore Database
Effectively giving a user the rights to create a procedure that would be able to
call procedures in other databases that user did not even have rights to!
Thanks to all who contributed to this useful thread. I'm also one of those trying-to-be DBAs who can't go to a "real" training course. It seems every time I think I understand something in the manual about security, I try it and it blocks more than I thought it should--and I have to revert to giving people all sorts of permissions I really would rather not, because otherwise they can't even login. Situation is less easily managed because we have 150+ DBs (and counting), but at least there are only about 4 of us that can directly get into SQL Server--all of whom need a good amount of permissions.
Please help me out a little here. I'm trying to figure out what is the critical factor in your "create procedure" scenario. Leaving aside the backup/restore question, simply creating procedures, views, etc. doesn't by itself give you access to other DBs. Is it that the DB in question is owned by sa? Does that in itself give all the cross-database rights you're concerned about? If the owner were dbo, would that eliminate this one concern?
-
A good practice for assigning rights to users for an internally developed app is the use of Application roles.
An application role grants rights at runtime.
It works this way:
1. User starts app
2. User logs in with pwd.
3. app launches: sp_setapprole @rolename='App_Role', @password='password_encoded_in_app'
4. SQL server grants temporary rights to user connection.
5. When user opens another connection, the sp_setapprole needs to be launched again.
At our site, the PUBLIC role cannot do anything. All security is set on an app role.
Some users need read rights to create SQL statements on a subset of tables. We created a special role for this purpose, we did not adapt standard roles.
We do NEVER change the roles delivered by microsoft (Public, db_datareader, db_denywrite,...)
-
There are a couple of issues with applications roles that you need to be aware. Application roles immediately eliminate user context and login context permissions as soon as they are activated.
1) This means that you cannot map to another database as a user other than as guest. If you have to cross databases, you must assigm permissions to guest (or a role that guest is a member of preferably).
2) This takes effect for the connection... meaning if you are using OLE DB resource pooling and you have differing levels of permissions, you have a problem. Either you eliminate app roles or you eliminate resource pooling.
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 -
Here is a hole that our NT guru used to solve a printing problem. He made all the users administrators of the SQL Box to solve it and did not tell anyone. Low and behold one of the users decided to use Access to query some tables and when the screens were open deleted some records of of a few tables. Took over a week to find out what was happening. The NT guru thinks that this isn't really a big deal (even though my users can now add and drop tables, security, heck even drop the whole database and I can't stop them. I was told it was more important for the customer to be able to use the application
-
For those who may be interested, there are a couple of awesome security tools from NGS Software, one of which would have identified the breach discussed in this forum: Typhoon II, NGSSquirrel and SQLCrack. Just a reminder, Steve Jones has written about these before. Link: http://www.nextgenss.com
Bill Bertovich
Bill Bertovich
bbertovich@interchangeusa.com -
Timscronin,
Your admin is right, it's not a big deal... from his current flawed perspective. But point out to him that absolutely nothing is stopping any of the users from completing destroying that box.
Point him to the PSTools suite at http://www.sysinternals.com/ and specifically to PSEXEC, which allows you to do this:
PSEXEC \\MyPoorServer cmd
That puts you at a command shell on the server. What's to stop you from deleting (accidentally, mind you), a few critical files? Absolutely nothing.
Also point out that if the admin shares are still there on that server (i.e. C$ or ADMIN$), that any user can make that server his or her own personal file server... bad, bad, bad!
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 believe you are preaching to the choir, Brian. But I suspect such issues will be just as much ignored by this "NT guru" as previous arguments were.
Plain & simple, sounds like he's not going to listen until a REAL disaster happens--or, preferably, someone (our SQL hero perhaps?) finds another way to solve the printing problem so the customer can use the app. That's where the effort should be applied, not more arguing with him.
-
He's a perfect example of what I was talking about.......
Ask him what he intends to do when the disaster DOES occur, and remind him that since he knows about it ahead of time, it most definately IS his fault once it happens......I've seen people lose their jobs over much much less.
I believe in Smith's law. Smith's law states that Murphy was an optimist.
-
This is clearly an awkward position for a SQL DBA to be in: responsible for the databases on a server that you have no control over - worse yet, you know of security issues that you can't fix. Rather than light a cigarette, put on the blindfold and wait for the inevitable volley of shots, try this: put on your 'disaster recovery' hat and implement every trick in the book. Nightly backups (get those backup files to tape or onto another server!), transaction log backups every 5 minutes, maybe log shipping, etc. Basically think of the server as down. Now think about all the stuff you wished you had done to get it online quick and painlessly. That's where you want to be. This box [will] be compromised. Bet on it. Now put yourself in the best position you can to save as much as you can and restore it quickly. You may be called on to restore it from a week or a month ago.
It's not much, but if it saves the customer's data and gets them online again, you may get the opportunity to keep your job.
This may express the sentiment: I recently worked with a developer on a previously programmed app that mysteriously broke at a critical time. The developer was shoved into the crisis, found and fixed the problem. While walking down the hall together, I complimented him: "Nice catch" I said. He didn't even miss a beat when he replied "if the rock is big enough, it looks like you caught it."
-
The other thing you're going to want to do is get your concerns documented. Address a pleasant but focused e-mail to the admin indicating the risks as you see them with possible mitigation steps. Make sure your manager receives a copy of said e-mail. Keep that puppy safe. When something bad happens to the server, you've got evidence that said you attempted to address the issues as you saw them.
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
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply