TT380 - A bluffer's guide

Is all about security

The first thing to do (for Linux/UNIX users apparently, p.54, not quite sure what Windows users are supposed to do) is check the owner of the "MySQL daemon".

Make sure it's not the "root" user or there could be trouble.

Under Linux / UNIX:

ps auxw | grep mysqld

will reveal who owns "daemon".

Securing your mySQL connection

If you run mySQL on your own workstation protect your mySQL setup with a screensaver password when you leave your PC unattended. Or lock your front door, the choice is yours.

If you run mySQL on a server outside of your network then take precautions to make sure nasty people don't get hold of your login information. You can use, wait for it, SSH to encrypt the traffic you send over the web or, if you are using something browser based like phpMyAdmin, you should use the HTTPS protocol via SSL (Secure Sockets Layers - don't know, don't ask, haven't done TT282 yet, just do it alright!).

Luckily I've already installed SSH, I may blog how at some point...

Introducing the mySQL privilege system

Me: Hello mySQL privelege system. What do you do?

mySQL p.s.: Well, I check

• where you are accessing from (your host)


• who you say you are (username and password)


• what you are allowed to do (your command privileges)

Me: Thanks. Where do you store this information pray tell?

mySQL p.s.: Hmmm, let me think...

• columns_priv - defines user privileges for specific fields in a table


• db - defines permissions for all databases on the server


• func - defines user-created functions


• host - defines the acceptable hosts that can connect to a specific database


• tables_priv - user privileges for specific tables


• user - command privileges for a specific user

You'll need these in a minute when you add a few sample users to mySQL you know.

Me: Will I? Cheers....

2-step authentication process

mySQL checks 3 things during the authentication process, and it does it in 2 stages:

1. the host you are connecting from and the username / password pair you are using


2. whatever command (SELECT, UPDATE yadda yadda yadda) you're using, it makes sure you're allowed

If you can't be verified, you can't get in. There's a bit more to it than that, but remember we're bluffing.

Working with user privileges
Adding Users

The simplest way to add a new user is via the GRANT command. Or you can INSERT into all the relevant tables in the database, sounds like work to me!

So here we go:

GRANT privileges
ON databse.tablename
TO username@host
IDENTIFIED BY 'password';

Privileges that may be GRANTed:

ALL
ALTER
CREATE
DELETE
DROP
FILE
INDEX
INSERT
PROCESS
REFERENCES
RELOAD
SELECT
SHUTDOWN
UPDATE
USAGE

For example:

GRANT SELECT, INSERT
ON myDB.*
TO john@"%"
IDENTIFIED BY '99hjc';

Note two wildcards:

* replaces the entire list of tables
% replaces all known hosts anywhere!

Although GRANTing privileges should be automatically updated, you can FLUSH PRIVILEGES to reload the privilege tables. Super.

Removing Privileges

The opposite of GRANT.

For example:

REVOKE INSERT
ON myDB.*
FROM john@'%';

Will ruin john @ anywhere he damn well chooses' day if he wants to execute INSERT commands on any tables in myDB.

FLUSH PRIVILEGES afterwards, just to make sure.

Summary

Well, very interesting but still a little unsure about this whole setting up users thing.

I think I'll crack on through mySQL Hour 6 and see if that illuminates the issue further.