CODEWITHSUNDEEP
mysqlsql
mysqlrockstar
mysqlrockstar

Reputation: 2612

MySQL User Privileges list in one shot

SET @s:='';
SELECT @s:= CONCAT('SHOW GRANTS FOR \'',user,'\'@\'',host,'\';') FROM mysql.user where user = 'root';    
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

I dont mind to achieve this using any stored proc. Let say I have 2 users with root

'root'@'%' and 'root'@'localhost'

What I want is to get result of multiple prepare statements. But the above only executes the last one (ie 'root'@'localhost'). The two challenges I faced

  1. PREPARE / EXECUTE stmt can execute only one query at a time
  2. Only Stored proc can use loop

Objective: All I want is to execute two 'SHOW GRANTS FOR' in one shot

Upvotes: 0

Views: 1448

Answers (3)

mysqlrockstar
mysqlrockstar

Reputation: 2612

This in also not the answer, as the question is more on the execution of multiple prepared statements, Another example can be like in case we need to OPTIMIZE all tables in a database, @wchiquito answer is accepted for that reason

Finally Percona already came up with pt-show-grants

One more way I tried myself to get it along with the database-name. But this may not work on Version 5.7, In a more readable format would be

(SELECT `GRANTEE`, `TABLE_SCHEMA`, (CASE 
WHEN GROUP_CONCAT(`PRIVILEGE_TYPE`) = 'SELECT' THEN 'READ ONLY'
WHEN (LOCATE('DELETE',GROUP_CONCAT(`PRIVILEGE_TYPE`))
+ LOCATE('UPDATE',GROUP_CONCAT(`PRIVILEGE_TYPE`))
+ LOCATE('INSERT',GROUP_CONCAT(`PRIVILEGE_TYPE`))
+ LOCATE('SELECT',GROUP_CONCAT(`PRIVILEGE_TYPE`))) >= 4 THEN 'READ+WRITE'
ELSE GROUP_CONCAT(`PRIVILEGE_TYPE` ORDER BY `PRIVILEGE_TYPE`)
END) AS 'PRIVILEGE_TYPE'
FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES
WHERE GRANTEE NOT REGEXP  '^......$'
GROUP BY `GRANTEE`, `TABLE_SCHEMA`)
UNION
(SELECT  `GRANTEE`, 'All Databases' AS `TABLE_SCHEMA`, (CASE 
WHEN GROUP_CONCAT(`PRIVILEGE_TYPE`) = 'SELECT' THEN 'READ ONLY'
WHEN (LOCATE('DELETE',GROUP_CONCAT(`PRIVILEGE_TYPE`))
+ LOCATE('UPDATE',GROUP_CONCAT(`PRIVILEGE_TYPE`))
+ LOCATE('INSERT',GROUP_CONCAT(`PRIVILEGE_TYPE`))
+ LOCATE('SELECT',GROUP_CONCAT(`PRIVILEGE_TYPE`))) >= 4 THEN 'READ+WRITE'
ELSE GROUP_CONCAT(`PRIVILEGE_TYPE` ORDER BY `PRIVILEGE_TYPE`)
END) AS 'PRIVILEGE_TYPE'
FROM INFORMATION_SCHEMA.USER_PRIVILEGES
WHERE GRANTEE NOT REGEXP  '^......$'
GROUP BY `GRANTEE`
HAVING GROUP_CONCAT(`PRIVILEGE_TYPE`) != 'USAGE')

Upvotes: 2

Sampad Das
Sampad Das

Reputation: 139

DROP PROCEDURE IF EXISTS `db`.`SP_UserRolePriv`;

DELIMITER $$

CREATE PROCEDURE  `db`.`SP_UserRolePriv`()
BEGIN


DECLARE done int(10) default 0;
DECLARE Var_user varchar(25);
DECLARE Var_host varchar(25);
DECLARE QST text;
DECLARE cur_urp CURSOR FOR SELECT user,host FROM mysql.user;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;


OPEN cur_urp;

    tab_loop: LOOP

    FETCH cur_urp INTO Var_user,Var_host;

        IF done THEN
                LEAVE tab_loop;
            END IF;

        -- SELECT CONCAT(Var_user,'@',Var_host) "User";

        SET @QST = CONCAT('SHOW GRANTS FOR ',CONCAT('''',Var_user,'''@''',Var_host,''';'));

        -- SELECT QST;

        PREPARE stmt FROM @QST;

        EXECUTE stmt ; 

        DEALLOCATE PREPARE stmt; 

    END LOOP;
CLOSE cur_urp;

END $$

DELIMITER ;


CALL `mysqlagent`.`SP_UserRolePriv`();

Upvotes: 1

wchiquito
wchiquito

Reputation: 16551

With the appropriate privileges, you can do something like:

mysql> system rm -f /tmp/get_show_grants.sql;

mysql> SELECT CONCAT('system rm -f /tmp/show_grants.sql;
       SELECT CONCAT(\'SHOW GRANTS FOR \'\'\', `user`, \'\'\'@\'\'\', `host`,\'\'\';\')
       INTO OUTFILE \'/tmp/show_grants.sql\'
       FROM `mysql`.`user`
       WHERE `user` = \'root\';
       source /tmp/show_grants.sql;
       system rm -f /tmp/show_grants.sql /tmp/get_show_grants.sql;
       ') INTO OUTFILE '/tmp/get_show_grants.sql';
Query OK, 1 row affected (0.00 sec)

mysql> source /tmp/get_show_grants.sql;
Query OK, 1 row affected (0.00 sec)

+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

Upvotes: 0

Related Questions