Principle of Least Privilege

"A user (or process) should have the lowest level of privilege required to perform an assigned task."

Very few need or should have root. Never (or VERY VERY carefully!) over the web.

GRANT & REVOKE are commands MySQL uses to give and take rights away at various levels of privilege. These levels include database , table , column .

Summary of GRANT:

     GRANT privilege [comma separated list]
        ON db-or-tables
        TO username
        [IDENTIFIED BY 'secret-password'] [WITH GRANT OPTION-really not often!!]
        ;
             

Find privileges online tutorial...

Examples:

GRANT select ON biz.*  TO 'lkd'@'localhost'      IDENTIFIED BY 'csci284';
GRANT select ON biz.*  TO 'lkd'@'%.sewanee.edu'  IDENTIFIED BY 'csci284';
GRANT select ON biz.*  TO 'lkd'@'%'              IDENTIFIED BY 'csci284';

flush privileges;
             

Creates an account for user 'lkd' to login from anywhere with select only privileges on your biz database only. And I can do the same with the shapes database... or any other. What about a GUEST account for the simple database we looked at for shapes & colors? Where we allow select *and* insert privileges?

GRANT select,insert ON snazzy.*  TO 'guest'@'localhost'      IDENTIFIED BY 'pssst';
GRANT select,insert ON snazzy.*  TO 'guest'@'%.sewanee.edu'  IDENTIFIED BY 'pssst';
GRANT select,insert ON snazzy.*  TO 'guest'@'%'              IDENTIFIED BY 'pssst'; 


#--   PHP scripts will need access in order for the php to perform queries on behalf of web users.
#--   You will have to set up the access wearing your MySQL HAT thinking about the following

#--   LEAST necessary       never,never, *.*       LOGIN allowed from WHERE         NOT your ROOT password

GRANT select           ON  snazzy.*       TO  'guest'@'%'            IDENTIFIED BY 'pssst';
             

If you mess up and want to back out a user, do so in the usual way, with a delete.

delete from user where User='guest';
             

After you have set up the guest account, MAKE SURE IT WORKS
To do this... login to some other machine in the room and do (or ask someone else to try) the following:

mysql -u guest -p -h YOURMACHINENAME.sewanee.edu