MySql grant user permission

mysql grant
Anuj picture Anuj · Jul 2, 2012 · Viewed 22.9k times · Source

I want to create a new user in MySql. I do not want that new user to do much with my existing databases [I just want to grant Select privilege to him], but he can do anything and everything with a new database which he creates.

Firstly, is there a way to grant permission as per the database owner? If it is possible, then that is the ideal thing I am looking for. And if not, then how do I restrict a particular user from accessing [only Select privilege] some specific database only, allowing him to do anything he wants with the remaining ones?

Answer

HeatfanJohn picture HeatfanJohn · Jul 2, 2012

From the MySQL grant documentation:

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
GRANT SELECT ON *.* TO 'jeffrey'@'localhost';
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';

The first command creates the user. The second grants select on all databases and tables. The third command grants all access to all tables in db1.

Is there anything else specific you are looking to do?

Related questions

grant remote access of MySQL database from any IP address

I am aware of this command: GRANT ALL PRIVILEGES ON database.* TO 'user'@'yourremotehost' IDENTIFIED BY 'newpassword'; But then it only allows me to grant a particular IP address to access this remote MySQL database. What if I want it …

Read More
How to grant remote access permissions to mysql server for user?

If I do SHOW GRANTS in my mysql database I get GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD 'some_characters' WITH GRANT OPTION If I am not mistaken, root@localhost means that user root can access the …

Read More
How to grant remote access to MySQL for a whole subnet?

I can easily grant access to one IP using this code: $ mysql -u root -p Enter password: mysql> use mysql mysql> GRANT ALL ON *.* to root@'192.168.1.4' IDENTIFIED BY 'your-root-password'; mysql> FLUSH PRIVILEGES; But i need …

Read More