what is definer
The DEFINER clause specifies the MySQL account to be used when checking access privileges at routine execution time for routines that have the SQL SECURITY DEFINER characteristic. ... If the DEFINER clause is omitted, the default definer is the user who executes the CREATE PROCEDURE or CREATE FUNCTION statement.
------------------
DELIMITER $$
CREATE PROCEDURE ShowCustomers()
begin
select * from customers limit 2 ;
end$$
DELIMITER ;
call ShowCustomers();
---------------
DELIMITER $$
CREATE PROCEDURE ShowCustomers1()
begin
select * from customers limit 2 ;
end$$
DELIMITER ;
call ShowCustomers1();
--------------------
create user bunny@localhost identified by 'bunny';
-------------------
drop procedure ShowCustomers;
------------------
The DEFINER clause specifies the MySQL account to be used when checking access privileges at routine execution time for routines that have the SQL SECURITY DEFINER characteristic. ... If the DEFINER clause is omitted, the default definer is the user who executes the CREATE PROCEDURE or CREATE FUNCTION statement.
-------------
DELIMITER $$
CREATE definer=bunny@localhost PROCEDURE `ShowCustomers`()
sql security definer
begin
select * from customers;
end$$
DELIMITER ;
call classicmodels.ShowCustomers();
cd C:\Program Files\MySQL\MySQL Server 8.0\bin\
mysql -u bunny -p
------------------
Enter password: *******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 8.0.25 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use classicmodels;
Database changed
mysql> select * from customers limit 3;
+----------------+----------------------------+-----------------+------------------+--------------+-------------------+--------------+-----------+----------+------------+-----------+------------------------+-------------+
| customerNumber | customerName | contactLastName | contactFirstName | phone | addressLine1 | addressLine2 | city | state | postalCode | country | salesRepEmployeeNumber | creditLimit |
+----------------+----------------------------+-----------------+------------------+--------------+-------------------+--------------+-----------+----------+------------+-----------+------------------------+-------------+
| 103 | Atelier graphique | Schmitt | Carine | 40.32.2555 | 54, rue Royale | NULL | Nantes | NULL | 44000 | France | 1370 | 21000.00 |
| 112 | Signal Gift Stores | King | Jean | 7025551838 | 8489 Strong St. | NULL | Las Vegas | NV | 83030 | USA | 1166 | 71800.00 |
| 114 | Australian Collectors, Co. | Ferguson | Peter | 03 9520 4555 | 636 St Kilda Road | Level 3 | Melbourne | Victoria | 3004 | Australia | 1611 | 117300.00 |
+----------------+----------------------------+-----------------+------------------+--------------+-------------------+--------------+-----------+----------+------------+-----------+------------------------+-------------+
3 rows in set (0.00 sec)
mysql> select * from customers limit 3 \G;
*************************** 1. row ***************************
customerNumber: 103
customerName: Atelier graphique
contactLastName: Schmitt
contactFirstName: Carine
phone: 40.32.2555
addressLine1: 54, rue Royale
addressLine2: NULL
city: Nantes
state: NULL
postalCode: 44000
country: France
salesRepEmployeeNumber: 1370
creditLimit: 21000.00
*************************** 2. row ***************************
customerNumber: 112
customerName: Signal Gift Stores
contactLastName: King
contactFirstName: Jean
phone: 7025551838
addressLine1: 8489 Strong St.
addressLine2: NULL
city: Las Vegas
state: NV
postalCode: 83030
country: USA
salesRepEmployeeNumber: 1166
creditLimit: 71800.00
*************************** 3. row ***************************
customerNumber: 114
customerName: Australian Collectors, Co.
contactLastName: Ferguson
contactFirstName: Peter
phone: 03 9520 4555
addressLine1: 636 St Kilda Road
addressLine2: Level 3
city: Melbourne
state: Victoria
postalCode: 3004
country: Australia
salesRepEmployeeNumber: 1611
creditLimit: 117300.00
3 rows in set (0.00 sec)
ERROR:
No query specified
mysql> drop procedure ShowCustomers;
Query OK, 0 rows affected (0.11 sec)
mysql> DELIMITER $$
mysql>
mysql> CREATE PROCEDURE ShowCustomers()
-> begin
-> select * from customers limit 2 ;
-> end$$
Query OK, 0 rows affected (0.11 sec)
mysql> DELIMITER ;
mysql>
mysql> call ShowCustomers();
+----------------+--------------------+-----------------+------------------+------------+-----------------+--------------+-----------+-------+------------+---------+------------------------+-------------+
| customerNumber | customerName | contactLastName | contactFirstName | phone | addressLine1 | addressLine2 | city | state | postalCode | country | salesRepEmployeeNumber | creditLimit |
+----------------+--------------------+-----------------+------------------+------------+-----------------+--------------+-----------+-------+------------+---------+------------------------+-------------+
| 103 | Atelier graphique | Schmitt | Carine | 40.32.2555 | 54, rue Royale | NULL | Nantes | NULL | 44000 | France | 1370 | 21000.00 |
| 112 | Signal Gift Stores | King | Jean | 7025551838 | 8489 Strong St. | NULL | Las Vegas | NV | 83030 | USA | 1166 | 71800.00 |
+----------------+--------------------+-----------------+------------------+------------+-----------------+--------------+-----------+-------+------------+---------+------------------------+-------------+
2 rows in set (0.00 sec)
Query OK, 0 rows affected (0.05 sec)
mysql> create user bunny@localhost identified by 'bunny';
Query OK, 0 rows affected (0.40 sec)
mysql> drop procedure ShowCustomers;
Query OK, 0 rows affected (0.16 sec)
mysql> DELIMITER $$
mysql>
mysql> CREATE definer=bunny@localhost PROCEDURE `ShowCustomers`()
-> sql security definer
-> begin
-> select * from customers;
-> end$$
Query OK, 0 rows affected (0.13 sec)
mysql>
mysql> DELIMITER ;
mysql> call ShowCustomers();
ERROR 1370 (42000): execute command denied to user 'bunny'@'localhost' for routine 'classicmodels.ShowCustomers'
mysql>
mysql>
No comments:
Post a Comment
Note: only a member of this blog may post a comment.