how to grants privileges to a user?

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
davidklonski
Forum Contributor
Posts: 128
Joined: Mon Mar 22, 2004 4:55 pm

how to grants privileges to a user?

Post by davidklonski »

Hello

I am experimenting with creating new users, granting them privileges and revoking them.

Here is what I am trying to do:
create a new user account for a user with username: 'johnsmith' and password: 'johnpassword'.
Allow that user to right to perform the following commands on database 'dbname': select, update, insert

After reading the manual, here is what I tried:
grant select, update, insert on dbname.* to 'johnsmith' identified by password 'johnpassword';

this didn't work because I got an error saying that:
"Password hash should be a 41-digit hexadecimal number"

could someone show me how I can create a new user account and provide that use with the privileges to select, update, insert?

thanks in advance
User avatar
tim
DevNet Resident
Posts: 1165
Joined: Thu Feb 12, 2004 7:19 pm
Location: ohio

Post by tim »

/use less
Last edited by tim on Tue Apr 20, 2004 6:22 pm, edited 1 time in total.
davidklonski
Forum Contributor
Posts: 128
Joined: Mon Mar 22, 2004 4:55 pm

Post by davidklonski »

I am talking about generating MySQL accounts and providing SQL privileges.
I need to know how to do it in DDL of SQL and not in PHP
Unipus
Forum Contributor
Posts: 409
Joined: Tue Aug 26, 2003 2:06 pm
Location: Los Angeles, CA

Post by Unipus »

I believe the password part is not supposed to be there.

grant select, update, insert on dbname.* to 'johnsmith' identified by 'johnpassword';
davidklonski
Forum Contributor
Posts: 128
Joined: Mon Mar 22, 2004 4:55 pm

Post by davidklonski »

can you show me the matching revoke command for the same user?
whatever I try, I am getting an error message saying that the SQL statement is invalid

here is correct format, taken from the spec:
REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON {tbl_name | * | *.* | db_name.*}
FROM user_name [, user_name] ...

here is what I am trying:
REVOKE select, update, insert ON dbname.* FROM 'johnsmith';

Do I always need to specify the host name as part of the username?
'johnsmith@host'? is 'johnsmith' equivalent to 'johnsmith@localhost' ?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

davidklonski wrote: Do I always need to specify the host name as part of the username?
'johnsmith@host'? is 'johnsmith' equivalent to 'johnsmith@localhost' ?
Cause i don't like running into trouble, i always use 'user'@'host'
Post Reply