RE: Question about Grants/Passwords - Armando - com.mysql.lists.win32

12 messages in com.mysql.lists.win32RE: Question about Grants/Passwords

From	Sent On	Attachments
Van Sederburg	25 Mar 2004 19:54
Armando	25 Mar 2004 21:52
Armando	26 Mar 2004 19:43
Van Sederburg	27 Mar 2004 08:30
Armando	27 Mar 2004 11:51
Van Sederburg	27 Mar 2004 12:20
Armando	27 Mar 2004 20:00
Van Sederburg	28 Mar 2004 07:23
Van Sederburg	28 Mar 2004 08:00
Armando	28 Mar 2004 09:32
Van Sederburg	28 Mar 2004 09:39
CDHUNG	29 Mar 2004 04:00

Subject:	RE: Question about Grants/Passwords
From:	Armando (diji...@shaw.ca)
Date:	03/28/2004 09:32:50 AM
List:	com.mysql.lists.win32

Well blow me down. After all that, just a FLUSH.

Well at least it looks like your problem is resolved, and I guess you'll be FLUSHing your pivileges as a rule by default from now on. I always do just to be safe :-) Cheers!

-----Original Message----- From: Van Sederburg [mailto:vp...@pobox.com] Sent: Sunday, March 28, 2004 10:01 AM To: Armando Cc: win...@lists.mysql.com Subject: RE: Question about Grants/Passwords

Oh wait, I hope I haven't been really stupid here...

I just issued Flush Privileges after the second grant, and suddenly it worked? I thought you only used FLUSH if you updated the tables directly?!?

So it seems that this is how it works:

1. Grant privileges w/identified by (password now exists - displays via select from mysql.user, also displays in MySQL CC) 2. Grant more privileges (password displays via select from mysql.user, does NOT display in MySQL CC, and doesn't work to connect with) 3. Flush Privileges (password continues to display via select from mysql.user, also displays in MySQL CC, and works correctly to connect)

I don't think step three is supposed to be needed is it?

Van

Try this: start the mysql server using the --old-passwords option. That way mysql will always generate 16 byte hashes instead of 41 byte

hashes that 4.1.x generates by default. Then create your user with the

grant...identified statement you were using before. At this point you were using 'set password' to set the password to the older 16 byte hash, but do not do that this time as the password should already be 16 bytes due to the server running in --old-passwords. Now grant the user some other privileges as desired and see what happens this time. Cheers.

Armando

-----Original Message----- From: Van Sederburg [mailto:vp...@pobox.com] Sent: Saturday, March 27, 2004 2:21 PM To: Armando Cc: win...@lists.mysql.com Subject: RE: Question about Grants/Passwords

Hi Armando,

Sorry, yes, I'm using MySQL 4.1.1 alpha, and ODBC 3.51 (there's no binary available for 3.52 yet) all on Windows XP Pro.

But I may have just figured out something, sort of...

If you look at the password column in the user table, it DOES show the

password. But it's no longer functional. If after running the commands

we're describing I try to connect with myuser/password, I get an error. Try connecting with no password, and it works.

i.e., run the grants, then try >mysql -u myuser -ppassword and see if you can connect. It won't work unless you use >mysql -u myuser with no password.

So the password is displayed in the table, but it's non-functional???

MySQL control center does show the password as gone, which is apparently correct.

Van

This is an interesting problem you're having, and I don't think you're

doing anything wrong. I just tried the same commands and entering them

directly in the mysql montior command line. I also do not need to use

old_password. I tried your two grants:

GRANT create temporary tables, delete, index, insert, lock tables, select, update ON mydb.* TO myuser@'%' identified by 'password';

GRANT create ON mydb.* to myuser@'%';

The first created my user and gave them the specified rights, the second one gave them the specified rights but did not eliminate the password. Perhaps instead of entering your statements in MySQL Control

Center and executing them there, try going to command line in DOS and

doing it that way. Do you get the same result? By the way, you said you're using ODBC 4.1.. Did you mean you're using MySQL 4.1? MyODBC is

only at 3.52 last time I checked the site. Cheers.

Armando

-----Original Message----- From: Van Sederburg [mailto:vp...@pobox.com] Sent: Saturday, March 27, 2004 10:31 AM To: Armando Cc: win...@lists.mysql.com Subject: RE: Question about Grants/Passwords

Thanks for the information Armando, but I must really be missing something here.

I've been trying this all morning, and I always lose the password upon

a subsequent grant. Here's exactly what I'm running (via MySQL Control Center)

--Grant initial privileges GRANT create temporary tables, delete, index, insert, lock tables, select, update ON mydb.* TO myuser@'%' identified by 'password'; -- Ok, password exists

-- Still using ODBC 4.1... set password for 'myuser'@'%' = old_password('password'); -- Ok, password corrected

-- Now add another privilege GRANT create ON mydb.* to myuser@'%'; -- Password is GONE.

I tried this same scenario, only rather than adding another privilege,

I added the same initial privileges to a different database. The password disappeared that way too.

What am I doing wrong?

Thanks!

Van

Actually no, updating the user table using the UPDATE statement is actually for global privileges. My apologies for not being clearer on

this point. If you want to assign database/table/column specific access to a specific user, use the GRANT statement, but do not add the

'identified by' portion to your grant statement, that way it will not

affect the user's password. Remember that if you are going to assign

specific privileges to a user, that their global privileges in the user table are all set to 'N'. That way mysql will only allow the privileges you specifically enter from your grant statements. Cheers.

Armando

-----Original Message----- From: Van Sederburg [mailto:vp...@onepost.net] Sent: Friday, March 26, 2004 7:58 AM To: Armando Subject: RE: Question about Grants/Passwords

Thanks, and just to be sure I'm clear... does this same logic apply if

I want to grant the privileges to ANOTHER database? i.e., they have

privs on databaseA, and I add databaseB and want to give them the same

rights.

Do I then have to do an INSERT to the db table?

Thanks

Van

Yes. Instead of using grant, update the user's privileges in the user

table directly with an UPDATE. Ie;

Update user set Delete_priv='Y', insert_priv='Y' where user='username'; FLUSH PRIVILEGES;

Of course this needs to be done with a user who has the ability to modify the user table in the mysql database, but my assumption is you

already are. Cheers.

Armando

-----Original Message----- From: Van Sederburg [mailto:vp...@pobox.com] Sent: Thursday, March 25, 2004 9:55 PM To: win...@lists.mysql.com Subject: Question about Grants/Passwords

If a user has been granted some privileges, and has a password assigned, is it possible to grant them more privileges without knowing

their password?

i.e., I assumed that if I at first I said grant something on mydb.* to myuser@'%' identified by 'password';

that I could grant more privileges like this; grant somethingelse on mydb.* to myuser@'%';

But it always seems to wipe out the password. What am I missing? Do

I always have to use the "identified by" clause to avoid losing the

password? I just want to add privileges and don't care what their password is.

If I have to use the password, is there a way to get the current value

and use it again?

Thanks!