MySQL Grant Privileges command examples

Create and Grant

1
2
3
4
CREATE USER 'admin'@'%' IDENTIFIED BY 'password';
#GRANT ALL PRIVILEGES ON db.table TO 'username'@'hostlikestring';
GRANT ALL PRIVILEGES ON db.* TO 'admin'@'%';
FLUSH PRIVILEGES;

Create, Modify and Delete

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select host,user,password from mysql.user;
GRANT ALL PRIVILEGES ON *.* TO 'username'@'192.168.1.%'
    IDENTIFIED BY 'password'
    WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost'
    IDENTIFIED BY 'password'
    WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'username'@'regionprefix%'
    IDENTIFIED BY 'password'
    WITH GRANT OPTION;
delete from mysql.user where host='%';
commit; flush table mysql.user;
FLUSH PRIVILEGES;
select host,user,password from mysql.user;