MySQL - Grant

mysql

GRANT SELECT,INSERT,DELETE,UPDATE ON cfresources TO 'marketing'@'172.16.%' IDENTIFIED BY '...' [WITH GRANT OPTION];
FLUSH PRIVILEGES;
GRANT ALL PRIVILEGES ON dbName.* TO userName@'192.58.197.0/255.255.255.0' IDENTIFIED BY '...';
GRANT ALL PRIVILEGES ON *.* TO userName@"%.domain.com" IDENTIFIED BY '...';
GRANT <privileges> ON <what> TO <user>[identified by "<password>"][with grant option]

GRANT ALL ON db.* TO dbmanager@server.host.net IDENTIFIED BY 'some password' WITH GRANT OPTION

Where <privileges> is a comma separated list of privileges:

  • ALTER: modify existing tables and indexes
  • CREATE: create new databases and tables
  • DELETE: delete data
  • DROP: delete table and / or databases
  • INDEX: create and / or delete indexes
  • INSERT: add new rows
  • SELECT: view / search table entries
  • UPDATE: modify existing table entries
  • FILE: read and write file on the MySQL server
  • PROCESS: view / kill server threads belonging to other users
  • RELOAD: reload access control tables, flush the logs, etc
  • SHUTDOWN: shutdown the server
  • ALL: allow to do everything
  • USAGE: only allow to login but nothing else

To obtain a list of users:

USE mysql; SELECT user FROM user;
CREATE USER ben IDENTIFIED BY 'plaintext';
CREATE USER ben IDENTIFIED BY PASSWORD 'hashed';

RENAME USER ben TO bforta;
DROP USER bforta;

Newly created accounts have no access at all.

SHOW GRANT FOR bforta;
GRANT SELECT ON crashcourse.* TO bforta;
GRANT ALL PRIVILEGES ON schemaName.* TO username@'localhost' identified by 'somepassword';
REVOKE SELECT ON crashcourse.* FROM bforta;
flush privileges;

When using GRANT and REVOKE, the user account must exist, but the object being referred to need not. This allows administrators to design and implement security before databases and tables are even created. A side effect of this is that if a database or table is removed (with DROP statement) any associated access rights will still exist. And if the database or table is re-created in the future, those rights will apply to them.

GRANT SELECT, INSERT ON crashcourse.* TO bforta;

Other user management commands:

UPDATE user SET Password=PASSWORD('new password') WHERE user="root";
DELETE FROM user WHERE Host='localhost' AND User='';
FLUSH PRIVILEGES;
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License