I want to create read only user in MySQL without any intervention of root user.
CREATE USER ‘$user‘@’127.0.0.1′ IDENTIFIED BY ‘$password‘;
Works for me only If I am a root user. But I want it to happen from a non root user. Can this be done?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
From the manual
To use this statement, you must have the global CREATE USER privilege or the INSERT privilege for the mysql database.
So first you must have the root account grant you access to CREATE USER. Then your account will be able to create other users.
You can read the manual http://dev.mysql.com/doc/refman/5.1/en/create-user.html
First you have to create a user and then assign permission to it.
CREATE USER 'test'@'%' IDENTIFIED BY 'test';
and later execute this query to grant global SELECT privilege.
GRANT SELECT ON *.* TO 'test'@'%';