[MDEV-17674] GRANT ON PROCEDURE can be erroneously interpreted as GRANT ON TABLE - Jira

Details

Type: Bug
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
Fix Version/s: 10.4(EOL)
Component/s: Parser
Labels:
None

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

.

Attachments

Issue Links

is blocked by

MDEV-17677 Keywords are parsed as identifiers when followed by a dot

Open

Activity

Ascending order - Click to sort in descending order

Alexander Barkov added a comment - 2018-11-12 10:43

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

.

Alexander Barkov added a comment - 2018-11-12 10:43 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 .

Elena Stepanova added a comment - 2018-11-12 11:43

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;

Elena Stepanova added a comment - 2018-11-12 11:43 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 ;

Alexander Barkov added a comment - 2018-11-12 11:54 - edited

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.

Alexander Barkov added a comment - 2018-11-12 11:54 - edited 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.

MariaDB Server

GRANT ON PROCEDURE can be erroneously interpreted as GRANT ON TABLE

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration