MySQL Forums
Forum List  »  Security

Security privileges on stored procedure foibles?
Posted by:
Date: October 30, 2008 03:12AM

Hi all,

I have a following script:

------------------------------------------------------------------------
-- Configure definer user
CREATE USER 'mydefineruser'@'localhost' IDENTIFIED BY PASSWORD '*F3A2A51A9B0F2BE2468926B4132313728C250DBF';

-- Configure the web app user whose credentials will be used by the web app to connect and execute sprocs.
CREATE USER 'someuser'@'localhost' IDENTIFIED BY PASSWORD '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB';

-- CREATE DATABASE
CREATE DATABASE IF NOT EXISTS testdb;
USE testdb;

-- CREATE TABLES
CREATE TABLE users (
`UserId` BIGINT(20) NOT NULL AUTO_INCREMENT,
`Name` VARCHAR(128) NOT NULL,
`Email` VARCHAR(128) NOT NULL,
`Password` VARCHAR(128) NOT NULL,
`Salt` VARCHAR(64) NOT NULL,
PRIMARY KEY(`UserId`)
)
ENGINE = InnoDB
COMMENT = 'This table stores the user logon info';

CREATE UNIQUE INDEX `IDX_user1_1` ON users(`Email`);

delimiter //

-- Select user password and salt
CREATE DEFINER='mydefineruser'@'localhost' PROCEDURE GetUserPasswordSalt (in userEmail VARCHAR(128))
BEGIN
SELECT Password, Salt FROM users WHERE Email = userEmail;
END;
//

delimiter ;

GRANT SELECT, INSERT, UPDATE, DELETE ON tempdb.users TO 'mydefineruser'@'localhost';

GRANT EXECUTE ON testdb.GetUserPasswordSalt TO 'someuser'@'localhost';
------------------------------------------------------------------------
Basically I am trying to create 2 users. One will be used as a definer user and he will have ISUD permissions on the 'testdb'. I also have a 'someuser' who will be an web app user. This user will only have Execute permission on the SPROCS and nothing more. (I have ommitted the creation of more tables and SPROCS for brevity but they follow the same general patern.)

Accroding to the books and online docs that I have read, the above configuration should work, unless, of course I am seriously mistaken:). So when I run my webapp, on the very first need to connect and execute a SPROC I am getting the error:

"[MySqlException: #42000 Access denied for user 'someuser'@'localhost' to database 'testdb']

What am I doing wrong here? I cannot seem to figure out. If I understand this correctly the user 'someuser' does not need an access to the db. It only needs the Execute privilege on the SPROCS. The security definer on the other hand will need some privileges to manipulate the tables. Any help is appreciated. Is this a bug? Here is the version of MySQL that I am running:

mysql Ver 14.12 Distrib 5.0.24a, for Win32 (ia32)

Kind regards,
Archil

Options: ReplyQuote


Subject
Views
Written By
Posted
Security privileges on stored procedure foibles?
6173
October 30, 2008 03:12AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.