30 Jan

Access Control in MySQL Stored Routines: DEFINER, INVOKER & SQL SECURITY

MySQL Stored Routines (functions and procedures) are not only used for improving performance but also they’re handy when it comes to enhancing security and restricting user access. This post briefs you about security aspects of stored routines by means of example.

By the definition of a Stored Routine,
– DEFINER clause specifies the creator of the stored routine.
– SQL SECURITY clause specifies the execution context of the stored routine.

Now let’s start with an example:

Create a very basic stored routine in test database:

DELIMITER //
CREATE PROCEDURE user_cnt()
BEGIN
SELECT COUNT(*) as total_user FROM mysql.user;
END;//
DELIMITER ;

Our current user is: root@localhost
[ You can see your current user by issuing select CURRENT_USER(); ]

Call the procedure:
CALL user_cnt()\G
total_user: 10

Here, We created a stored routine using root@localhost user.
– DEFINER: The user who creates this procedure. i.e. root@localhost
– SQL SECURITY: Defines under whose privileges the routine will be executed; defaults to DEFINER. i.e. root@localhost

As SQL SECURITY is set to DEFINER, a user even with only EXECUTE permission for routine can call and get the output of the stored routine regardless of whether that user has permission on mysql database or not.

Lets see how this works. Create a “execuser” with limited privileges:
GRANT EXECUTE ON test.* TO 'execuser'@'localhost' IDENTIFIED BY 'execuser' ;
FLUSH PRIVILEGEES;

Login using execuser to mysql prompt and call the procedure created under test database:
mysql> CALL user_cnt()\G
total_user: 10

Now check that below command fired by execuser fails due to lack of privileges:
mysql> select count(*) from mysql.user;
ERROR 1142 (42000): SELECT command denied to user 'execuser'@'localhost' for table 'user'

– Thus stored procedure allows us to restrict users to access tables directly but still getting access to certain data.
– User execuser@localhost is not having permission on mysql database and still he could get the data.
– As the SQL SECURITY was set to DEFINER (default), the execution of the routine happened under the security context of root@localhost user and returned the result.

Is this behaviour something that you don’t want to happen? Hmmm we have a way out here.
Lets rewrite the stored procedure as below:

DELIMITER //
DROP PROCEDURE IF EXISTS user_cnt;
CREATE DEFINER='root'@'localhost' PROCEDURE user_cnt()
SQL SECURITY INVOKER
BEGIN
SELECT COUNT(*) as total_user FROM mysql.user;
END;//
DELIMITER ;

– Did you note “SQL SECURITY INVOKER”!! That’s our saviour.
– We’ve specified the DEFINER attribute explicitly.
– SQL SECURITY decides under whose privileges the stored routine gets executed! Here it is INVOKER, the user that CALLs the routine!

Let’s check through execuser@localhost user:
mysql> call user_cnt();
ERROR 1142 (42000): SELECT command denied to user 'execuser'@'localhost' for table 'user'

So, this is now pretty clear that the stored routine tried to execute the SELECT query under INVOKER’s (execuser@localhost) privileges; and the routine failed as the INVOKER has no privileges on user table.

Finally, a few points:
Together DEFINER & SQL SECURITY clauses define the security context to be used during routine execution time.
The DEFINER attribute defaults to the current user & defines the creator of the Stored Routine.
SQL SECURITY defines the execution permission of the stored routine and defaults to DEFINER.

I hope this cleared the basics; thanks for reading.

26 Jan

Xtreme Movie Review: Agneepath Agneepath Agneepath (2012)

Kancha Agneepath 2012

Agneepath… Agneepath… Agneepath…

Vruksh ho bhale khade,
ho ghane ho bade,
Ek patra chhanh bhi,
mang mat, mang mat, mang mat,
Agnipath, Agnipath, Agnipath…

Oh yes… This action-thriller will surely make you feel the word “Agneepath”. The movie, as promoted, has lived on REVENGE and lived upto the mark.
Sanjay Dutt as ‘Kancha’, Hritik Roshan as ‘Vijay Dinanath Chauhan’ and Rishi Kapoor as ‘Rauf Lala’ are well written characters & brilliantly played by each of them.
Scripts, Music, Action, and everything that makes a movie are at place; and holds it nicely.
The movie grabs you since the beginning till the end!

The emotions and the feeling of revenge are extra ordinarily sharpen, stretched to the limits in the script and greatly executed.
Vijay Dinanath Chauhan, a classic character played by the great Amitabh Bachchan has always left a great impact on the minds of viewers. And this remake, Agneepath (2012), is undisputedly effective, well directed and fully enjoyable movie.

Tu na thakega kabhi,
tu na thamega kabhi,
tu na mudega kabhi,
Kar shapath, Kar shapath, Kar shapath,
Agnipath, Agnipath, Agnipath…

“Vijay Deenanath Chauhan, poora naam, baap ka naam Deenanath Chauhan, gaon Mandwa.”

Vijay’s father, Dinanath Chauhan, was a teacher in the village Mandwa who taught him the path of fire. Kancha hangs Diananath & kicks him out of his way to become drug mafia and rule the people. Vijay leaves to Mumbai with her mother and eventually happens to know Rauf Lala after following Kancha in Mumbai. Vijay plays & uses Rauf to reach Kancha and challenges Kancha at the end & completes the REVENGE… as usual :)… During the movie Vijay is well supported by Kaali.

The movie is better watched :)

Sanjay Dutt AKA Kancha is HUGE. Sanjay has done a great job with his look, size & act… :)
The way Kancha enters holding Holy Geeta makes a great impact. Kancha recites a few funny dialogues like “To ramji Lankaa padhaar gaye hain; dekho vaanar to nahin aaye!”

Kancha Agneepath 2012-tu kya lekar aaya tha, tu kya lekar jaega
[ PS: this is my facebook cover image :) ]


tum kya leke aaye the…
tum kya leke jaoge…
rahejayega toh sirf ek he insaan…
sarva sakti sali sarva saktimaa…
kaancha…

As they have done a great job of filming “REVENGE”, some of the scenes are so intense that “a few girls” won’t like that… :) But that was quite building the roots of the story and let you feel heat of Agneepath.

About “Chikni Chameli”, surely I’d love to see Katrina 1000 times but not a single time like in this song! For me the dance steps were pathetic and music is good but a complete copy! Ofcourse music director duo Ajay-Atul copied from their marathi hit number “Kombadi”.
All songs are good, but my favourite is “Abhi Mujh Mein Kahin” by Sonu Nigam.
BTW did you notice the statutory warnings “Smoking is injurious to health” during the song after Katrina lit the bidis!!

Yes ofcourse, Priyanka as Kaali & Om Puri as Gaaytonde are good too…
Agneepath (1990) & Agneepath (2012) both are my favourites but which Vijay do you think is better?
Thanks Karan Malhotra & Karan Johar for the great remake.

Ye mahan drushya hai,
Chal raha manushya hai,
ashru, swed, rakt se,
lathpat, lathpat, lathpat..
Agnipath, Agnipath, Agnipath…

WOW awesome movie after a long time…

-- Kedar Vaijanapurkar --