TiDB is an open-source, cloud-native, distributed, MySQL-Compatible database for elastic scale and real-time analytics. Try AI-powered Chat2Query free at : https://www.pingcap.com/tidb-serverless/
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
Create users and grant some permissions.(root user)
CREATE USER 'db_a'@'%';
CREATE USER 'db_b'@'%';
GRANT ALL PRIVILEGES ON `db_a`.* TO 'db_a'@'%'
GRANT ALL PRIVILEGES ON `db_b`.* TO 'db_a'@'%'
GRANT ALL PRIVILEGES ON `db_b`.* TO 'db_b'@'%'
GRANT ALL PRIVILEGES ON `db_b`.* TO 'db_b'@'%'
SHOW GRANTS FOR 'db_a'@'%';
+------------------------------------------------+
| Grants for db_a@% |
+------------------------------------------------+
| GRANT USAGE ON *.* TO 'db_a'@'%' |
| GRANT ALL PRIVILEGES ON `db_a`.* TO 'db_a'@'%' |
| GRANT ALL PRIVILEGES ON `db_b`.* TO 'db_a'@'%' |
+------------------------------------------------+
SHOW GRANTS FOR 'db_b'@'%';
+------------------------------------------------+
| Grants for db_b@% |
+------------------------------------------------+
| GRANT USAGE ON *.* TO 'db_b'@'%' |
| GRANT ALL PRIVILEGES ON `db_a`.* TO 'db_b'@'%' |
| GRANT ALL PRIVILEGES ON `db_b`.* TO 'db_b'@'%' |
+------------------------------------------------+
Create views.(root user)
create database db_a;
create database db_b;
use db_a;
CREATE TABLE `tmp_table1` (
`id` decimal(18,0) NOT NULL,
`row_1` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
create ALGORITHM=UNDEFINED DEFINER=`db_a`@`%` SQL SECURITY DEFINER VIEW view_test_v1 as (
with rs1 as(
select otn.*
from tmp_table1 otn
)
select ojt.* from rs1 ojt
)
2. What did you expect to see? (Required)
use db_b;
select * from db_a.view_test_v1;
-- Get result
3. What did you see instead (Required)
show create table db_a.view_test_v1
-- CREATE ALGORITHM=UNDEFINED DEFINER=`db_a`@`%` SQL SECURITY DEFINER VIEW `view_test_v1` (`row_1`) AS (WITH `rs1` AS (SELECT `db_a`.`otn`.`id` AS `id`,`db_a`.`otn`.`row_1` AS `row_1` FROM `db_a`.`tmp_table1` AS `otn`), `rs2` AS (SELECT `oon`.`row_1` AS `row_1` FROM `db_a`.`tmp_table1` AS `oon` LEFT JOIN `rs1` AS `tfn` ON `oon`.`row_1`=`tfn`.`row_1`) SELECT `db_a`.`ojt`.`row_1` AS `row_1` FROM `rs2` AS `ojt`)
...And if I query this view from db_a, the following error occurs:
use db_b;
select * from db_a.view_test_v1;
(1356, "View 'db_a.view_test_v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them")
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
Create users and grant some permissions.(root user)
Create views.(root user)
2. What did you expect to see? (Required)
3. What did you see instead (Required)
...And if I query this view from db_a, the following error occurs:
4. What is your TiDB version? (Required)
+-----------------------------------------------------------+ | TIDB_VERSION() | +-----------------------------------------------------------+ | Release Version: v8.1.0 | | Edition: Community | | Git Commit Hash: 945d07c5d5c7a1ae212f6013adfb187f2de24b23 | | Git Branch: HEAD | | UTC Build Time: 2024-05-21 03:51:57 | | GoVersion: go1.21.10 | | Race Enabled: false | | Check Table Before Drop: false | | Store: tikv | +-----------------------------------------------------------+