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
- PREPARE / EXECUTE stmt can execute only one query at a time
- Only Stored proc can use loop
Objective: All I want is to execute two 'SHOW GRANTS FOR' in one shot
Copyright License：
Author:「mysqlrockstar」,Reproduced under the CC 4.0 BY-SA copyright license with link to original source & disclaimer.
Link to：https://stackoverflow.com/questions/35918448/mysql-user-privileges-list-in-one-shot
About “MySQL User Privileges list in one shot” questions
SET @s:='';
SELECT @s:= CONCAT('SHOW GRANTS FOR \'',user,'\'@\'',host,'\';') FROM mysql.user where user = 'root';
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREP
|
Is there a MySQL query to get a list of all permissible user privileges?
Like the one shown on this MySQL page?
|
Maybe I am missing something, I am misunderstanding something about MySQL-PHP connections... I can't figure. I know it will be a lot to read but I can't put this in less words... Sorry!
The probl...
|
There is a MySQL user with database level privileges, as shown in the screen shot below. However, when an attempt is made to revoke privileges for this user, an error 1141 is the result. Why is t...
|
Need help to list all the privileges a user has (means, with "Y" on the column privileges) from the select * from mysql.user; result and have it as 1 column (as user_priv) together with host and user
|
We recently made the switch from SQL Server to MySQL for new projects, and I'm still struggling to get my head around the security model.
One of our applications has a MySQL user, called
|
I have a question regarding how to grant privileges MySQL user accounts. I have a MySQL user account, and in my example code I would like to grant privileges to the whole database. When I run the c...
|
From the control panel of my website I have created a new MySQL(5) database Test and a new user admin with password 123. I have tried assigning privileges to the user admin using:
GRANT ALL PRIVIL...
|
After executing the following mysql statement to create database and giving privileges to user can't access mysql
create database my_db;
GRANT ALL PRIVILEGES ON my_db.* TO 'user'@'%' IDENTIFIED BY '
|
I have really weird problem that I can't wrap my mind around. I'm using Debian Jessie for our servers. I've added testing repos and upgraded mysql version from 5.5 to 5.7.2 then setup private network
|
|
|