Page 1 of 1
How to view grants for user, root@%[solved]
Posted: Sun Jan 15, 2006 2:09 pm
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)
Posted: Sun Jan 15, 2006 6:36 pm
by Jenk
STOP USING ROOT AND STOP TRYING TO REVOKE PRIVILEDGES ON ROOT!!!!111oneone
Posted: Mon Jan 16, 2006 5:12 am
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....

Posted: Mon Jan 16, 2006 10:09 am
by Weirdan
Jenk, shouting at others is a bad behaviour. Calm down please.
Posted: Mon Jan 16, 2006 10:14 am
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
Posted: Mon Jan 16, 2006 2:21 pm
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
Posted: Mon Jan 16, 2006 5:01 pm
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.
Posted: Mon Jan 16, 2006 5:23 pm
by Jenk
Weirdan wrote:Jenk, shouting at others is a bad behaviour. Calm down please.
the !!!111oneoneone kinda gave away I was jokingly shouting..
Posted: Mon Jan 16, 2006 7:10 pm
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.
Posted: Mon Jan 16, 2006 10:02 pm
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?
Posted: Tue Jan 17, 2006 4:36 am
by Jenk
Have you tried an app like MySQL Administrator, it might give a better error message atleast

edited
Posted: Tue Jan 17, 2006 6:04 am
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)
Posted: Tue Jan 17, 2006 7:33 pm
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.