MySQL Bugs: #14177: grant execute to procedures

Bug #14177	grant execute to procedures - don't work
Submitted:	20 Oct 2005 13:11	Modified:	24 Dec 2005 15:00
Reporter:	hala t	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.15-BK	OS:	Linux (Linux)
Assigned to:	Alexander Nozdrin	CPU Architecture:	Any

Description:
I've got a problems with procedures 
I create procedure and want to give a right to execute it to a simple user 
When I login as this user and want to call this procedure mysql says that i don't have right to use that procedure 
this is an example code that I want to execute :

*****************************************************
TABLES :
create table klient
(
uzytkownik varchar(20),
nazwisko varchar (30),
imie varchar (20), 
pesel  int not null primary key UNIQUE,
adres varchar (40)
)
type =InnoDB;

create table konto
(
saldo DEC (10,2) DEFAULT 0, 
nrkonta  DEC (20,0) UNIQUE   , 
wlasciciel int not null references klient(pesel),
CONSTRAINT wieksze check (saldo>=0)
)
type =InnoDB;

PROCEDURE :

delimiter //
CREATE PROCEDURE  dodawanie
(IN uzytkownik varchar(20), nazwisko varchar (30),imie varchar (20),
pesel int,adres varchar (40),nrkonta DEC (20,0))
begin
insert into klient values (uzytkownik,nazwisko,imie,pesel,adres);
insert into konto  values (0,nrkonta,pesel);
end
//
delimiter ;

grant execute on dodawanie to user1;

mysql -u user1

call dodawanie ('dd','ddf','im',232,'sdsd',11);

How to repeat:
***

Thank you for a bug report. I think, it is a bug, because it is clearly stated in the manual (http://dev.mysql.com/doc/refman/5.0/en/request-access.html):

"A '%' or blank Host  value in either table means “any host.”"

There is no description of the proc_privs table columns interpretation at this page, by the way, but this is the other issue.

So, to repeat the problem, create table and procedure as described by the reporter, than (as root user, who created tables and procedure):

mysql> grant execute on procedure dodawanie to user2;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
[openxs@Fedora 5.0]$ bin/mysql -uuser2
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 5.0.15

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> call dodawanie ('dd','ddf','im',232,'sdsd',11);
ERROR 1370 (42000): execute command denied to user ''@'localhost' for routine 'test.dodawanie'
mysql> exit
Bye

Lets try once more:

[openxs@Fedora 5.0]$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9 to server version: 5.0.15

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> grant execute on procedure dodawanie to 'user2'@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
[openxs@Fedora 5.0]$ bin/mysql -u user2
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10 to server version: 5.0.15

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> call dodawanie ('dd','ddf','im',232,'sdsd',11);
Query OK, 1 row affected (0.05 sec)

mysql> exit
Bye
[openxs@Fedora 5.0]$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11 to server version: 5.0.15

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select * from mysql.procs_priv;
+-----------+------+-------+--------------+--------------+----------------+-----------+---------------------+
| Host      | Db   | User  | Routine_name | Routine_type | Grantor        | Proc_priv | Timestamp           |
+-----------+------+-------+--------------+--------------+----------------+-----------+---------------------+
| %         | test | user1 | dodawanie    | PROCEDURE    | root@localhost | Execute   | 2005-10-20 17:15:50 |
| %         | test | user2 | dodawanie    | PROCEDURE    | root@localhost | Execute   | 2005-10-20 17:17:53 |
| localhost | test | user2 | dodawanie    | PROCEDURE    | root@localhost | Execute   | 2005-10-20 17:22:04 |
+-----------+------+-------+--------------+--------------+----------------+-----------+---------------------+
3 rows in set (0.00 sec)

(Don't mention user1 - the result of testing). But look: % does not work for EXECUTE priviledge as it works for table-related ones. It is either a bug, or it needs to be properly documented.

Verified on 5.0.15-BK (ChangeSet@1.2022, 2005-10-20 11:12:34+05:00, bar@mysql.com).

I can not get what is wrong here. I think, everything works correctly.

I'll try to explain:

1. As a root you created a procedure (it can be any procedure) and
granted an EXECUTE of this procedure to some user:
  - CREATE PROCEDURE p1() SELECT 1;
  - GRANT EXECUTE ON PROCEDURE p1 TO u1;
    (note, that 'u1' has not been known/used before -- it's clear db)

2. Then, you tried to log in as u1:
  - mysql -u u1
  - However, actually, you was logged as anonymous user from localhost
    (''@localhost). And this is correct.
  - You could not execute the procedure and this is also right, because
    you were an anonymous user, not 'u1'.

3. Then, you granted EXECUTE to u1@localhost. This added u1@localhost
  to access-checking tables. So, when you logged in again as u1, you were
  actually logged in as u1@localhost. So, you could execute the procedure.

Yes, it is clearly described in the manual (http://dev.mysql.com/doc/refman/5.0/en/connection-access.html):

"It is a common misconception to think that, for a given username, all entries that explicitly name that user are used first when the server attempts to find a match for the connection. ... As a result, jeffrey is authenticated as an anonymous user, even though he specified a username when connecting."

So, it is not a bug, but intended and documented behaviour. Sorry for misleading verification.