granting user rights

[bookouri]

I just started trying to learn mysql so Im new to this.
I have a question about granting user rights to objects though...

for example
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON db_name.*
-> TO 'user'
-> IDENTIFIED BY 'password';

If i want to grant privileges on a user that already exists, I will probably not have any idea what their password is. I tried to grant privileges on a user without the password but the grant statement fails when i do that.

Am I missing something?

thanks for any comments...

 

[bookouri]

I guess I had a syntax error or something. I finally got the grant to run without providing a password..

grant all on *.* to 'myuser'

but myuser still cant connect to mysql with
mysql -u myuser -p

I get an error 1045 access denied for my user now.

 

[vacunita]

You don't need to specify the password. Just the User.

What sort of error are you getting?


----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[vacunita]

Is your user connecting to the DB from the same location that DB resides on?

MYSQL is picky about access and locations .
A user may be able to access mysql from several locations.

If the user is not connecting from the same machine that holds the DB then you need to specify a host. such as:

GRANT ALL on Dbname.* TO 'user'@'location';

locations can be IP addresses or domain names.




----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[DonQuichote]

In addition to the above, localhost does not "fall into" '%'. So you can grant any rights to '%' (any location) and you will still be unable to log in. Off course '%' can be very dangerous, unless you really mean to run a publicly available database server. In short, if you want to connect from localhost and from other machines, you need two grant statements at minimum.

If you want to connect from the internal network (say 192.168.0.0 to 192.168.0.255), you can use the wildcard also:

GRANT READ ON DbName.* TO 'SomeUser'@'192.168.0.%';

You need to supply the password clause only once.

One warning: If you have the querylog running, those GRANT statements will be logged also (!). You can suppress logging with:

SET @LogSave=@@session.sql_log_off;
SET SESSION sql_log_off=1; -- For security, do not allow these statements to be logged.

-- Your GRANT statements here...

SET SESSION sql_log_off=@LogSave;

One further warning: you can bind MySQL to an IP address in my.cnf or my.ini. Make sure this is not the case or you connect from that IP address. No wildcards or netmasks are possible in the config files, alas.

Good luck!


+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[bookouri]

ill have to look closer at that location thing.. i am only trying to logon at the local machine..
this is the format im using..
mysql -u mynewuser -p

it works fine for root but not for my newuser...

 

[DonQuichote]

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON db_name.* TO 'mynewuser'@'localhost' IDENTIFIED BY 'password';

This should do it.


+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)