Create user with full privileges in mysql

Global privileges – Privileges are grouped according to the version that supports.

Privileges support by all MySQL version

[table id=5 /]

Supports 4.0.2 version
[table id=6 /]

Supports 5.0 version
[table id=7 /]

Supports 5.1 version
[table id=8 /]

First check your MySQL version,

[root@bugs-server]# mysql --version
mysql  Ver 14.12 Distrib 5.0.95, for redhat-linux-gnu (i686) using readline 5.1

Now we go to MySQL database

[root@bugs-server ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3410
Server version: 5.0.95 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

We will check all user at mysql database

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select user, host, password from user;
+-------------+-------------+------------------+
| user        | host        | password         |
+-------------+-------------+------------------+
| root        | localhost   | 3ab886884b99c0c3 |
| root        | bugs-server |                  |
| root        | 127.0.0.1   |                  |
|             | localhost   |                  |
|             | bugs-server |                  |
+-------------+-------------+------------------+

Now we create one user for example.

mysql> create user 'myee'@'localhost' IDENTIFIED by 'test01';
Query OK, 0 rows affected (0.00 sec)

Back end of MySQL will execute command like below,

insert into mysql.user ( host, user, password, select_priv, insert_priv, update_priv, delete_priv,  create_priv, 
drop_priv, reload_priv, shutdown_priv, process_priv,  file_priv, grant_priv, references_priv, index_priv, alter_priv, 
show_db_priv, super_priv, create_tmp_table_priv, lock_tables_priv, execute_priv, repl_slave_priv, repl_client_priv, 
create_view_priv, show_view_priv, create_routine_priv, alter_routine_priv, create_user_priv) values ( 'localhost', 
'myee', PASSWORD('test01'), 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 
'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N' );

flush privileges;

So just update mysql.user for myee user,

mysql> update mysql.user set user='myee', host='localhost', select_priv='Y', insert_priv='Y', delete_priv='Y', 
create_priv='Y', drop_priv='Y', reload_priv='Y', shutdown_priv='Y', process_priv='Y', file_priv='Y', 
references_priv='Y', index_priv='Y', alter_priv='Y', grant_priv='Y', update_priv='Y', execute_priv='Y', 
repl_slave_priv='Y', show_db_priv='Y', repl_client_priv='Y', super_priv='Y', lock_tables_priv='Y', 
create_tmp_table_priv='Y', create_view_priv='Y', create_routine_priv='Y', show_view_priv='Y', alter_routine_priv='Y', 
create_user_priv='Y' where user='myee' and host='localhost';

mysql> flush privileges;

Now myee user has full privileges in localhost, if you wanna connect from outside just change host with you ip address.

Leave a Reply

Your email address will not be published.