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,
|
1 2 |
[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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
[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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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.
|
1 2 |
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,
|
1 2 3 4 5 6 7 8 |
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,
|
1 2 3 4 5 6 7 8 |
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.