Routines and Privileges in MySQL
MySQL Access Privileges - Part 4
Division 7
Forward: In this part of the series, we look at Routines and Privileges in MySQL. Routines are stored procedures and functions. In MySQL, a trigger is like a part of a table. I will also address the trigger and its privilege in this tutorial.
By: Chrysanthus Date Published: 8 Aug 2012
Introduction
Procedure Example
The following code is an example of a procedure. Do not worry for now what the procedure does or what problem it solves. Just know that the name of the procedure is, checkshortage.
#procedure to determine shortage of any product
Delimiter //
CREATE PROCEDURE checkshortage
(
IN numberOfRows INTEGER
)
BEGIN
# loop counter variable
DECLARE i INTEGER DEFAULT 1;
#variable for quantity requested
DECLARE QtyRequestVar INTEGER;
#variable for available quantity in the products table
DECLARE QtyAvailVar INTEGER;
#variable for quantity that would remain
DECLARE QtyRemainVar INTEGER;
WHILE i <= numberOfRows DO
SELECT QtyRequest, QtyAvail
FROM SaleDetailsTemp
WHERE ID = i
INTO QtyRequestVar, QtyAvailVar;
SET QtyRemainVar = QtyAvailVar – QtyRequestVar;
UPDATE SaleDetailsTemp SET QtyRemain= QtyRemainVar WHERE ID = i;
SET i = i + 1;
END WHILE;
END;
//
When a procedure like this one is typed at the mysql prompt and the Enter key is pressed after the last line, it is stored in the server, and it becomes a stored procedure. So a user logs into the server using mysql and then types a procedure, pressing the Enter key after the last line, to have the stored procedure. This is possible only if the user has the right to create a procedure; that is, if the user has the routine privilege. After the procedure has been created and stored successfully, at anytime, the user can login and give another user the privilege to execute the procedure. In MySQL, when you are running or using a procedure, we say you are executing the procedure.
Routine privileges are: ALTER ROUTINE, CREATE ROUTINE, EXECUTE, and GRANT OPTION. Now, if you have the ALTER ROUTINE privilege on a routine (procedure), it means you can change (alter) a routine. CREATE ROUTINE privilege enables you to create the routine in the first place. EXECUTE privilege enables you to execute (run or play) the routine. If you have the GRANT OPTION privilege, it means you can give the other routine privileges that you have, to another user.
These 4 routine privileges can be granted at the global or database level. The routine privilege level and the table privilege level are of the same level, as you come down from the global level. Now, know that ALTER ROUTINE, EXECUTE, and GRANT OPTION privileges can be granted at the routine level, but CREATE ROUTINE cannot be granted at the routine level. That is how things are with MySQL.
So, if you have the 4 routine privileges, at the global level, you can grant all of them to a user (John) at the mysql prompt as follows:
grant ALTER ROUTINE, CREATE ROUTINE, EXECUTE on *.* to 'john'@'localhost' with GRANT OPTION;
The first asterisk in *.* means all databases. If you want the privileges only for a particular database, then type the name of the database in the position of the first asterisk. You can also have the privileges without CREATE ROUTINE for a particular routine. The following statement grants privileges for the above routine in a particular database:
grant ALTER ROUTINE, EXECUTE on PROCEDURE myDb.checkshortage to 'john'@'localhost' with GRANT OPTION;
Note the reserved word, PROCEDURE and its position in the statement.
A trigger can be considered as part of a table. Actually, a trigger is typed and saved just like a procedure, but it must be associated to a table. The following code is an example of a trigger. Do not worry for now what the trigger does or what problem it solves. Just know that the name of the trigger is, InformManager.
#trigger to send product' s info to manager's table
Delimiter //
CREATE TRIGGER InformManager
AFTER UPDATE
ON Products
FOR EACH ROW
BEGIN
IF NEW.Quantity <= NEW.ReorderLevel THEN
INSERT INTO Manager (ProductName, ReorderLevel, CurrentLevel) VALUES (NEW.ProductName, NEW.ReorderLevel, NEW.Quantity);
END IF;
END;
//
The table this trigger is associated with is called, products, indicated in the “ON Products” line in the code. As mentioned above a trigger is produced and saved like a procedure.
Trigger Privilege
The trigger privilege is, TRIGGER. It is a table level privilege. It is a table privilege. We saw this in one of the previous parts of the series. If you have this privilege, it means you can create and drop and execute a trigger for a table.
Assume that you have a supermarket and you want each customer (user) to be able to INSERT rows into the sales table and the table has a trigger. You would grant each customer the INSERT and TRIGGER privileges as follows:
grant INSERT, TRIGGER on supermarket.sales to 'customer'@'localhost';
For a supermarket, you can give majority of the customers the same user name (customer) and the same password; that is, the same account. For the above trigger (code) you would have something like:
grant SELECT, TRIGGER on supermarket.Products to 'customer'@'localhost';
Note that, you have the name of the table the trigger is associated with and not the name of the trigger. In this case, the name of the table is, Products.
Note: Anybody who has the TRIGGER privilege can create, drop and execute the trigger. So, to allow ordinary customers to use and not create or drop triggers, give them the trigger privilege but do not make them know the name of any table. The EXECUTE privilege is for routines (stored procedures and functions) and not for triggers.
That is it for this part of the series. We stop here and continue in the next part.
Chrys
Related Links
Major in Website DesignWeb Development Course
HTML Course
CSS Course
ECMAScript Course
NEXT