The Politics of SQL Security

Question

The Politics of SQL Security

quackhandle1975

SSChampion

Points: 11055
More actions
May 22, 2016 at 10:09 pm

#310784

Comments posted to this topic are about the item The Politics of SQL Security
[font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

sqlservercentral 79667 SSC Rookie Points: 42 More actions · Answer 1

I find an application developer that agrees with me and we tackle one thing at a time. It's slow but at least there's progress.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 2

I simply ask if he'll enjoy reading about himself in the morning news after the company has suffered a breach. I also explain that customers can and do sue when breaches occur and that it's really difficult to keep and get new customers after such a breach has occurred.

They usually come around.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Kyrilluk Ten Centuries Points: 1269 More actions · Answer 3

Jeff Moden (5/23/2016)
I simply ask if he'll enjoy reading about himself in the morning news after the company has suffered a breach. I also explain that customers can and do sue when breaches occur and that it's really difficult to keep and get new customers after such a breach has occurred.
They usually come around.

If an application has an obvious security hole (such as the sa password hard coded in the application or stored proc that can be SQL injected), is not possible to sue the software provider?

Yet Another DBA SSCarpal Tunnel Points: 4299 More actions · Answer 4

Find someone who does listen and can implement the changes. Which can take many months of many, many small incremental changes.

And keep all the emails about the advice given so that you can at least defend yourself if it ever gets to HR!

Terje Hermanseter SSCarpal Tunnel Points: 4851 More actions · Answer 5

Everything in this editorial sounded all too familiar..

quackhandle1975 SSChampion Points: 11055 More actions · Answer 6

Thanks for the comments, and yes for DBA's it does sound all too familiar. At one client I used to actually wake up and think: I wonder if today's the day we get hacked?

Even though it wasn't security related (on the face of it), one application I worked with the top 10 queries were all select * ... some were running 100's of times an hour! The CTO even came to my desk to ask why users were not seeing decent performance, so I show him the offending statements, he tells me to sort it out, so I show it to the app company's in house developers - nope, that's not our fault, they tell me, it's your SQL Scripts/Network/Users/Bad lighting/Coffee that's causing the slowdown. Again politics! :w00t: 😉

qh

[font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

RonKyle SSC-Dedicated Points: 31556 More actions · Answer 7

I'm with you on all things but the 1433 port. I could easily be convinced, but could someone explain that more or point me to a source that can. That port is set on installation. I can't ever recall being asked to set it to dynamic.

As far as the rest, I guess I've been lucky not to have such stubborn clients or companies.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 8

RonKyle (5/23/2016)
I'm with you on all things but the 1433 port. I could easily be convinced, but could someone explain that more or point me to a source that can. That port is set on installation. I can't ever recall being asked to set it to dynamic.
As far as the rest, I guess I've been lucky not to have such stubborn clients or companies.

No, I wouldn't set it as dynamic. Just choose a number (preferably five figures or more to avoid conflicts with existing ports) and use that. Use a different number for each instance in your estate.

John

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 9

sqlservercentral 79667 (5/22/2016)
I find an application developer that agrees with me and we tackle one thing at a time. It's slow but at least there's progress.

That makes sense.

terrance.steadman SSCrazy Points: 2097 More actions · Answer 10

Greetings,

I had a similar situation in a company I had worked for. I had inherited a web application with a SQL server back end. The web application was in ASP Classic with scripts hard-coded in plain text in the web page as well as including a host of other issues. Including the *gasp* "SELECt * FROM..." and the "oh no" sa account with the password hard coded in plain text. Oh yea, did I mention the reports that had a run time long enough to go out for lunch?

I was blessed though to at least have a good boss that let me "tune" the system the way I felt it should be. After a few years of work, the web application was rewritten from the ground up and no admin access was available at all. It only used windows groups for access to the database. It allowed a much easier way of disabling a specific person's account without causing extra work for the database itself. All scripting was done in classes handling the work load and the web pages were stripped down to pass information back and forth. All scripts were strongly typed so only the needed information was passed on.

The reports were also redone so they would run faster and more accurately.

In the end, they would complain if the reports took longer than 10 seconds to run and a 30 second delay anywhere was cause for a trouble ticket. Nothing like management to always want a bit more. 😛

Take care and have a great day.

Rod at work SSC-Dedicated Points: 34007 More actions · Answer 11

As a developer who was part of a team in the past that liberally hard-coded the 'sa' account and its password in lots of applications and all reports, I've got to plead guilty. All I can say is that, when we were doing it we didn't know anything about security, especially when it came to databases. Yes, we were hard-coding sa all over the place because it was convenient. It was only after the IT manager let and I was the senior developer, that I began changing application access and reports to a normal user account. And it probably took up to a year to finally root out all of the places sa was used in apps and reports. But we finally got it done.

Kindest Regards, Rod Connect with me on LinkedIn.

Eric M Russell SSC Guru Points: 125561 More actions · Answer 12

When dealing with unreasonable requests coming from a position of higher authority, your first response should be to object. But remember to do it in a friendly and helpful way, and also do it one-on-one in private, because you don't want to question the CIO's judgement or technical expertise in front of other coworkers, and also he or she will be much more receptive one-on-one.

Also, followup the conversion with an email reiterating your objections. Again, don't CC anyone else. The purpose of this email is mostly CYA, so you'll want to FW a copy of your email from your Sent folder to your personal email account as well (becase in the event of a data breach you may be unceremoneously cutoff from your corporate email, and you'll need a copy for your defense). Normally a DBA shouldn't be concerned with CYA, but when it comes to database security, consider what's potentially at stake. YOUR name, not just the name of the organization or the CIO, could end up in the media.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Wayne West SSC-Insane Points: 22586 More actions · Answer 13

So many developers have full admin access to their workstations that you end up needing admin access to run the bloody stuff. First rule should be to make the devs run at the reduced privilege level that normal users have to work under.

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

Eric M Russell SSC Guru Points: 125561 More actions · Answer 14

Also if you are forced to comply with an unreasonable request, then give them the bare minimum required. For example, keep in mind that the 'SA' account and SYSADMIN are not the same thing. 'SA' is just a SQL Server account, and what exact permissions it has can be determined by, you, the DBA.

You can drop or rename the original defaul 'SA' account and then re-create it using access and permissions using your own discretion. In the example below, we have altered the 'SA' account to be more of a Power User who can query any table and do things like view execution plans or run traces, but it no longer has full server administration (SYSADMIN) privillages.

use MASTER

alter login [sa] disable;

alter login [sa] with name = [sa_bak];

go

create login [sa] with PASSWORD = 'wh@t3v3r', DEFAULT_DATABASE = master;

go

grant alter trace to [sa];

grant view any definition to [sa];

grant virw server state to [sa];

go

use Accounting;

create user [sa] for login [sa];

go

exec sp_addrolemember 'db_datareader', 'sa';

go

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho