'Re: Question about Grants/Passwords'

[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql-win32
Subject:    Re: Question about Grants/Passwords
From:       "CDHUNG" <eghkn03 () engsvr ! ust ! hk>
Date:       2004-03-29 12:00:46
Message-ID: 00a801c41585$78f4cf80$bfe2fea9 () MADDOG
[Download RAW message or body]


----- Original Message ----- 
From: "Van Sederburg" <vps@pobox.com>
To: "Armando" <dijital@shaw.ca>
Cc: <win32@lists.mysql.com>
Sent: Monday, March 29, 2004 1:39 AM
Subject: RE: Question about Grants/Passwords


> Yep, I've tested it every which way, with --old-passwords and without, and
> it requires the FLUSH every time.
>
> Thanks for helping me work through it!
>
> Van
>
>
> > Well blow me down. After all that, just a FLUSH.
> >
> > Supposedly, no you should not need to use FLUSH PRIVILEGES when you use
> > GRANT to update user privileges. The server is supposed to notice these
> > changes and immediately reload the grant tables into memory. FLUSH
> > PRIVILEGES is supposed to be used to do it manually.
> >
> > 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!
> >
> > Armando
> >
> > -----Original Message-----
> > From: Van Sederburg [mailto:vps@pobox.com]
> > Sent: Sunday, March 28, 2004 10:01 AM
> > To: Armando
> > Cc: win32@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:vps@pobox.com]
> >> Sent: Saturday, March 27, 2004 2:21 PM
> >> To: Armando
> >> Cc: win32@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:vps@pobox.com]
> >>> Sent: Saturday, March 27, 2004 10:31 AM
> >>> To: Armando
> >>> Cc: win32@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:vps@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:vps@pobox.com]
> >>>>> Sent: Thursday, March 25, 2004 9:55 PM
> >>>>> To: win32@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!
> >>>>>
> >>>>> --
> >>>>> MySQL Windows Mailing List
> >>>>> For list archives: http://lists.mysql.com/win32
> >>>>> To unsubscribe:
> >> http://lists.mysql.com/win32?unsub=dijital@shaw.ca
> >>>>>
> >>>>>
> >>>>> --
> >>>>> MySQL Windows Mailing List
> >>>>> For list archives: http://lists.mysql.com/win32
> >>>>> To unsubscribe:
> >> http://lists.mysql.com/win32?unsub=mysql@myrro.com
> >>>>>
> >>>>
> >>>>
> >>>> --
> >>>> MySQL Windows Mailing List
> >>>> For list archives: http://lists.mysql.com/win32
> >>>> To unsubscribe:
> > http://lists.mysql.com/win32?unsub=mysql@myrro.com
> >>>>
> >>>
> >>>
> >>> --
> >>> MySQL Windows Mailing List
> >>> For list archives: http://lists.mysql.com/win32
> >>> To unsubscribe:    http://lists.mysql.com/win32?unsub=dijital@shaw.ca
> >>>
> >>>
> >>> --
> >>> MySQL Windows Mailing List
> >>> For list archives: http://lists.mysql.com/win32
> >>> To unsubscribe:    http://lists.mysql.com/win32?unsub=mysql@myrro.com
> >>>
> >>
> >>
> >> --
> >> MySQL Windows Mailing List
> >> For list archives: http://lists.mysql.com/win32
> >> To unsubscribe:    http://lists.mysql.com/win32?unsub=dijital@shaw.ca
> >>
> >>
> >> --
> >> MySQL Windows Mailing List
> >> For list archives: http://lists.mysql.com/win32
> >> To unsubscribe:    http://lists.mysql.com/win32?unsub=mysql@myrro.com
> >>
> >
> >
> > --
> > MySQL Windows Mailing List
> > For list archives: http://lists.mysql.com/win32
> > To unsubscribe:    http://lists.mysql.com/win32?unsub=mysql@myrro.com
> >
>
>
> -- 
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe:
http://lists.mysql.com/win32?unsub=eghkn03@engsvr.ust.hk
>
>



-- 
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:    http://lists.mysql.com/win32?unsub=mysql-win32@progressive-comp.com

[prev in list] [next in list] [prev in thread] [next in thread]
Configure | About | News | Add a list | Sponsored by KoreLogic