Difference between revisions of "MySQL database Access and Users"

Latest revision as of 10:13, 1 July 2017

How to define "who" can access MySQL via PHP, MySQL client, and what rights "who" has.

There is a database called "mysql" with user rights tables. Two key tables are:

1. The mysql.user is a global table and privileges apply to all databases.
2. The mysql.db defines what privileges a user has on each individual database.

If user 'lisa' is defined to have access to her database 'lisadb' and have limited or no access defined in mysql.user except only her password defined there, the mysql.db file will show her access rights to lisadb. Lisa will be able to write PHP to access lisadb as well as run the mysql client from console and access only lisadb.

Commands and what they do:

mysql> grant all privileges on *.* to lisa@localhost identified by 'password';

• This gives lisa global rights in mysql.user to all the databases. She will be able to do almost anything to anyone's database, include delete the database, drop tables, etc.

mysql> grant select,insert,update,delete,create,drop,alter,index on *.* to lisa@localhost identified by 'password';

• Grant specific global rights to lisa for all databases. Creates privileges visible in mysql.user

mysql> grant select,insert,update,delete,create,drop,alter,index on lisadb.* to lisa@localhost identified by 'password';

• Recommended. Grant specific and perhaps necessary privileges to lisa for her specific database. Privileges will be visible in mysql.db

mysql> show grants for lisa@localhost;

• Displays what privileges her account has.

mysql> select * from mysql.user;
mysql> select * from mysql.db;

• You know what these do.

mysql> set password for lisa@localhost = password('shhsecret');
mysql> update mysql.user set password = password('shhsecret') where User = 'lisa';
mysql> flush privileges;

• Various methods of setting the authentication password for lisa. The first is recommended and provided by mysql. User's password is stored in mysql.user, there are _not_ individual passwords for each database in mysql.db, so the same password applies to all databases for user.

• The second is the manual method of setting user's password. In this example lisa's password is being set to a most secure 'shhsecret'.

• Finally, it _is_ necessary to execute the flush command for mysql to recognize the changes to lisa's account during the current session.

mysql> delete from mysql.user where User = 'lisa';

• Now we are done with lisa so we can revoke all her access and get her out of all our mysql databases.

Privilege 	            Meaning
----------------------------------------------------------------------
ALL [PRIVILEGES]        Sets all simple privileges except GRANT OPTION
ALTER                   Allows use of ALTER TABLE
CREATE                     Allows use of CREATE TABLE
CREATE TEMPORARY TABLES    Allows use of CREATE TEMPORARY TABLE
CREATE VIEW 	          Allows use of CREATE VIEW
DELETE 	                Allows use of DELETE
DROP 	                Allows use of DROP TABLE
EXECUTE 	                Allows the user to run stored procedures (MySQL 5.0)
FILE 	                Allows use of SELECT ... INTO OUTFILE and LOAD DATA INFILE
INDEX 	                Allows use of CREATE INDEX and DROP INDEX
INSERT 	                Allows use of INSERT
LOCK TABLES 	          Allows use of LOCK TABLES on tables for which you have the SELECT privilege
PROCESS                    Allows use of SHOW FULL PROCESSLIST
RELOAD                     Allows use of FLUSH
REPLICATION CLIENT         Allows the user to ask where the slave or master servers are
REPLICATION SLAVE 	    Needed for replication slaves (to read binary log events from the master)
SELECT                     Allows use of SELECT
SHOW DATABASES             SHOW DATABASES shows all databases
SHOW VIEW                  Allows use of SHOW CREATE VIEW
SHUTDOWN                   Allows use of mysqladmin shutdown
SUPER                      Allows use of CHANGE MASTER, KILL, PURGE MASTER LOGS, and SET GLOBAL statements, the mysqladmin 
                           debug command; allows you to connect (once) even if max_connections is reached
UPDATE                     Allows use of UPDATE
USAGE                      Synonym for ``no privileges
GRANT OPTION               Allows privileges to be granted