Worst Security hole I have ever seen
-
Besides a blank sa password, recently I ran across one of the worst security
holes 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
Create Procedure
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!,
OR backup the database, and copy it to their own system, and put it into a
server of their own control, etc..!!! What I found to be even more insidious is
that the DBA in question did not know that these rights had been granted to the
public role. I quickly wrote this little script to show me all the rights on
that server that had been granted.
SET NOCOUNT ON
EXEC sp_MSforeachdb
@command1 = "PRINT '' PRINT REPLICATE('*', 80) PRINT '
ROLES' EXEC ..[sp_helprolemember]",
@command2 = "PRINT '' PRINT ' STATEMENT PERMISSIONS' EXEC ..sp_helprotect NULL, NULL, NULL, 's'",
@postcommand = "PRINT '' PRINT 'SERVER ROLES' EXEC MASTER..sp_helpsrvrolemember"
SET NOCOUNT OFFI was wondering if anyone else had seen worse than this, either done by
sneakiness or a complete lack of understanding of security. Oh, btw, we quickly
removed those rights from public, and scanned any procedures that had been
created for calls to the security stored procedures in master, as well as cross
database calls. It does look like this hole was created innocently, but was
still a grave concern as someone with rights had obviously granted these rights
without a clear knowledge of SQL Server security.
Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food -
Wow. Not sure I've seen a worse one. Granting rights to public is BAAAAADDDDDD!
Steve Jones
-
I think people would be amazed at how often things like this occur though. I've seen similar things quite often, and when asking about them, find that the most common reason is not wanting to deal with setting up securities, or not understanding the roles that they use. One I remember in particular, was a situation where the company had a relatively inexperienced DBA, with no formal training, who simply assigned the public role to whatever the web app involved needed. He didn't understand securities, and admitted it openly, even to his boss, but the company was too cheap to train him, and he also made the comment that he wasn't going to spend HIS time learning it. He would learn at work, but of course, was always too busy putting out fires to spend time studying. When I suggested that his boss give him allocated time for this, I got a funny look from the manager, and he simply stated that he didn't have the resources to let his DBA have free time during work hours(can you believe he considered it free time?). There was just too much to do. This also happened to be the reason they brought me in for a three month stint, to clean up some messes that had been created along the way. It seems that all too often, so long as nothing is broken, they take the opinion that everything is fine. After all, it works fine, is the answer most given for why issues like this aren't taken care of immediately. Regardless of how thin the ice they skate on actually is, until it breaks (and is too late, most times) it's simply not an issue. Then when disaster strikes, they take the defense of, "I didn't know", or "It's not my fault", etc...
A great majority of the companies I've worked for had no "On Staff" DBA at all, but used a network admin, exchange admin, or NT admin to perform these functions, to keep costs down. In their minds, if the guy could keep the server running, and the vb guys could retrieve their data (mostly embedded sql statements), why did they need a dba? Sql Server is great at keeping itself going, and can work under some pretty amazing circumstances, and if a network guy knows how to map a login to dbo, or assign public rights to everything, then the developers can beat it up however they want, and it will just keep on ticking. Of course, a couple years down the line, they realize the mistake when disaster strikes, or their app becomes too large for SQL to compensate, but by that time, the whole app generally need a re-write to make it work right again, and then they have the costs to consider. Suprisingly enough, even then, I've seen companies put hundreds of thousands of dollars of new hardware in place rather than hire a DBA. It seems, it's easier to justify hardware than skill. Or recover from disaster than prevent it to start with. I have never understood it, but see it more often than not.
-
While the permissions assigned to public are absolutely atrocious, there are a couple of mitigating factors.
1) Unless the user is a member of db_ddladmin or db_owner, the user is only allowed to create an object with itself as owner.
2) If cross-db chaining is turned off (assuming SP3), the user has to have a valid user and the proper permissions in the database.
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 -
Although those are very good points, Brian, they do not apply to previous versions of SQL. Securities are cracking down and the situation as a whole is improving, but its been going on for years.......
-
Double Wow!
I have never granted any rights to public (and never will) but checked my servers using tim's code (great code Tim) to be sure (OK! phew!).
SQL security is a nightmare and I have found training couses woefully lacking in this area.
Scorpion's statement re no "On Staff" DBA is a good one. We had a full time DBA and when he left I got lumbered with the extra work (me a mere developer) what a steep learning curve (and still learning).
Far away is close at hand in the images of elsewhere.
Anon. -
Thanks Brian for allaying some of my worries. I am in Davids boat, a developer
who has often had to pull double duty as a DBA. Unlike the DBA wannabe in that
Scorpion refers to in his post(nice post btw), I do try very hard to learn (even
on my own time
) as much as I can about what I work on. Even though I havebeen working on SQL Server since 6.5 I in no way consider myself to be a DBA,
just a learned journeyman. Yet the person who was managing that DB with the
security hole was their full time DBA. How could they not have found this? I
also confirmed that even with cross database ownership chaining turned on, they
would not have been able to call any of the master security stored procedures,
as they verify membership in the "securityadmin" using
IS_SRVROLEMEMBER('role'[,'login']). Yet they still would have been able to
select out of any other supposedly secure DB, including a hypothetical one
holding credit card information.
Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food -
Nice post Tim. I too have to learn in my own time as well (VB, VC++, TSQL, HTML etc) and been using SQL since 6.5. I think you probably undersell yourself in thinking your not a DBA, technically anybody that administers DB's is a DBA (wait for the flack on this one) it is only experience that differs. I call my self a DBA although I have no qualifications for this nor do I have DBA in my job title or JD. I look after and administer 4 SQL servers and I reckon I'm a DBA!!!!
Edited by - davidburrows on 02/07/2003 08:34:46 AM
Far away is close at hand in the images of elsewhere.
Anon. -
Thanks David, but there are too many things I do not understand. I am a nuts and
bolts person, and like to truly understand how the internals of things work.
There are too many things about DDL, DML, the query optimizer, etc... that I
just don't know enough about. I am not knocking you or anyone else who calls
them self a DBA, but when I read the SQL mags, and several of the articles make
me scratch my head (Imagine yourself at the zoo watching a 6'3 300 lb gorilla
scratch his head) I realize my shortcomings. Several of the posters here also
impress me with their knowledge, and until I can refute, dispute, or agree from
a learned level with anything they say, I will continue to call myself a
journeyman DBA. That does not mean I will keep quiet and not debate with them.
I also hold in high regard anyone like yourself that has the drive to learnon their own something new. Give me 1 person who has drive and wants to learn. I
do not want the lazy person with the ???????? degree. There are too many people
like the one in Scorpions post that inundate the computer industry with poorly
designed, badly written, and incompetently implemented projects. Forgive me for
that last rant. But then, how would the rest of the diamonds shine, if there was
no coal?
Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food -
Thats great and speaks a lot about yourself and your honesty. As I explained I call my a DBA in title only, I too am only starting on this long journey and have a very long way to go. I wish too that I knew a lot more about the topics you mentioned and they scare the pants off me! I suspect your right in that there are probably a lot of people who call themselves DBA and have little or no knowledge. I agree with you about the posters on this site but I suspect they have a lot to learn as well and I don't think they are arogant enough not to agree.
ps It's difficult to soar like an Eagle when you work with Turkeys.
Far away is close at hand in the images of elsewhere.
Anon. -
LMAO......
In my humble opinion, the BEST kind of DBA is the one who says, "I don't know." when in fact he doesn't, and then goes and researches whatever it is until he/she gets the understanding to say, "Now I do.", whether they are a network admin, NT Admin, whatever......
I've seen DBA's who didn't deserve the title, and Network Admin's who DID, but didn't have it.....etc....and the mere fact that you guys are here, looking for discussion and advice, and taking time to learn about it, classifies you as Good DBA's in my book. (for what little that means....)
As long as I've been working in the DBA field, I find things daily that I either didn't know, had forgotten, or simply was wrong about. It would be easier for me than most to say, "I've seen it all", but we all know that couldn't be true. At least those of us who work with it every day, anyway.....and I think that the day I quit learning new things about SQL, is the day I need to change careers.....
-
And since we're on the topic... just take a gander at what the public role has access to. You'll be surprised. One of the things I've written recently to identify what stored procedures public has access to based on a particular system table (or actually, any table):
-- Replace the value in @@TableName to point to the table you want info on
DECLARE @TableName sysname
SET @TableName = 'sysobjects'SELECT DISTINCT USER_NAME(so.uid) [Owner], so.name [Stored Procedure]
FROM sysobjects so
JOIN syscomments sc
ON so.id = sc.id
JOIN syspermissions sp
ON so.id = sp.id
WHERE so.xtype = 'P' AND
sc.text LIKE '%' + @TableName + '%' AND
sp.grantee = USER_ID('public')
ORDER BY Owner, [Stored Procedure]It's doing a LIKE and isn't doing any sort of parsing, so there's bound to be a few false positives, but you get the idea.
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 -
Brian this does close to the same thing as your query, but does not limit the search to stored procedures, if that was your goal. :
EXEC sp_helprotect @username = 'public', @permissionarea = 'o'Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food -
Sorry, ran your query, and too another look at what you were ouputting. You were not displaying just what stored procedures public had access to, but ones that queried or used the table sysobjects. Came up with quite a few on my system....
Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food -
Right. Based on a particular system table. You specify the table with @TableName.
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
) as much as I can about what I work on. Even though I have
I also hold in high regard anyone like yourself that has the drive to learnViewing 15 posts - 1 through 15 (of 39 total)
You must be logged in to reply to this topic. Login to reply