MySQL privileges

Create super user with access from the internal network:

CREATE USER 'sami'@'192.168.%' IDENTIFIED BY 'some-secret-password';
GRANT ALL PRIVILEGES ON *.* TO 'sami'@'192.168.%';
FLUSH PRIVILEGES;

Create restricted user allowed to access from localhost and one external IP address:

CREATE USER 'sami'@'92.247.16.194' IDENTIFIED BY 'secret-pass';
CREATE USER 'sami'@'localhost' IDENTIFIED BY 'secret-pass';
GRANT CREATE, SELECT, INSERT, DELETE, UPDATE, ALTER ON sandbox.* TO 'sami'@'92.247.16.194';
GRANT CREATE, SELECT, INSERT, DELETE, UPDATE, ALTER ON sandbox.* TO 'sami'@'localhost';

List the user permissions on localhost:

SHOW GRANTS FOR 'sami'@'localhost';

SELECT * FROM information_schema.user_privileges;

Revoke the privilege:

REVOKE [type of permission] ON [database name].[table name] FROM 'sami'@'localhost';

Change user password:

SET PASSWORD FOR 'user'@'host' = 'password';

Remove user:

DROP USER ‘sami’@‘localhost’;

Short list of privileges: ALL PRIVILEGES- allow a MySQL user all access to a designated database (or if no database is selected, across the system) CREATE- allows them to create new tables or databases DROP- allows them to them to delete tables or databases DELETE- allows them to delete rows from tables INSERT- allows them to insert rows into tables SELECT- allows them to use the Select command to read through databases UPDATE- allow them to update table rows GRANT OPTION- allows them to grant or remove other users privileges Often the remote access is restricted by MySQL settings. Here's a quick solution for linux systems. Find localhost restriction:

egrep -r bind-address /etc/mysql/

Disable it:

bind-address = 127.0.0.1
/etc/init.d/mysql restart