[MDEV-17674] GRANT ON PROCEDURE can be erroneously interpreted as GRANT ON TABLE Created: 2018-11-12  Updated: 2023-04-27

Status: Open
Project: MariaDB Server
Component/s: Parser
Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3, 10.4
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Blocks
is blocked by MDEV-17677 Keywords are parsed as identifiers wh... Open

 Description   

I run this script:

DROP USER IF EXISTS user1@localhost;
 
DROP PROCEDURE IF EXISTS p1;
 
CREATE PROCEDURE p1() BEGIN END;
 
CREATE USER user1@localhost;
 
GRANT ALL PRIVILEGES ON procedure .p1 TO user1@localhost;
 
SHOW GRANTS FOR user1@localhost;
 
DROP USER user1@localhost;

It prints the following output in SHOW GRANTS:

+------------------------------------------------------------------------------+
 
| Grants for user1@localhost                                                   |
 
+------------------------------------------------------------------------------+
 
| GRANT USAGE ON *.* TO 'user1'@'localhost'                                    |
 
| GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `test`.`p1` TO 'user1'@'localhost' |
 
+------------------------------------------------------------------------------+

Looks fine so far. The procedure name ".p1" was translated to test.p1 (i.e. took the current database name).

Now I add run a similar script, but remove the space character in the GRANT command, between procedure and .p1:

DROP USER IF EXISTS user1@localhost;
 
DROP PROCEDURE IF EXISTS p1;
 
CREATE PROCEDURE p1() BEGIN END;
 
CREATE USER user1@localhost;
 
GRANT ALL PRIVILEGES ON procedure.p1 TO user1@localhost;
 
SHOW GRANTS FOR user1@localhost;
 
DROP USER user1@localhost;


+-----------------------------------------------------------------+
 
| Grants for user1@localhost                                      |
 
+-----------------------------------------------------------------+
 
| GRANT USAGE ON *.* TO 'user1'@'localhost'                       |
 
| GRANT ALL PRIVILEGES ON `procedure`.`p1` TO 'user1'@'localhost' |
 
+-----------------------------------------------------------------+

Looks wrong. It was translated to:

GRANT ALL PRIVILEGES ON TABLE `procedure`.`p1` TO 'user1'@'localhost'

It should not happen. The keyword PROCEDURE is reserved in MariaDB. It should never be associated with the identifier `procedure`.

So 

ON PROCEDURE.p1

should be interpreted as 

ON PROCEDURE current_database.p1

.

 Comments   
Comment by Alexander Barkov [ 2018-11-12 ]

The same phenomena is repeatable with FUNCTION:

DROP USER IF EXISTS user1@localhost;
 
DROP FUNCTION IF EXISTS f1;
 
CREATE FUNCTION f1() RETURNS INT RETURN 1;
 
CREATE USER user1@localhost;
 
GRANT ALL PRIVILEGES ON function .f1 TO user1@localhost;
 
SHOW GRANTS FOR user1@localhost;
 
DROP USER user1@localhost;

It prints the following output in SHOW GRANTS:

+-----------------------------------------------------------------------------+
 
| Grants for user1@localhost                                                  |
 
+-----------------------------------------------------------------------------+
 
| GRANT USAGE ON *.* TO 'user1'@'localhost'                                   |
 
| GRANT EXECUTE, ALTER ROUTINE ON FUNCTION `test`.`f1` TO 'user1'@'localhost' |
 
+-----------------------------------------------------------------------------+


DROP USER IF EXISTS user1@localhost;
 
DROP FUNCTION IF EXISTS f1;
 
CREATE FUNCTION f1() RETURNS INT RETURN 1;
 
CREATE USER user1@localhost;
 
GRANT ALL PRIVILEGES ON function.f1 TO user1@localhost;
 
SHOW GRANTS FOR user1@localhost;
 
DROP USER user1@localhost;


+----------------------------------------------------------------+
 
| Grants for user1@localhost                                     |
 
+----------------------------------------------------------------+
 
| GRANT USAGE ON *.* TO 'user1'@'localhost'                      |
 
| GRANT ALL PRIVILEGES ON `function`.`f1` TO 'user1'@'localhost' |
 
+----------------------------------------------------------------+

Note, the keyword FUNCTION (unlike PROCEDURE) is not reserved in MariaDB. So this grammar is ambiguous.

But it should probably also parse 

ON FUNCTION.f1

as 

ON FUNCTION current_database.f1

rather than 

ON TABLE FUNCTION.f1

.

Comment by Elena Stepanova [ 2018-11-12 ]

Same apples to TABLE as such:

MariaDB [test]> grant all privileges on table .t1 to foo@localhost;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> grant all privileges on table.t2 to foo@localhost;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> show grants for foo@localhost;
 
+-----------------------------------------------------------+
 
| Grants for foo@localhost                                  |
 
+-----------------------------------------------------------+
 
| GRANT USAGE ON *.* TO 'foo'@'localhost'                   |
 
| GRANT ALL PRIVILEGES ON `table`.`t2` TO 'foo'@'localhost' |
 
| GRANT ALL PRIVILEGES ON `test`.`t1` TO 'foo'@'localhost'  |
 
+-----------------------------------------------------------+
 
3 rows in set (0.00 sec)

But should procedure.p (or even procedure .p, for that matter) really work if you interpret procedure as a special word? I'd expect it to throw a syntax error, just as it does when you do

create procedure.p1() begin end;

or

create procedure .p1() begin end;

Comment by Alexander Barkov [ 2018-11-12 ]

The problem is repeatable with any keyword:

DROP USER IF EXISTS user1@localhost;
 
CREATE USER user1@localhost;
 
GRANT ALL PRIVILEGES ON select.f1 TO user1@localhost;
 
SHOW GRANTS FOR user1@localhost;
 
DROP USER user1@localhost;


+--------------------------------------------------------------+
 
| Grants for user1@localhost                                   |
 
+--------------------------------------------------------------+
 
| GRANT USAGE ON *.* TO 'user1'@'localhost'                    |
 
| GRANT ALL PRIVILEGES ON `select`.`f1` TO 'user1'@'localhost' |
 
+--------------------------------------------------------------+

Notice, select.f1 was erroneously interpreted as TABLE `select`.`f1`. It should return a syntax error instead.

Generated at Thu Feb 08 08:38:14 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.