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
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`(
SELECT user_id, user_fullname, date_last_login FROM users WHERE
user_name = P_user_name AND user_password = P_password;