mirror of
https://github.com/MariaDB/server.git
synced 2026-01-24 04:14:49 +01:00
ca63e2c627
SELECT privilege Analysis: When setup_fields() is called, the want_privilege is set to NO_ACL, so correct priveleges are not checked. Fix: Since RETURNING requires SELECT_ACL privelige, when we are setting up the returning fields for the given query, set want_privilege to SELECT_ACL. Reset to original value of want_privilege once done.
166 lines
7.6 KiB
Text
166 lines
7.6 KiB
Text
set local sql_mode="";
|
|
set global sql_mode="";
|
|
drop table if exists t1;
|
|
create table t1 (n int);
|
|
create view v1 as select * from t1;
|
|
insert delayed into v1 values (1);
|
|
ERROR HY000: 'test.v1' is not of type 'BASE TABLE'
|
|
drop table t1;
|
|
drop view v1;
|
|
CREATE DATABASE meow;
|
|
connect root,localhost,root,,meow;
|
|
connection root;
|
|
CREATE TABLE table_target ( mexs_id CHAR(8), messzeit TIMESTAMP, PRIMARY KEY (mexs_id));
|
|
CREATE TABLE table_target2 ( mexs_id CHAR(8), messzeit TIMESTAMP, PRIMARY KEY (mexs_id));
|
|
CREATE TABLE table_target3 ( mexs_id CHAR(8), messzeit TIMESTAMP, PRIMARY KEY (mexs_id));
|
|
CREATE VIEW view_target2 AS SELECT mexs_id,messzeit FROM table_target2;
|
|
CREATE SQL SECURITY INVOKER VIEW view_target3 AS SELECT mexs_id,messzeit FROM table_target3;
|
|
CREATE TABLE table_stations ( mexs_id VARCHAR(8), icao VARCHAR(4), country CHAR(2), PRIMARY KEY (mexs_id), UNIQUE KEY icao (icao), KEY country (country), CONSTRAINT stations_ibfk_8 FOREIGN KEY (country) REFERENCES countries (country) ON UPDATE CASCADE);
|
|
INSERT INTO table_stations VALUES ('87654321','XXXX','YY');
|
|
CREATE TABLE table_countries ( country CHAR(2), iso_short_en VARCHAR(64), PRIMARY KEY (country));
|
|
INSERT INTO table_countries VALUES ('YY','Entenhausen');
|
|
CREATE ALGORITHM=MERGE SQL SECURITY INVOKER VIEW view_stations AS select table_stations.mexs_id AS mexs_id, table_stations.icao AS icao, table_stations.country AS landescode from (table_stations join table_countries on((table_stations.country = table_countries.country)));
|
|
CREATE TABLE table_source ( id varchar(4), datetime TIMESTAMP, PRIMARY KEY (id));
|
|
INSERT INTO table_source VALUES ('XXXX','2006-07-12 07:50:00');
|
|
GRANT SELECT ON table_source TO user20989@localhost;
|
|
GRANT SELECT ON table_countries TO user20989@localhost;
|
|
GRANT SELECT ON table_stations TO user20989@localhost;
|
|
GRANT SELECT ON view_stations TO user20989@localhost;
|
|
GRANT SELECT ON table_target TO user20989@localhost;
|
|
GRANT SELECT ON table_target2 TO user20989@localhost;
|
|
GRANT INSERT,DELETE,SELECT ON view_target3 TO user20989@localhost;
|
|
connect user20989,localhost,user20989,,meow;
|
|
connection user20989;
|
|
REPLACE INTO table_target
|
|
SELECT stations.mexs_id AS mexs_id, datetime AS messzeit
|
|
FROM table_source
|
|
INNER JOIN view_stations AS stations
|
|
ON table_source.id = stations.icao
|
|
LEFT JOIN table_target AS old
|
|
USING (mexs_id);
|
|
ERROR 42000: INSERT, DELETE command denied to user 'user20989'@'localhost' for table `meow`.`table_target`
|
|
REPLACE INTO view_target2
|
|
SELECT stations.mexs_id AS mexs_id, datetime AS messzeit
|
|
FROM table_source
|
|
INNER JOIN view_stations AS stations
|
|
ON table_source.id = stations.icao
|
|
LEFT JOIN view_target2 AS old
|
|
USING (mexs_id);
|
|
ERROR 42000: INSERT, DELETE command denied to user 'user20989'@'localhost' for table `meow`.`view_target2`
|
|
REPLACE INTO view_target3
|
|
SELECT stations.mexs_id AS mexs_id, datetime AS messzeit
|
|
FROM table_source
|
|
INNER JOIN view_stations AS stations
|
|
ON table_source.id = stations.icao
|
|
LEFT JOIN view_target3 AS old
|
|
USING (mexs_id);
|
|
ERROR HY000: View 'meow.view_target3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
|
|
connection root;
|
|
disconnect user20989;
|
|
GRANT INSERT,DELETE ON table_target TO user20989@localhost;
|
|
GRANT INSERT,DELETE,SELECT ON view_target2 TO user20989@localhost;
|
|
GRANT INSERT,DELETE,SELECT ON table_target3 TO user20989@localhost;
|
|
connect user20989,localhost,user20989,,meow;
|
|
connection user20989;
|
|
REPLACE INTO table_target
|
|
SELECT stations.mexs_id AS mexs_id, datetime AS messzeit
|
|
FROM table_source
|
|
INNER JOIN view_stations AS stations
|
|
ON table_source.id = stations.icao
|
|
LEFT JOIN table_target AS old
|
|
USING (mexs_id);
|
|
REPLACE INTO table_target2 VALUES ('00X45Y78','2006-07-12 07:50:00');
|
|
ERROR 42000: INSERT, DELETE command denied to user 'user20989'@'localhost' for table `meow`.`table_target2`
|
|
REPLACE INTO view_target2 VALUES ('12X45Y78','2006-07-12 07:50:00');
|
|
SELECT stations.mexs_id AS mexs_id, datetime AS messzeit
|
|
FROM table_source
|
|
INNER JOIN view_stations AS stations
|
|
ON table_source.id = stations.icao
|
|
LEFT JOIN view_target2 AS old
|
|
USING (mexs_id);
|
|
mexs_id messzeit
|
|
87654321 2006-07-12 07:50:00
|
|
REPLACE INTO view_target2
|
|
SELECT stations.mexs_id AS mexs_id, datetime AS messzeit
|
|
FROM table_source
|
|
INNER JOIN view_stations AS stations
|
|
ON table_source.id = stations.icao
|
|
LEFT JOIN view_target2 AS old
|
|
USING (mexs_id);
|
|
REPLACE INTO view_target3
|
|
SELECT stations.mexs_id AS mexs_id, datetime AS messzeit
|
|
FROM table_source
|
|
INNER JOIN view_stations AS stations
|
|
ON table_source.id = stations.icao
|
|
LEFT JOIN view_target3 AS old
|
|
USING (mexs_id);
|
|
connection root;
|
|
disconnect user20989;
|
|
SELECT * FROM table_target;
|
|
mexs_id messzeit
|
|
87654321 2006-07-12 07:50:00
|
|
SELECT * FROM view_target2;
|
|
mexs_id messzeit
|
|
12X45Y78 2006-07-12 07:50:00
|
|
87654321 2006-07-12 07:50:00
|
|
SELECT * FROM view_target3;
|
|
mexs_id messzeit
|
|
87654321 2006-07-12 07:50:00
|
|
DROP VIEW view_stations;
|
|
DROP TABLE table_source;
|
|
DROP TABLE table_countries;
|
|
DROP TABLE table_stations;
|
|
DROP TABLE table_target;
|
|
DROP TABLE table_target2;
|
|
DROP TABLE table_target3;
|
|
DROP VIEW view_target2;
|
|
DROP VIEW view_target3;
|
|
DROP USER user20989@localhost;
|
|
disconnect root;
|
|
connection default;
|
|
DROP DATABASE meow;
|
|
set local sql_mode=default;
|
|
set global sql_mode=default;
|
|
#
|
|
# MDEV-37950: INSERT ... RETURNING exposes columns for which
|
|
# the user lacks SELECT privilege
|
|
#
|
|
CREATE USER regular;
|
|
GRANT INSERT ON *.* TO regular;
|
|
GRANT DELETE ON *.* TO regular;
|
|
CREATE DATABASE test1;
|
|
DROP TABLE IF EXISTS test1.t_trigger_test1;
|
|
Warnings:
|
|
Note 1051 Unknown table 'test1.t_trigger_test1'
|
|
CREATE TABLE test1.t_trigger_test1 (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(50),
|
|
note VARCHAR(100)
|
|
);
|
|
CREATE TRIGGER test1.trg_before_insert
|
|
BEFORE INSERT ON test1.t_trigger_test1
|
|
FOR EACH ROW
|
|
BEGIN
|
|
SET NEW.name = CONCAT('BEFORE_', NEW.name);
|
|
END |
|
|
INSERT INTO test1.t_trigger_test1 (name) VALUES ('Alice') RETURNING *;
|
|
ERROR 42000: SELECT command denied to user 'regular'@'localhost' for column 'id' in table 't_trigger_test1'
|
|
INSERT INTO test1.t_trigger_test1 (name) VALUES ('Alice') RETURNING id, name, note;
|
|
ERROR 42000: SELECT command denied to user 'regular'@'localhost' for column 'id' in table 't_trigger_test1'
|
|
# same for DELETE because delete with "WHERE" still requires to
|
|
# read from the table, which basically means having select privileges
|
|
DELETE FROM test1.t_trigger_test1 WHERE id=1;
|
|
ERROR 42000: SELECT command denied to user 'regular'@'localhost' for column 'id' in table 't_trigger_test1'
|
|
DELETE FROM test1.t_trigger_test1 WHERE id=1 RETURNING id;
|
|
ERROR 42000: SELECT command denied to user 'regular'@'localhost' for column 'id' in table 't_trigger_test1'
|
|
DELETE FROM test1.t_trigger_test1 WHERE id=1 RETURNING *;
|
|
ERROR 42000: SELECT command denied to user 'regular'@'localhost' for column 'id' in table 't_trigger_test1'
|
|
DELETE FROM test1.t_trigger_test1 RETURNING *;
|
|
ERROR 42000: SELECT command denied to user 'regular'@'localhost' for column 'id' in table 't_trigger_test1'
|
|
DELETE FROM test1.t_trigger_test1 RETURNING id;
|
|
ERROR 42000: SELECT command denied to user 'regular'@'localhost' for column 'id' in table 't_trigger_test1'
|
|
DELETE FROM test1.t_trigger_test1;
|
|
DROP TRIGGER test1.trg_before_insert;
|
|
DROP TABLE test1.t_trigger_test1;
|
|
DROP USER regular;
|
|
DROP DATABASE test1;
|