Creating a Database, User, and Granting Privileges

Posted on Mar 11, 2011

I always for get how to creat a database, user, and granting privileges to the new user for that database:

CREATE DATABASE new_db;
Query OK, 1 row affected (0.02 sec)
CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.03 sec)
CREATE USER 'new_user'@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.03 sec)
GRANT ALL PRIVILEGES ON 'new_db'.* TO 'new_user'@'localhost' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.03 sec)
GRANT ALL PRIVILEGES ON 'new_db'.* TO 'new_user'@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.03 sec)

Privileges

  • ALL
  • ALTER
  • ALTER_ROUTINE
  • CREATE
  • CREATE  ROUTINE
  • CREATE TEMPORARY TABLES
  • CREATE USER
  • CREATE VIEW
  • DELETE 
  • DROP
  • EVENT
  • EXECUTE
  • FILE
  • GRANT OPTION
  • INDEX
  • INSERT
  • LOCK TABLES
  • PROCESS
  • REFERENCES
  • RELOAD
  • REPLICATION CLIENT
  • REPLICATION SLAVE
  • SHOW DATABASES
  • SHOW VIEWS
  • SHUTDOWN
  • SUPER 
  • TRIGGER
  • UPDATE
  • USAGE