SINGLE USER:
To see which grants a user has do
show grants for sdas;
+-----------------------------------------------------------------------------------------------------+
| Grants for sdas@% |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'sdas'@'%' IDENTIFIED BY PASSWORD '*B79ACB61D4237708B6280EC0A5A1092E54A02849' |
| GRANT SELECT ON `ANYPROV`.* TO 'sdas'@'%' |
| GRANT SELECT, INSERT, UPDATE ON `ANYPROV`.`RESPONSE` TO 'sdas'@'%' |
| GRANT SELECT, INSERT, UPDATE ON `ANYPROV`.`FEATURE` TO 'sdas'@'%' |
| GRANT SELECT, INSERT, UPDATE ON `ANYPROV`.`CUSTOMERVALUE` TO 'sdas'@'%' |
| GRANT SELECT, INSERT, UPDATE ON `ANYPROV`.`SERVICE` TO 'sdas'@'%' |
| GRANT SELECT, INSERT, UPDATE ON `ANYPROV`.`PRODUCT` TO 'sdas'@'%' |
| GRANT SELECT, INSERT, UPDATE ON `ANYPROV`.`PROVPRODUCTATTR` TO 'sdas'@'%' |
| GRANT SELECT, INSERT, UPDATE ON `ANYPROV`.`PROCESSENGINES` TO 'sdas'@'%' |
| GRANT SELECT, INSERT, UPDATE ON `ANYPROV`.`PROVFEATURE` TO 'sdas'@'%' |
| GRANT SELECT, INSERT, UPDATE ON `ANYPROV`.`REPORTGEN` TO 'sdas'@'%' |
| GRANT SELECT, INSERT, UPDATE ON `ANYPROV`.`PROVPRODUCT` TO 'sdas'@'%' |
| GRANT SELECT, INSERT, UPDATE ON `ANYPROV`.`FLOWEVENT` TO 'sdas'@'%' |
| GRANT SELECT, INSERT, UPDATE ON `ANYPROV`.`ORDERS` TO 'sdas'@'%' |
| GRANT SELECT, INSERT, UPDATE ON `ANYPROV`.`PROVSERVICEATTR` TO 'sdas'@'%' |
| GRANT SELECT, INSERT, UPDATE ON `ANYPROV`.`CUSTOMER` TO 'sdas'@'%' |
| GRANT SELECT, INSERT, UPDATE ON `ANYPROV`.`PROVSERVICE` TO 'sdas'@'%' |
+-----------------------------------------------------------------------------------------------------+
17 rows in set (0.00 sec)
SHOW GRANTS FOR 'sdas'@'localhost';
To revoke the delete access do :
REVOKE DELETE ON `ANYPROV`.`RESPONSE` FROM sdas;
REVOKE DELETE ON `ANYPROV`.`SERVICE` FROM sdas;
REVOKE DELETE ON `ANYPROV`.`PRODUCT` FROM sdas;
REVOKE DELETE ON `ANYPROV`.`PROVPRODUCT` FROM sdas;
REVOKE DELETE ON `ANYPROV`.`ORDERS` FROM sdas;
REVOKE DELETE ON `ANYPROV`.`PROVSERVICE` FROM sdas;
MULTIPLE USER:
If requested to remove Delete privs from multiple users, you can use the following scripts to help
1) SELECT CONCAT('SHOW GRANTS FOR \'', user ,'\'@\'', host, '\';') FROM mysql.user WHERE user in ('sdas','sbhandari');
+----------------------------------------------------------+
| CONCAT('SHOW GRANTS FOR \'', user ,'\'@\'', host, '\';') |
+----------------------------------------------------------+
| SHOW GRANTS FOR 'sbhandari'@'%'; |
| SHOW GRANTS FOR 'sdas'@'%'; |
| SHOW GRANTS FOR 'sbhandari'@'localhost'; |
| SHOW GRANTS FOR 'sdas'@'localhost'; |
+----------------------------------------------------------+
4 rows in set (0.00 sec)
Exit
2) cut and paste the SHOW GRANTS into a file /tmp/show_grants.sql
3) run it to generate the output statements, delete …
mysql -uroot –p*** -se "source /tmp/show_grants.sql" > /tmp/show_all_grants.sql
4) pull out all the delete statements only
grep -i DELETE /tmp/show_all_grants.sql > /tmp/revoke_deletes.sql
5)
vi /tmp/revoke_deletes.sql
Then change the TO -> FROM
%s/TO/FROM
Also change @'%' -> ;
%s/@'%'/;
Also remove single quote
%s/'//g
Also change
%s/GRANT SELECT, INSERT, UPDATE,/REVOKE
SHOULD NOW LOOK LIKE:
REVOKE DELETE ON `ANYPROV`.`PROVSERVICE` FROM sdas;
5) then run it
Mysql> source /tmp/revoke_deletes.sql
No comments:
Post a Comment