Yes you will have all the GRANTS for every user sitting in the 'mysql' schema
(from mysq.user) if you use the --all-databases option of mysqldump.
Here is something radical if you want to record the grants yourself:
Run the following query
SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') UserGrants
FROM mysql.user WHERE user IS NOT NULL AND user <> '' AND host IS NOT NULL AND
host <> '';
This query will create a SHOW GRANTS FOR command for all users in the mysqld
instance. Pipe the output to a ShowGrants.sql file
Mysql --skip-column-names -h<host> -u<username> -p<password> -A -e"SELECT
CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') UserGrants FROM mysql.user
WHERE user IS NOT NULL AND user <> '' AND host IS NOT NULL AND host <> ''" >
ShowGrants.sql
Then execute the script:
Mysql -h<host> -u<username> -p<password> -A < ShowGrants.sql > AllGrants.sql
AllGrants.sql will have all GRANTS but each line has no semicolon at the end
Just append a semicolon at the end of every line like this:
sed -i 's/$/;/' AllGrants.sql
Give it a try !!!
-----Original Message-----
From: Brown, Charles [mailto:[EMAIL PROTECTED]
Sent: Wednesday, March 19, 2008 11:37 AM
To: Sebastian Mendel; [email protected]
Subject: RE: mysql privileges
Here is a follow-up question: Using mysqldump, I'm about to dump all
databases and import to another instance - new . My question is do I
need to define all security and users in the new mysql or the security
definitions and privileges will be included in the dump file created by
mysqldump.
Please help me!
-----Original Message-----
From: Sebastian Mendel [mailto:[EMAIL PROTECTED]
Sent: Wednesday, March 19, 2008 1:24 AM
To: [email protected]
Subject: Re: mysql privileges
Malka Cymbalista schrieb:
> Thanks for your reply. When I do show grants, I get back
>
> GRANT SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES ON *.* TO
'super' @ 'localhost' ( mailto:super'@ 'localhost' ) identified by
password.......
>
> So it looks like super doesn't have rights to select from the hr
table. But why not? According to the tables_priv table, super should
have right to select.
did you export/import your data, or just copied the data files from your
old
to the new MySQL?
did you reload privileges after changes?
FLUSH PRIVILEGES;
--
Sebastian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
********************************************
This message is intended only for the use of the Addressee and
may contain information that is PRIVILEGED and CONFIDENTIAL.
If you are not the intended recipient, you are hereby notified
that any dissemination of this communication is strictly prohibited.
If you have received this communication in error, please erase
all copies of the message and its attachments and notify us
immediately.
Thank you.
********************************************
--
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]
