Create User in MySQL Command Line and Grant Privileges

Create User in Mysql Database and Grant Them Privileges

Mysql is a database management system that allows creating users at different levels. The users can be restricted to use all the privileges or can be granted to use all. Mysql has a very powerful privilege system. You can grant very specific privileges to users on the bases of their role to use the database. The users can be humans or they can be programs or applications. So be very careful in granting the privileges to users.

Users are distinguished by the username and host name. It means that user from different domains can have same usernames. To create user in mysql command line run mysql command line interface. Now login with the root username, that is the by default admin account for the database management system. Here we are going to create two users one for the admin use and one for the web use. We are creating users at localhost with WAMP server installed on a windows machine.

Now type

mysql->CREATE USER db_web@localhost;

mysql->CREATE USER db_admin@localhost;

In the above code we are creating users with “create user” command. The users with the names db_web and db_admin are created. When the users are created you can grant them privileges to use the DBMS. Now let us grant privileges to these two users.

Mysql->GRANT INSERT, UPDATE, DELETE, CREATE, DROP, FILE, INDEX, ALTER, CREATE TEMPORARY TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE ON *.* TO db_web@localhost;

In the above statement we are granting the listed functionalities to the db_web user. The user can use these privileges to all the databases and all the tables in these databases as we have used *.* (database.table) in the statement. Now we are going to grant privileges to the db_admin user.

mysql->GRANT ALL ON *.* TO db_admin@localhost WITH GRANT OPTION;

In the above statement we are granting privileges to the db_admin user. It has all the privileges on all the tables of all the databases and it can grant the privileges to others as well. Now we will run the “Flush privileges” statement.

mysql->FLUSH PRIVILEGES;

The above statement tell the MySQL DBMS to go and read the privileges from the database again. Hence we will be able to use the privileges to use right away.

Now let us give passwords to these users.

mysql-> UPDATE MYSQL.USER SET PASSWORD = PASSWORD(‘your_password’) WHERE USER = ‘db_web’;

mysql-> UPDATE MYSQL.USER SET PASSWORD = PASSWORD(‘your_password’) WHERE USER = ‘db_admin’;

mysql->FLUSH PRIVILEGES;

That’s all to create user in mysql command line and grant privileges.

Comments