List:General Discussion« Previous MessageNext Message »
From:Gary W. Smith Date:February 28 2007 9:43am
Subject:Stored proc permissions question
View as plain text  
I have a set of tables that contain sensitive user information.  I need
to use this data for validation BUT I don't want the end user to have
access to read this data.  In MSSQL I used to be able to create a stored
proc to do the work (even though the user didn't have access to the
table).  I was wondering if there is a way to do this in MySQL.

In a nut shell, this table contains user account information included
encoded password.  I want to be able to pass a username and password to
the stored proc and return the valid user ID (or no RS of none is
found).

Basically, here is the stored proc.  It works, but only for root.  Is it
possible to allow user to execute this without having SELECT access to
the table users?  If so, how?


CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_testlogin`(
  P_user_name VARCHAR(32),
  P_password VARCHAR(32)
)
BEGIN
  SELECT user_id, user_fullname, date_last_login FROM users WHERE
user_name = P_user_name AND user_password = P_password;
END
Thread
Stored proc permissions questionGary W. Smith28 Feb
  • Re: Stored proc permissions questionChristian Hammers28 Feb
    • RE: Stored proc permissions questionGary W. Smith28 Feb
      • RE: Stored proc permissions questionGary W. Smith28 Feb
      • Re: Stored proc permissions questionChristian Hammers28 Feb
        • RE: Stored proc permissions questionGary W. Smith28 Feb