[MDEV-19479] Privileges not applied correctly for sequences when altering a column to take a default value from a sequence Created: 2019-05-15  Updated: 2024-01-12

Status: Confirmed
Project: MariaDB Server
Component/s: Authentication and Privilege System, Data Definition - Alter Table, Sequences
Affects Version/s: 10.3.14, 10.3, 10.4
Fix Version/s: 10.4

Type: Bug Priority: Minor
Reporter: Niels Grewe Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 3
Labels: None
Environment:

Mac OS 10.14.4


 Description   

I've run into a little issue related to how privileges are applied to sequences. It seems that for sequences, general privileges on all tables in a DB are not evaluated correctly.

The following script creates a seq_bug user and database, and grants the user all privileges on the DB:

DROP DATABASE IF EXISTS seq_bug;
 
DROP USER IF EXISTs 'seq_bug'@'localhost';
 
CREATE DATABASE seq_bug;
 
CREATE USER 'seq_bug'@'localhost' IDENTIFIED BY 'foo';
 
GRANT ALL PRIVILEGES ON seq_bug.* TO 'seq_bug'@'localhost';

Now logging into the server as seq_bug, try to execute this DDL: 

CREATE TABLE seq_bug (id INT);
 
CREATE SEQUENCE s1;
 
ALTER TABLE seq_bug ALTER COLUMN id SET DEFAULT (NEXT VALUE FOR s1);

The expected result would be that the operation completes correctly, what in fact happens is the following:

ERROR 1142 (42000) at line 3: ALTER command denied to user 'seq_bug'@'localhost' for table 's1'

I was able to work around the issue by granting the user explicit privileges to use that table, so using the following allows the ALTER TABLE statement to complete successfully:

DROP DATABASE IF EXISTS seq_bug;
 
DROP USER IF EXISTs 'seq_bug'@'localhost';
 
CREATE DATABASE seq_bug;
 
CREATE USER 'seq_bug'@'localhost' IDENTIFIED BY 'foo';
 
GRANT ALL PRIVILEGES ON seq_bug.* TO 'seq_bug'@'localhost';
 
GRANT ALL PRIVILEGES ON seq_bug.s1 TO 'seq_bug'@'localhost';

The root user is also capable of executing the ALTER TABLE without extra privileges.

 Comments   
Comment by Elena Stepanova [ 2019-05-16 ]

Thanks for the report and test case.

Comment by Jan-Hendrik Diederich [ 2020-09-16 ]

I just had to give my limited powers user the INSERT right for the sequence table.
Like:
GRANT INSERT ON database.test_sequence TO 'user'@'localhost';

With MariaDB 10.4.14.

Whatever, since we normally don't have root rights, and often no GRANT rights, we can't GRANT rights, so we still need this bug fixed.

Comment by Roman Pavlov [ 2020-12-24 ]

I can confirm this issue for MariaDB 10.5.6 on FreeBSD 12.1

Comment by Slav Umni [ 2022-10-14 ]

Confirm this for MariaDB 10.9.3

Comment by Vladimir G. [ 2024-01-12 ]

Issue confirmed on MariaDB 11.2.2. Fresh install on Rocky Linux 9.3 (from MariaDB repo using mariadb_repo_setup script).

For this ALTER TABLE usage, unless GRANT is given to specific object, it won´t work.

Here my tests:

– As user root on the database
CREATE OR REPLACE USER 'test_admin_user'@'%' IDENTIFIED BY 'foo';
– I need test_admin_user to fully manage any database with prefix s_
GRANT ALL PRIVILEGES ON `s_%`.* TO 'test_admin_user'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

– Reconnect as user test_admin_user to the database
CREATE OR REPLACE DATABASE s_testdb;
USE s_testdb;

CREATE TABLE seq_bug (id INT);
CREATE SEQUENCE some_seq;

– Next line fails with message "SQL Error [1142] [42000]: (conn=19) ALTER command denied to user 'test_admin_user'@'X.X.X.X' for table `s_testdb`.`some_seq`"
ALTER TABLE seq_bug ALTER COLUMN id SET DEFAULT (NEXT VALUE FOR some_seq);

GRANT ALL PRIVILEGES ON `s_%`.some_seq TO 'test_admin_user'@'%';
– Next line continues to fail
ALTER TABLE seq_bug ALTER COLUMN id SET DEFAULT (NEXT VALUE FOR some_seq);

GRANT ALL PRIVILEGES ON `s_testdb`.some_seq TO 'test_admin_user'@'%';
– Next line finally succeeds
ALTER TABLE seq_bug ALTER COLUMN id SET DEFAULT (NEXT VALUE FOR some_seq);

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