Routines and Privileges in MySQL
MySQL Access Privileges - Part 4
Foreword: In this part of the series, we look at Routines and Privileges in MySQL.
By: Chrysanthus Date Published: 18 Jul 2015
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
CREATE PROCEDURE checkshortage
IN numberOfRows INTEGER
# 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
WHERE ID = i
INTO QtyRequestVar, QtyAvailVar;
SET QtyRemainVar = QtyAvailVar – QtyRequestVar;
UPDATE SaleDetailsTemp SET QtyRemain= QtyRemainVar WHERE ID = i;
SET i = i + 1;
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 Mysqlcom 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 it is 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 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 with GRANT OPTION;
Note the reserved word, PROCEDURE and its position in this statement.
Example of a Trigger
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
CREATE TRIGGER InformManager
FOR EACH ROW
IF NEW.Quantity <= NEW.ReorderLevel THEN
INSERT INTO Manager SET ProductName = NEW.ProductName, ReorderLevel = NEW.ReorderLevel, CurrentLevel = NEW.Quantity;
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.
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 database 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;
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;
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. The EXECUTE “privilege” is for routines (stored procedures and functions) and not really for triggers.
That is it for this part of the series. We stop here and continue in the next part.
Related LinksImplementing Database in MySQL
Programming in MySQL
Backup Basics in MySQL
MySQL Access Privileges
Regular Expressions in MySQL
Date and Time in MySQL
Event in MySQL
PurePerl MySQL API Prepared Statements
More Related Links
PurePerl MySQL Command Line Tool
Major in Website Design
Perl Course - Optimized
Web Development Course