mysql> show grants for 'kiran'@'localhost';
>+-------------------------------------------------------------------------------------------------------------------------------------------------------+
>| Grants for kiran@localhost |
>+-------------------------------------------------------------------------------------------------------------------------------------------------------+
GRANT USAGE ON *.* TO 'kiran'@'localhost' IDENTIFIED BY PASSWORD '*C57074CED5D2747BA86351977028B255F1AD6914' |
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER ON `Database1`.* TO 'kiran'@'localhost' |
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER ON `Database2`.* TO 'kiran'@'localhost' |
mysql> REVOKE UPDATE ON Database1.transaction FROM 'kiran'@'localhost';
ERROR 1147 (42000): There is no such grant defined for user 'kiran' on host 'localhost' on table 'transaction'
### Giving privileges on each table based on schema
SELECT CONCAT( 'GRANT SELECT, INSERT, XXX ON ', TABLE_SCHEMA, '.', TABLE_NAME, " ON `Database1`.* TO 'kiran'@'localhost'" ) FROM TABLES WHERE TABLE_SCHEMA='Database1';
## giving privileges on all schema and all tables
mysql -Bse"select CONCAT(\"GRANT SELECT, INSERT, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER, UPDATE ON \",TABLE_SCHEMA,\".\",TABLE_NAME, \" TO 'kiran'@'localhost' identified by 'kiran';\") from information_schema.tables where TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema')"
## giving privileges on all schemas ( schema level privileges)
mysql -Bse"select CONCAT(\"GRANT SELECT, INSERT, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER, UPDATE ON \",SCHEMA_NAME,\".*\" \" TO 'kiran'@'localhost' identified by 'kiran';\") from information_schema.SCHEMATA where SCHEMA_NAME NOT IN ('mysql', 'information_schema', 'performance_schema')"
For more details about the privileges the url is as follows:
http://dev.mysql.com/doc/refman/5.5/en/grant.html#grant-database-privileges
http://dev.mysql.com/doc/refman/5.5/en/grant.html#grant-table-privileges
>+-------------------------------------------------------------------------------------------------------------------------------------------------------+
>| Grants for kiran@localhost |
>+-------------------------------------------------------------------------------------------------------------------------------------------------------+
GRANT USAGE ON *.* TO 'kiran'@'localhost' IDENTIFIED BY PASSWORD '*C57074CED5D2747BA86351977028B255F1AD6914' |
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER ON `Database1`.* TO 'kiran'@'localhost' |
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER ON `Database2`.* TO 'kiran'@'localhost' |
mysql> REVOKE UPDATE ON Database1.transaction FROM 'kiran'@'localhost';
ERROR 1147 (42000): There is no such grant defined for user 'kiran' on host 'localhost' on table 'transaction'
### Giving privileges on each table based on schema
SELECT CONCAT( 'GRANT SELECT, INSERT, XXX ON ', TABLE_SCHEMA, '.', TABLE_NAME, " ON `Database1`.* TO 'kiran'@'localhost'" ) FROM TABLES WHERE TABLE_SCHEMA='Database1';
## giving privileges on all schema and all tables
mysql -Bse"select CONCAT(\"GRANT SELECT, INSERT, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER, UPDATE ON \",TABLE_SCHEMA,\".\",TABLE_NAME, \" TO 'kiran'@'localhost' identified by 'kiran';\") from information_schema.tables where TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema')"
## giving privileges on all schemas ( schema level privileges)
mysql -Bse"select CONCAT(\"GRANT SELECT, INSERT, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER, UPDATE ON \",SCHEMA_NAME,\".*\" \" TO 'kiran'@'localhost' identified by 'kiran';\") from information_schema.SCHEMATA where SCHEMA_NAME NOT IN ('mysql', 'information_schema', 'performance_schema')"
For more details about the privileges the url is as follows:
http://dev.mysql.com/doc/refman/5.5/en/grant.html#grant-database-privileges
http://dev.mysql.com/doc/refman/5.5/en/grant.html#grant-table-privileges

No comments:
Post a Comment