- Feature Articles
- CodeSOD
- Error'd
-
Forums
-
Other Articles
- Random Article
- Alex's Soapbox
- Announcements
- Best of…
- Best of Email
- Best of the Sidebar
- Bring Your Own Code
- Coded Smorgasbord
- Mandatory Fun Day
- Off Topic
- Representative Line
- News Roundup
- Editor's Soapbox
- Software on the Rocks
- Souvenir Potpourri
- Sponsor Post
- Tales from the Interview
- The Daily WTF: Live
- Virtudyne
Admin
There's an awful lot of not knowing about MS SQL server here too.
First, xp_cmdshell won't even run on a default installation of SQL Server, since the SQL Server Agent must be running in the context of an actual account with specific privileges or it simply bails.
Second, you can't back up the database device files while they are attached or the server is active, since they are locked for exclusive access (not even the backup process can read them). Since this was a public server, there's no way to take the database down for backup.
Third, even if you used replication to another server, you'd still need stored procedures (or worse, administrative passwords in plaintext on the filesystem) to detach the device so the backup could run and reattach it after the backup finished.
So yeah, the WTF here was that Tris offered to install something that was removed for "security reasons". Luke, being a droid, could only respond the way he did, as anyone who was offering to circumvent security was an obvious risk.
The proper response for Tris would have been "I'm sorry, I can't do anything to help you. You're too stupid to deserve a backup."
Admin
Are you being deliberately daft? The vast majority of stored procs contain plain-old-sql: select, insert, update and delete statements. Exposing a limited set of plain-old-sql statements cannot be less secure than exposing arbitrary statements.
Admin
Indeed. This is surely the reason Tris was ordered to leave. He could have been arrested and interrogated. Luke did him a favor.
Admin
http://www.cases.tv/Rack_Monitor_Keyboard.jpg
Admin
That is true but isn't SQL server 2005 Shadow Copy Aware?
Admin
http://support.microsoft.com/kb/919023 Setting up volume shadow copy services for SQL Server 2005. This is a way to backup with no stored procedures and without the database passwords. In fact all that is needed is the user be part of the "Backup Users" group on the computer/domain.
CAPTCHA waffles
Admin
Bah, I just re-read my own posts and even I'm not sure what I meant any more. ;-)
So somehow I forgot that in addition to enabling CALL queries it is possible to disable all the other kinds of queries on many SQL server implementations. In theory you actually do get a security benefit with those servers if you strictly control the set of available stored procedures and disable other query types for all non-DBA roles, and if there isn't already some easier way to compromise the entire system.
In practice, regardless of the DB's capabilities, I build a gateway system between the DB and the outside world which implements a much stricter protocol than the typical ODBC command channel. In my experience the first security task is to isolate the DB server from user-facing servers, since most successful attacks in the past have been targeted at the server itself. Once that's done the SP vs. SQL debate is moot since the client can't directly communicate with the server to attempt either. Tasks requiring more privilege than the gateway allows are executed from machines on the DB server side of the gateway.
The "magically more secure" part is what I primarily disagreed with. All else being equal, switching to stored procedures instead of plain SQL isn't going to turn a convenience store into Fort Knox. It's more like switching from storing the store's cash in the register to storing the cash in a small wall safe in the manager's office. Neither is going to be effective if theives simply tear the safe out of the wall (get administrator access to the server and take over the DB process directly), or hold a gun to the manager's head until he opens the safe (subvert some random client machine on the LAN that the DBA uses and capture her password from the keyboard).
I should also point out that my experience with non-PostgreSQL servers may include a disproportionately large number of systems that have already been compromised. Use of SP didn't help very much in that group--SP either forced attackers to use exploits at higher privilege levels than the DB server, or the SP's were the security problem in themselves. I've seen a stored procedure on a live server with a single text argument whose typical values looked like "SELECT+%2A+FROM+reports+ORDER+BY+repdate+OFFSET+10+LIMIT+10". Guess what the stored query does, and where the text comes from.
PostgreSQL doesn't have an easy way to set an account such that it can only invoke stored procedures, much less only stored procedures from a fixed search path. You can create a variety of objects in PostgreSQL that grant users access to things they don't normally have access to. Views, for example, can expose data from tables that the owner of the view has read access to, but that the user executing SELECT against the view does not. Views don't have the search_path problem because of the way they are stored internally--their components are mapped directly to DB objects by numeric ID's so their meaning can't be changed by the user at run time. (The price for this is that the underlying tables can't be changed in several cases without first destroying the view.)
Privileged stored procedures (which are the only kind that matters in PostgreSQL, since an unprivileged stored procedure can't do anything the user couldn't already do directly with SQL commands) are incredibly hard to get right even with search_path set as the advisory suggests. There are still nasty traps, like variable type declarations in PL/PgSQL, which are bound to (possibly user-defined) type and operator functions prior to executing commands in the procedure.
Admin
No!!! NEVER backup a database by copying freaking binaries!!!! OMG!
You back it up by dumping it to a text file. Period. Want to save space? Compress the text file with gzip or something.
Admin
reminds me a friend who would say "nice to meet you, i'm gonna eat your liver" to incautious consultants
Admin
Frank
Admin
I don't understand the WTF here.. at all.
As engineers we have to work with the Corporate philosophies that are more often rooted in complete insanity... but that doesn't change the fact that if we wish to have food on our table, and as the insanity seems to grow geometrically each year, we have to adapt and match those expectations.
In this case, from the depiction- the consultant knew he was in an hostile environment, but at the end of the day, he had a job to do. This was to implement a backup scheme.
The stored procedures issue was a challenge and Tris' inability to adapt was the source of the expulsion. When dealing with banks and/or government, no matter how insane the corporate requirements are, a good engineer will work around them and provide the result they want.
In this case, the absence of stored procedures should have been queried rather than interjected... and when the answer to the query was due to security issues, the press to install them was also obviously the wrong answer. The rudeness in this case given the obvious animosity from the get-go I hang completely on Tris rather than Luke... especially given that Luke worked for an organization that the absence of stored procedures as being deemed "insecure" is likely just the icing on the cake folks like Luke had to work within every day. Government and Banks love to make any particular task 100 times more complicated and limited while, at the same time, pressing sky-high expectations on impossible timelines. This is the nature of the beast.
Tris just was simply too green to establish this and was a fish out of water. Most consultants are and make this same mistake... hence why so many consultants are looked upon with contempt vs. embraced. If Tris had this "stuff", he wouldn't be consulting but lodged somewhere making larger sums and plowing through challenges with ease.
How should this have gone? Upon the query of the missing procedures, the real result should have been. "Great, I have seen all I need to see and will now need to collect the remaining security and business requirements to implement the backup plan. Do you have a contact I may utilize for additional security issues as I implement the plan?" .. and brainstormed a mirror-split/offline mount/bulk copy/mirror-sync dump on a secondary server sharing the mirror (1 second downtime, no procedures needed).. and put a pricetag high enough to afford that dreamhome on the Riviera, including a nice sizeable kickback from the local hardware var for the secondary server.
Admin
Sounds like the NSA or maybe the bowels of the Pentagon, a DIA scene. That's what they are like. They are wooden, unfriendly, nasty people, many of whom are either just plain mean or in need of a lot of therapy to deal with their multiple psychiatric traumas from war or some other kind of government activities, or both. And, they're paranoid and highly classist, loving the fact that they "have a secret" and the rest of the world doesn't. They hate everyone who isn't exactly like them but of course would never admit it. They are awful people.
Why am I not in gov't contracting work anymore, specifically spook-shop work? This is why.
Admin
Tris can consider himself lucky and Luke to be the hero that he was saving him from a hellish contract.
Admin
If when they removed all the stored procedures their Web site did not crash, this means that everything was programmed with hard-coded SQL, which in itself is a security hole and plain bad programming.
Now, removing the system stored procedures such as sp_adduser, that is beyond me. Where's the security hole? Unless you are dumb enough to allow Web users access to these procs and to run SQL code through the Web site, ... ?!?
I think the kid is fortunate to have been shown the door.
I also think the big dude was pissed because an outsider was involved and because he must be over-protective of his crap.
Probably the Web developer himself, who thought he should have been the one to set up the backup and was forced to use somebody else.
Happens more often than you might think.
Admin
Yes, especially CDs with a Microsoft logo on them.
Admin
Thanks for bringing some sanity and knowledge to the debate.
Tris knew his stuff and offered a solution. Luke, if he acted the way he did, not saying "sorry, can't do that" or at least "please explain me why you want to reinstall the SPs" and "can't we back up without them?", is wrong.
Luke's employer should expect him to look for the best solution and therefore to not be so stubborn.
He was rude and unprofessional and his attitude will end up costing his employer.
Admin
Contractually, the dude got paid in full, right?
Admin
It's got what SQL servers crave!
Admin
Yeah, there seems to be an entire dialogue missing from this story. It is as though the person forgot at least 5 good minutes of conversation. There had to be some sort of back and forth prior to being ordered to get out. Perhaps something along the lines of: "I can resintall them." "We don't want you to." "Well, I can't do this job without them." "Why not?" " "Only an idiot would ask that question." "Screw you." "Yeah? Well, your mother's a whore." "GET OUT!"
Now the story makes more sense.
Admin
:( The second method is fine. It's always in a safe state.
Admin
Its not that he suggested change, its that he brought unauthorized software into the Secured Computing Facility. Probably walked past ALL KINDS of signs saying "No Unauthorized Media beyond this point..." And they weren't talking about reporters...