MySQL DBA - Tips and Techniques Magazine

13 Nov 2014

Check / Revoke access for a user

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