How to grant privilege to users on MySQL database
by Arxleol on Sunday 02.05.2010, under MySQL, tutorial
If you need basics to start using your brand new mysql server without installing complex web interface.
First of all we want to start up mysql:
mysql -u root -p enter password
Second thing is to create some database.
create database dbname;
Let’s create dbuser with name dbuser on localhost and password userpass.
CREATE USER 'dbuser'@'localhost' IDENTIFIED BY 'userpass';Following are two types of granting first one grants all permissions on database dbname to user dbuser accessing from localhost. Second version grants only select and insert permissions.
GRANT ALL ON dbname.* TO 'dbuser'@'localhost'; GRANT SELECT, INSERT ON dbname.* TO 'dbuser'@'localhost';
To see what permissions certain user has use following command. Notice that this will show you permissions of admin on localhost.
SHOW GRANTS FOR 'admin'@'localhost';The following command is a bit more complex, if you have done something wrong this command will revoke all granted privileges. Afterwards select, insert, update and delete on every available database from any source address.
REVOKE ALL PRIVILEGES ON *.* FROM 'dbuser'@'localhost'; REVOKE GRANT OPTION ON *.* FROM 'dbuser'@'localhost'; GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'dbuser'@'localhost'WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;
