How to view grants for user, root@%[solved]

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

How to view grants for user, root@%[solved]

Post by raghavan20 »

When I tried to view the grants assigned for root@%, it came up with following errors...

Code: Select all

Why does this normal syntax does not work here.....
mysql> show grants for root@'%';
ERROR 1141 (42000): There is no such grant defined for user 'root' on host '%'
mysql> show grants for 'root'@'%';
ERROR 1141 (42000): There is no such grant defined for user 'root' on host '%'
Is this the only way to do it...

Code: Select all

mysql> select * from mysql.user
    -> where
    -> user = 'root'
    -> and
    -> host = '%';
+------+------+--------------+-------------+-------------+-------------+-------------+-------------+----
| Host | User | Password     | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Dro
p_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_p
riv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv |
 Repl_slave_priv | Repl_client_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions
 | max_updates | max_connections |
+------+------+--------------+-------------+-------------+-------------+-------------+-------------+----
| %    | root | rootPassword | Y           | Y           | Y           | Y           | Y           | Y
       | Y           | Y             | Y            | Y         | Y          | Y               | Y
    | Y          | Y            | Y          | Y                     | Y                | Y            |
 Y               | Y                |          |            |             |              |             0
 |           0 |               0 |
+------+------+--------------+-------------+-------------+-------------+-------------+-------------+----
1 row in set (0.03 sec)
Last edited by raghavan20 on Tue Jan 17, 2006 7:34 pm, edited 1 time in total.
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

STOP USING ROOT AND STOP TRYING TO REVOKE PRIVILEDGES ON ROOT!!!!111oneone
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

Jenk, try to understand, in my other topic I have replied, I am just doing this to understand mysql in depth and I am not running any kind of website from here.

If anything goes wrong, I do not mind reinstalling mysql again.... :wink:
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

Jenk, shouting at others is a bad behaviour. Calm down please.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

raghavan20, the following works for me:

Code: Select all

mysql> use test
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 version();
+------------+
| version()  |
+------------+
| 4.0.25-log |
+------------+
1 row in set (0.00 sec)

mysql> show grants for root@'%';
+-------------------------------------------------------------------------------------------------------+
| Grants for root@%                                                                                     |
+-------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '<censored>' WITH GRANT OPTION |
+-------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show grants for 'root'@'%';
+-------------------------------------------------------------------------------------------------------+
| Grants for root@%                                                                                     |
+-------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '<censored>' WITH GRANT OPTION |
+-------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> quit
Bye
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

Weirdan, but to my surprise, the same command worked now...but I have another problem...I am not able to drop users from root itself...

Code: Select all

mysql>  show grants for root@'%';
+-------------------------------------------------------------------------------------------------------
-------------------------+
| Grants for root@%
                         |
+-------------------------------------------------------------------------------------------------------
-------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '*9BC01A49104D6960C8C288548AD36884E0B
580D8' WITH GRANT OPTION |
+-------------------------------------------------------------------------------------------------------
-------------------------+
1 row in set (0.00 sec)

mysql> select user,host,password from mysql.user;
+---------------+--------------+-------------------------------------------+
| user          | host         | password                                  |
+---------------+--------------+-------------------------------------------+
| root          | localhost    | *9BC01A49104D6960C8C288548AD36884E0B580D8 |
| selectonly    | localhost    |                                           |
| root          | %            | *9BC01A49104D6960C8C288548AD36884E0B580D8 |
| raghavan      | localhost    | *7ED9914DFC68CFA990B39A56635E9CBDE3E1F4E0 |
| selecttest    | localhost    |                                           |
| visitor       | localhost    | 7b31759a487a1f8d                          |
| alltest       | localhost    |                                           |
| selectonly    | farvista.net |                                           |
| allbasics     | farvista.net |                                           |
| selectanyhost | %            |                                           |
| selectinsert  | 66.94.230.33 |                                           |
| selectfields  | localhost    |                                           |
| updatefields  | localhost    |                                           |
+---------------+--------------+-------------------------------------------+
13 rows in set (0.16 sec)

mysql> drop user alltest;
ERROR 1268 (HY000): Can't drop one or more of the requested users
mysql> drop user alltest@localhost;
ERROR 1268 (HY000): Can't drop one or more of the requested users
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

you may try to delete users directly from mysql.user table (issue FLUSH PRIVILEGES after that). There are chances you'll get more meaningful error message.
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

Weirdan wrote:Jenk, shouting at others is a bad behaviour. Calm down please.
the !!!111oneoneone kinda gave away I was jokingly shouting..
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

I have now come up with this problem. I searched in devnetwork.net, there is no exact solution given for the same problem. I appreciate any kind of guidance.

This happened after I tried to stop the mysql in command prompt and tried to alter a mysql server instance. As far as Windows services, mysql is running

Code: Select all

C:\>mysql -u root -p
Enter password: ************
ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)

C:\>telnet localhost 3306
Connecting To localhost...Could not open connection to the host, on port 3306.
No connection could be made because the target machine actively refused it.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Do you have a firewall that doesn't allow connections on that port?
Have you enabled tcp-ip sockets in your mysql server instance?
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

Have you tried an app like MySQL Administrator, it might give a better error message atleast :)
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

edited

Post by raghavan20 »

I have personally made sure that service at 3306 using tcp/ip is enabled

I get the same error message even when I disabled the McAfee firewall

Jenk, mysqladmin gives the error

Code: Select all

could not connect to the specified instance
mysql error no 2003
can't connect to the mysql server on localhost(10061)
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

The mysql 5 installation reported tcp port 3306 may be refused. I tried something with McAfee and it worked.
I copied my database folder in mysql4-install-dir/data and pasted in mysql5-install-dir/data and I am able to work with my old databases in mysql 4.
Post Reply