hi


i have a requirement that i have to enable/disable a user account in MYSQL progrmatically.do we have any command for this ???

thanks in advance
Ramana

I doubt if this is possible directly, but you can use GRANT to set permissions to which tables the user has access to, limiting resources or changing the password.

hi anjanesh

first of all thanks a lot for replying my thread.


as you said i tried ..but efforts went in vain

this is how i tried:

GRANT ALL ON *.* TO david;//assumeing user is "david"

REVOKE ALL PRIVILEGES ON *.* FROM david;

after giveing the above REVOKE COMMAND .when i tries to login with
mysql -u david -ppassword

it is allowing me to login.since REVOKE is applied on david user he should not allow david to login.

please help me in this regard...if you find any command pls send me


thanks in advance
Ramana

Do FLUSH PRIVILEGES; after every GRANT or REVOKE.

hi anjanesh

i tried with FLUSH PRIVILEGES also
but no luck..

if you find any other solution pls let me know

thanks in advance
Ramana

Actually, the full username path should be 'username'@'hostname'

If the hostname of david is localhost then try

hi

i tried with what ever the command u sent just now


i tried with this command::::REVOKE ALL PRIVILEGES ON *.* FROM 'david'@'localhost';

this is the error i am getting...

mysql> REVOKE ALL PRIVILEGES ON *.* FROM 'david'@'localhost';
ERROR 1141 (42000): There is no such grant defined for user 'david' on host 'localhost'

thanks
Ramana

Ok. Lets see the data directly from the mysql table.
Login as root
What do you get ?

hi

when i use this command:

SELECT `Host`, `User`, `Password` FROM `user`;

this is the error i am getting

ERROR 1046 (3D000): No database selected

USE mysql;

And I hope you are using mysql command line - if not what client are you using ? In what are you executing queries ?

hi

yeah you are right i am useing MYSQL Command Prompt.

i tried with SQLYog also....

but no result.
thanks
Ramana

Whats the result of DESCRIBE `user` ?

hi

COMMAND ESCRIBE `user`
this is the result

ERROR 1046 (3D000): No database selected

thanks

To select the database type
USE mysql;

hi

when i typed :
mysql> use mysql database;
Database changed

after that when i type

mysql> DESCRIBE `user`;
+-----------------------+-----------------------------------+------+-----+-----
---+-------+
| Field | Type | Null | Key | Defa
lt | Extra |
+-----------------------+-----------------------------------+------+-----+-----
---+-------+
| Host | char(60) | NO | PRI |
| |
| User | char(16) | NO | PRI |
| |
| Password | char(41) | NO | |
| |
| Select_priv | enum('N','Y') | NO | | N
| |
| Insert_priv | enum('N','Y') | NO | | N
| |
| Update_priv | enum('N','Y') | NO | | N
| |
| Delete_priv | enum('N','Y') | NO | | N
| |
| Create_priv | enum('N','Y') | NO | | N
| |
| Drop_priv | enum('N','Y') | NO | | N
| |
| Reload_priv | enum('N','Y') | NO | | N
| |
| Shutdown_priv | enum('N','Y') | NO | | N
| |
| Process_priv | enum('N','Y') | NO | | N
| |
| File_priv | enum('N','Y') | NO | | N
| |
| Grant_priv | enum('N','Y') | NO | | N
| |
| References_priv | enum('N','Y') | NO | | N
| |
| Index_priv | enum('N','Y') | NO | | N
| |
| Alter_priv | enum('N','Y') | NO | | N
| |
| Show_db_priv | enum('N','Y') | NO | | N
| |
| Super_priv | enum('N','Y') | NO | | N
| |
| Create_tmp_table_priv | enum('N','Y') | NO | | N
| |
| Lock_tables_priv | enum('N','Y') | NO | | N
| |
| Execute_priv | enum('N','Y') | NO | | N
| |
| Repl_slave_priv | enum('N','Y') | NO | | N
| |
| Repl_client_priv | enum('N','Y') | NO | | N
| |
| Create_view_priv | enum('N','Y') | NO | | N
| |
| Show_view_priv | enum('N','Y') | NO | | N
| |
| Create_routine_priv | enum('N','Y') | NO | | N
| |
| Alter_routine_priv | enum('N','Y') | NO | | N
| |
| Create_user_priv | enum('N','Y') | NO | | N
| |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | |
| |
| ssl_cipher | blob | NO | |
| |
| x509_issuer | blob | NO | |
| |
| x509_subject | blob | NO | |
| |
| max_questions | int(11) unsigned | NO | | 0
| |
| max_updates | int(11) unsigned | NO | | 0
| |
| max_connections | int(11) unsigned | NO | | 0
| |
| max_user_connections | int(11) unsigned | NO | | 0
| |
+-----------------------+-----------------------------------+------+-----+-----
---+-------+
37 rows in set (0.00 sec)



this is the result.

thanks