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

Introduction

This is part 4 of my series, MySQL Access Privileges. In this part of the series, we look at Routines and Privileges in MySQL. I assume you have read the previous parts of the series before reaching here, as this is a continuation. Routines are stored procedures and functions. In MySQL, a trigger is like a part of a table and is coded similarly to a stored procedure. I will also address the trigger and its privilege in this tutorial.

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;//
delimiter ;

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
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

delimiter //
CREATE TRIGGER InformManager
AFTER UPDATE
ON Products
FOR EACH ROW
BEGIN
    IF NEW.Quantity <= NEW.ReorderLevel THEN
        INSERT INTO Manager SET ProductName = NEW.ProductName, ReorderLevel = NEW.ReorderLevel, CurrentLevel = NEW.Quantity;
    END IF;
END;//
delimiter ;

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 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.

Chrys

Broad Network

Related Articles

Routines and Privileges in MySQL

MySQL Access Privileges - Part 4

Introduction

Related Links

Comments