Shin, I've never tried this, so it's pure speculation, but I believe all of the grant information is contained in a regular table called user.
You should be able to copy this information into a temporary table using select into, then perform regular updates to change the host information to match your new subnets. Then simply copy it back to the user table, which will add these records to the existing records, crucially with the same passwords. You might have to repeat this a few times to get all the subnets in. I'd certainly perform lots of testing away from the live system before I tried this for real. Also, hopefully someone else will comment on whether this will actually work, or whether their is a fatal flaw in the idea. Best regards, Andy > -----Original Message----- > From: Shin [mailto:[EMAIL PROTECTED] > Sent: 30 September 2003 11:02 > To: [EMAIL PROTECTED] > Subject: GRANT update query: Updating host access entry for users but > retaining existing passwords > > > Hi, > > I've got a MYSQL 3.23.x setup that has approx 4000 database and 4000 > user accounts. 1 database per user. > > I created each userid from a script of the form > > CREATE DATABASE mdb_userid; > GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, > INDEX,RELOAD ON mdb_userid.* TO [EMAIL PROTECTED] IDENTIFIED BY 'passwd'; > GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, > INDEX,RELOAD ON mdb_userid.* TO [EMAIL PROTECTED] IDENTIFIED BY 'passwd'; > FLUSH PRIVILEGES; > > (this is part of a much longer script that I pass thru to mysql and > I'm using dummy/example userid,passwd and host entries in the above). > > I now need to grant additional access from a number of subnets for > each of the users in the database to their own database. I wanted to > use a statement of the form > > GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, > ALTER,INDEX,RELOAD ON mdb_userid.* TO > userid@'192.168.10.0/255.255.255.0' IDENTIFIED BY 'passwd'; > > But the issue is that I do not have the plain text versions of each > of the users password; as many have changed over the years. So I > can't use the latter format with IDENTIFIED BY as I want to retain > existing passwds. > > If I just do the above without having an IDENTIFIED BY entry then > the users get a blank passwd which is definetly a no-no in my > environment. > > Ideally what I'd like is a quick and simple way to grant users > access from the new subnets but to retain their existing passwords - > and without me having to extract all their existing encrypted > passwords and then inserts them in afterwards in the appropriate > tables - I prefer using GRANT. > > Is there anyway I can what I'm after? > > many thanks > Shin > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
