MySQL users and permissions

[Lingerance]

So, the old devs have long left. Their code ranges from good, to questionable. However, there has never really been any person dedicated to looking after security issues, and these accounts just came to my attention, I've omitted the usernames for obvious reasons. Host % means it's accessible from any system that can reach port 3306 (which is every computer on our network), thankfully we don't have any workstations on said network.

Host

Password

Select_priv

Insert_priv

Update_priv

Delete_priv

Create_priv

Drop_priv

Reload_priv

Shutdown_priv

Process_priv

File_priv

Grant_priv

References_priv

Index_priv

Alter_priv

Show_db_priv

Super_priv

Create_tmp_table_priv

Lock_tables_priv

Execute_priv

Repl_slave_priv

Repl_client_priv

Create_view_priv

Show_view_priv

Create_routine_priv

Alter_routine_priv

Create_user_priv

ssl_type

ssl_cipher

x509_issuer

x509_subject

max_questions

max_updates

max_connections

max_user_connections

%

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

N

N

N

N

N

0

0

0

0

%

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

N

N

N

N

N

0

0

0

0

%

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

N

N

N

N

N

0

0

0

0

%

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

N

N

N

N

N

0

0

0

0

%

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

N

N

N

N

N

0

0

0

0

%

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

N

N

N

N

N

0

0

0

0

Sorry about the ugliness, it's the prettiest that CS would let me make it.

[dhromed]

SHUTDOWN;

[Lingerance]

@dhromed said:

SHUTDOWN;

Yup. DROP mysql.user; There's so many abusive things to be done with these accounts.


Edit: Actually, on further investigation, 33% of the accounts have these overly permissive privileges (drop, shutdown, delete, super, grant), this wouldn't be that much of an issue if we didn't have only two admins, but 150 MySQL accounts. 21 have no passwords. 50% have weak-hashes.

[blakeyrat]

@Lingerance said:

but 150 MySQL accounts. 21 have no passwords.

MySQL allows accounts with no passwords? Criminy.

[Kyanar]

@blakeyrat said:

@Lingerance said:

but 150 MySQL accounts. 21 have no passwords.

MySQL allows accounts with no passwords? Criminy.

It even defaults the ROOT account to no password. MSSQL just outright disables it.

[blakeyrat]

@Kyanar said:

@blakeyrat said:

@Lingerance said:

but 150 MySQL accounts. 21 have no passwords.

MySQL allows accounts with no passwords? Criminy.

It even defaults the ROOT account to no password. MSSQL just outright disables it.

And to be fair, Microsoft made that same mistake-- BEFORE THEY BECAME SANE. Next time someone tells me the open source software is more secure than the equivalent Microsoft software, I'll just say "MySQL, bitches!" do a fist-bump with Tom Cruise, put my mirrored sunglasses on, and drive off on my rocketbike.

[morbiuswilters]

@blakeyrat said:

@Kyanar said:

@blakeyrat said:

@Lingerance said:

but 150 MySQL accounts. 21 have no passwords.

MySQL allows accounts with no passwords? Criminy.

It even defaults the ROOT account to no password. MSSQL just outright disables it.

And to be fair, Microsoft made that same mistake-- BEFORE THEY BECAME SANE. Next time someone tells me the open source software is more secure than the equivalent Microsoft software, I'll just say "MySQL, bitches!" do a fist-bump with Tom Cruise, put my mirrored sunglasses on, and drive off on my rocketbike.

To be fair, most distros prompt for a root password when installing MySQL.  Additionally, it usually defaults to having networking turned off.  The assumption is that the admin will set up accounts and permissions correctly, and not just run it in the default state with everything accessing as root without a password.  Arguably, having a competent admin is far more important than having ultra-secure defaults.

[blakeyrat]

@morbiuswilters said:

To be fair, most distros prompt for a root password when installing MySQL.  Additionally, it usually defaults to having networking turned off.

 

Yeah, but that's true of MS SQL, too. 

[morbiuswilters]

@blakeyrat said:

@morbiuswilters said:

To be fair, most distros prompt for a root password when installing MySQL.  Additionally, it usually defaults to having networking turned off.

 

Yeah, but that's true of MS SQL, too. 

I'm not disputing that.  I'm simply pointing out that it isn't sensible to call MySQL "insecure" because it permits accounts without passwords and requires the admin to set up accounts and configure permissions.

[blakeyrat]

@morbiuswilters said:

I'm not disputing that.  I'm simply pointing out that it isn't sensible to call MySQL "insecure" because it permits accounts without passwords and requires the admin to set up accounts and configure permissions.

 

True. I have kind of a built-in prejudice against MySQL because I used it in the "dark ages". Which is to say "every version before about 5, where it was buggy as shit." It's still slightly buggy, but not nearly as bad as it used to be.

Although just a few months ago I was hit by one when transfering my blog's DB from one server to another... if your collation is set to latin1, you're still allowed to store UTF-only characters in the DB. Then when you query the same string back out, it silently translates your UTF characters into gibberish. Now, admittedly, my application was the flawed one-- thinking that the DB collation was set to UTF-8 when it was not-- but it was still a very, very annoying thing to fix.

Still, I blame MySQL, because a truly good DB server would refuse to store anything it couldn't spit back out.

[wrack]

Ever checked out the difference between charset and collation? And that client charset != server charset != database charset ?

[blakeyrat]

@bannedfromcoding said:

Ever checked out the difference between charset and collation? And that client charset != server charset != database charset ?

I'm not a databasetician. The point is, the blog was giving MySQL data that could not be stored using it's current settings. Then, when asked to retrieve the same
data back, MySQL gave back gibberish. Maybe it's not due to collation, but to one of those 4 dozen charset settings. Whatever.

[morbiuswilters]

@blakeyrat said:

@morbiuswilters said:

I'm not disputing that.  I'm simply pointing out that it isn't sensible to call MySQL "insecure" because it permits accounts without passwords and requires the admin to set up accounts and configure permissions.

 

True. I have kind of a built-in prejudice against MySQL because I used it in the "dark ages". Which is to say "every version before about 5, where it was buggy as shit." It's still slightly buggy, but not nearly as bad as it used to be.

Although just a few months ago I was hit by one when transfering my blog's DB from one server to another... if your collation is set to latin1, you're still allowed to store UTF-only characters in the DB. Then when you query the same string back out, it silently translates your UTF characters into gibberish. Now, admittedly, my application was the flawed one-- thinking that the DB collation was set to UTF-8 when it was not-- but it was still a very, very annoying thing to fix.

Still, I blame MySQL, because a truly good DB server would refuse to store anything it couldn't spit back out.

WTF are you on about?  latin1 and utf8 are both byte streams.  You can store any damn thing you want in them.  Collation matters for things like sorting, substr() and how it is displayed.  Your app is what took multi-byte utf8 and stored it as latin1; MySQL just saw it as multiple latin1 characters (most likely control characters if you got gibberish), because that's what you fucking told it you were storing.  It would be a bigger WTF if MySQL didn't allow you to store non-printable latin1 characters in a latin1 database simply to stop idiots from jamming utf8 in willy-nilly.  The fact is, you don't even necessarily need utf8 collation (although you should use it); you can store utf8 in a latin1 database (because they are both ASCII-compatible) and your application can handle outputting the correct content type so it displays at utf8.

[blakeyrat]

Let me have my irrational prejudices!

[morbiuswilters]

@blakeyrat said:

Let me have my irrational prejudices!

No.  Irrational prejudices cause hurricanes and oil spills.

[bstorer]

@morbiuswilters said:

@blakeyrat said:

Let me have my irrational prejudices!

No.  Irrational prejudices cause hurricanes and oil spills.

Bullshit.  I have it on good authority that those sorts of things are caused by the immodest dress of our women.

[Medezark]

@bstorer said:

@morbiuswilters said:

@blakeyrat said:

Let me have my irrational prejudices!

No.  Irrational prejudices cause hurricanes and oil spills.

Bullshit.  I have it on good authority that those sorts of things are caused by the immodest dress of our women.

Our women must not be dressing immodestly enough.  I asked for volcanoes, burning hail, triffid attacks, plagues of giant winged scorpions, and root-beer floods.

[PJH]

@Medezark said:

Our women must not be dressing immodestly enough.  I asked
for volcanoes,

Someone in Iceland heard it.

[dhromed]

@PJH said:

@Medezark said:

Our women must not be dressing immodestly enough.  I asked for volcanoes,

Someone in Iceland heard it.

 

lof lof lof Bjork joke lof lof lof

[demausdauth]

@Medezark said:

triffid attacks,

 

lol

 

[bstorer]

@dhromed said:

@PJH said:

@Medezark said:

Our women must not be dressing immodestly enough.  I asked for volcanoes,

Someone in Iceland heard it.

 

lof lof lof Bjork joke lof lof lof

  There's a lesson to be learned here: wearing a swan can stop air travel between the US and Europe.