What you are asking for can only be done in two ways
PROXY USERS
MySQL 5.5 introduced proxy users
When authentication to the MySQL server occurs by means of an authentication plugin, the plugin may request that the connecting (external) user be treated as a different user for privilege-checking purposes. This enables the external user to be a proxy for the second user; that is, to have the privileges of the second user. In other words, the external user is a “proxy user” (a user who can impersonate or become known as another user) and the second user is a “proxied user” (a user whose identity can be taken on by a proxy user).
You can find the defined proxy users in mysql.proxies_priv:
mysql> desc mysql.proxies_priv;
+--------------+------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------+------+-----+-------------------+-----------------------------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Proxied_host | char(60) | NO | PRI | | |
| Proxied_user | char(16) | NO | PRI | | |
| With_grant | tinyint(1) | NO | | 0 | |
| Grantor | char(77) | NO | MUL | | |
| Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------------+------------+------+-----+-------------------+-----------------------------+
7 rows in set (0.22 sec)
mysql>
Here is sample code from the MySQL Documentation
CREATE USER 'empl_external'@'localhost'
IDENTIFIED WITH auth_plugin AS 'auth_string';
CREATE USER 'employee'@'localhost'
IDENTIFIED BY 'employee_pass';
GRANT PROXY
ON 'employee'@'localhost'
TO 'empl_external'@'localhost';
SECURITY TYPE (STORED PROCEDURES/VIEWS)
In mysql.proc
, there is a column called security_type
. With views, this exists in information_schema.views
. It has two values 1) invoker
and 2) definer
. If a stored procedure has security_type
as definer
, you have the same access rights as the owner of the stored procedure. Of course, this is only applicable
- when calling a Stored Procedure or SELECTing from a View
- You have the EXECUTE privilege