[MDEV-34957] User privileges implemented by role can't execute procedure with event scheduler. - Jira

XML

Word

Printable

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Not a Bug
Affects Version/s: 10.6.18
Fix Version/s: N/A
Component/s: Authentication and Privilege System
Labels:
None
Environment:
AWS RDS for MariaDB 10.6.18

Description

Thank you for development always.

User privileged implemented by role can't execute procedure with event scheduler.

2024-09-19 4:36:42 12263 [ERROR] Event Scheduler: [test_user@%].[event_db.evt_call_pcd] execution failed, user no longer has EVENT privilege.

2024-09-19 4:36:42 12263 [ERROR] Event Scheduler: [test_user@%][event_db.evt_call_pcd] Access denied for user 'test_user'@'%' to database 'event_db'

2024-09-19 4:36:42 12263 [Note] Event Scheduler: [test_user@%].[event_db.evt_call_pcd] event execution failed.

Process of reproduction, follow below script pleaese
`admin` is my admin user for rds

0. Connect to mariadb with administrator user

mysql -h my.rds.host -u admin -p

1. Create database for test

-- Create database

MariaDB [(none)]> CREATE DATABASE event_db;

2. Create role and grant privileges

-- Create role & grant privileges

MariaDB [(none)]> CREATE ROLE priv_role;

MariaDB [(none)]> GRANT USAGE ON *.* TO 'priv_role';

MariaDB [(none)]> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, LOCK TABLES, EXECUTE, CREATE VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON event_db.* TO 'priv_role';

MariaDB [(none)]> FLUSH PRIVILEGES;

MariaDB [(none)]> SHOW GRANTS FOR priv_role;

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Grants for priv_role                                                                                                                                                              |

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO `priv_role`                                                                                                                                                 |

| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, LOCK TABLES, EXECUTE, CREATE VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `event_db`.* TO `priv_role` |

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

2 rows in set (0.001 sec)

3. Create user and grant role's privileges to user

-- Create user & grant role's privileges to user

MariaDB [(none)]> CREATE USER test_user@'%' IDENTIFIED BY '1234';

MariaDB [(none)]> GRANT 'priv_role' TO test_user@'%';

MariaDB [(none)]> SET DEFAULT ROLE 'priv_role' FOR test_user@'%';

MariaDB [(none)]> FLUSH PRIVILEGES;

MariaDB [(none)]> SHOW GRANTS FOR test_user@'%';

+----------------------------------------------------------------------------------------------------------+

| Grants for test_user@%                                                                                   |

+----------------------------------------------------------------------------------------------------------+

| GRANT `priv_role` TO `test_user`@`%`                                                                     |

| GRANT USAGE ON *.* TO `test_user`@`%` IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' |

| SET DEFAULT ROLE `priv_role` FOR `test_user`@`%`                                                         |

+----------------------------------------------------------------------------------------------------------+

3 rows in set (0.001 sec)

4. Create table & procedure(Just insert one row into table)

-- CREATE TABLE event_db.t1

MariaDB [(none)]> CREATE TABLE event_db.t1(i int);

-- CREATE PROCEDURE event_db.test_pcd

MariaDB [(none)]> CREATE OR REPLACE PROCEDURE event_db.test_pcd ()

BEGIN

	INSERT INTO event_db.t1 VALUES(1);

END; //

MariaDB [(none)]> DELIMITER ;

-- TEST CALL PROCEDURE

MariaDB [(none)]> CALL event_db.test_pcd();

Query OK, 1 row affected (0.002 sec)

MariaDB [(none)]> SELECT COUNT(*) FROM event_db.t1;

+----------+

| COUNT(*) |

+----------+

|        1 |

+----------+

1 row in set (0.001 sec)

5. Create event that just call procedrue 4 times

-- Without DEFINER

DELIMITER //

CREATE OR REPLACE EVENT event_db.evt_call_pcd

ON SCHEDULE EVERY 1 DAY STARTS current_timestamp+3

ENABLE

DO

BEGIN

	CALL event_db.test_pcd();

	CALL event_db.test_pcd();

	CALL event_db.test_pcd();

	CALL event_db.test_pcd();

END; //

DELIMITER ;

-- query information_schema.events for checking result of event

-- event was performed successfully because definer was set admin.

SELECT * FROM information_schema.events\G;

       EVENT_CATALOG: def

        EVENT_SCHEMA: event_db

          EVENT_NAME: evt_call_pcd

             DEFINER: admin@%

           TIME_ZONE: UTC

          EVENT_BODY: SQL

    EVENT_DEFINITION: BEGIN

CALL event_db.test_pcd();

CALL event_db.test_pcd();

CALL event_db.test_pcd();

CALL event_db.test_pcd();

END

          EVENT_TYPE: RECURRING

          EXECUTE_AT: NULL

      INTERVAL_VALUE: 1

      INTERVAL_FIELD: DAY

            SQL_MODE: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

              STARTS: 2024-09-19 04:21:27

                ENDS: NULL

              STATUS: ENABLED

       ON_COMPLETION: NOT PRESERVE

             CREATED: 2024-09-13 01:15:25

        LAST_ALTERED: 2024-09-19 04:21:24

       LAST_EXECUTED: 2024-09-19 04:21:27

       EVENT_COMMENT:

          ORIGINATOR: 63823116

CHARACTER_SET_CLIENT: utf8mb3

COLLATION_CONNECTION: utf8mb3_general_ci

  DATABASE_COLLATION: latin1_swedish_ci

3 rows in set (0.005 sec)

6. Count of event_db.t1 when event was created without definer

MariaDB [(none)]> SELECT COUNT(*) FROM event_db.t1;

+----------+

| COUNT(*) |

+----------+

|        5 |

+----------+

1 row in set (0.001 sec)

7. Reconnect to mariadb with user test_user

mysql -h my.rds.host -u test_user -p

-- Check privileges

MariaDB [(none)]> show grants for current_user;

+----------------------------------------------------------------------------------------------------------+

| Grants for test_user@%                                                                                   |

+----------------------------------------------------------------------------------------------------------+

| GRANT `priv_role` TO `test_user`@`%`                                                                     |

| GRANT USAGE ON *.* TO `test_user`@`%` IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' |

| SET DEFAULT ROLE `priv_role` FOR `test_user`@`%`                                                         |

+----------------------------------------------------------------------------------------------------------+

3 rows in set (0.001 sec)

8. Alter event start time

MariaDB [(none)]> ALTER EVENT event_db.evt_call_pcd ON SCHEDULE EVERY 1 DAY STARTS current_timestamp+3;

Query OK, 0 rows affected (0.002 sec)

-- definer changed to test_user.

-- LAST_EXECUTED changed to current_timestamp+3 too.

MariaDB [(none)]> SELECT * FROM information_schema.events\G;

*************************** 1. row ***************************

       EVENT_CATALOG: def

        EVENT_SCHEMA: event_db

          EVENT_NAME: evt_call_pcd

             DEFINER: test_user@%

           TIME_ZONE: UTC

          EVENT_BODY: SQL

    EVENT_DEFINITION: BEGIN

CALL event_db.test_pcd();

CALL event_db.test_pcd();

CALL event_db.test_pcd();

CALL event_db.test_pcd();

END

          EVENT_TYPE: RECURRING

          EXECUTE_AT: NULL

      INTERVAL_VALUE: 1

      INTERVAL_FIELD: DAY

            SQL_MODE: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

              STARTS: 2024-09-19 04:36:42

                ENDS: NULL

              STATUS: ENABLED

       ON_COMPLETION: NOT PRESERVE

             CREATED: 2024-09-13 01:15:25

        LAST_ALTERED: 2024-09-19 04:36:39

       LAST_EXECUTED: 2024-09-19 04:36:42

       EVENT_COMMENT:

          ORIGINATOR: 63823116

CHARACTER_SET_CLIENT: utf8mb3

COLLATION_CONNECTION: utf8mb3_general_ci

  DATABASE_COLLATION: latin1_swedish_ci

1 row in set (0.002 sec)

9. re counter of event_db.t1

-- If event was performed well, count of event_db.t1 will print 9.

-- But count of event_db.t1 is still 5.

MariaDB [(none)]> SELECT COUNT(*) FROM event_db.t1;

+----------+

| COUNT(*) |

+----------+

|        5 |

+----------+

1 row in set (0.001 sec)

10. error_log checking
Even though test_user has sufficient privileges, an error occurs saying that access denied for user.

2024-09-19 4:36:42 12263 [ERROR] Event Scheduler: [test_user@%].[event_db.evt_call_pcd] execution failed, user no longer has EVENT privilege.

2024-09-19 4:36:42 12263 [ERROR] Event Scheduler: [test_user@%][event_db.evt_call_pcd] Access denied for user 'test_user'@'%' to database 'event_db'

2024-09-19 4:36:42 12263 [Note] Event Scheduler: [test_user@%].[event_db.evt_call_pcd] event execution failed.

Attachments

Activity

People

Assignee:: Sergei Golubchik

Reporter:: Lee HyunWoo

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2024-09-19 04:54

Updated:: 2024-09-20 07:10

Resolved:: 2024-09-20 07:10

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.