mariadb statewise where case 子句在视图中

2022-01-15 00:00:00 mariadb mysql mysqladmin mariadb-10.5 mysqladministrator

我在协作表中创建了下面的视图

I created below view in collaboration table

CREATE VIEW contents(
  id,
  title
)
AS
select 
   mytable.id as id,
   mytable.title as title
from mytable 
where mytable.owner = substring_index(user(), '@', 1);

有什么方法可以检测视图内部的当前状态，例如 where 子句中的 select、insert、update、delete?

Is there any way to detect current state inside view like select, insert, update, delete within where clause ?

我希望有下面这样，不知道如何在 mysql/mariadb 中生成等价物

I wish to have like below, don't know how to produce equivalent in mysql/mariadb

/* during select statement user can see all available data*/
if state == 'select' then
    where 1 = 1 /* can see all data */
else
/* if state is update or delete user is allowed to modify or delete data which for which he/she is owner*/
    where mytable.owner = substring_index(user(), '@', 1);
endif

这是我的示例数据

MariaDB [test]> select * from mytable;
+----+-------------------+-------+
| id | title             | owner |
+----+-------------------+-------+
|  1 | created by root   | root  |
|  4 | created by helen  | helen |
|  6 | created by helen1 | helen |
|  7 | 123               | lina |
+----+-------------------+-------+

用户 helen 和 lina 对内容视图具有 SELECT、INSERT、UPDATE、DELETE 授权

User helen and lina has SELECT, INSERT, UPDATE, DELETE grants on contents view

  • 另外用户 helen 是普通用户如何授予创建 new_database 的权限，并继承 helen 在 new_database 中创建的任何新表的权限?我不想创建 helen 作为管理员.用户 helen 应该能够在她创建的数据库中创建任意数量的数据库和表.这是否可能?
  • Also user helen is normal user how to grant permission to create new_database, and inherit permissions for any new tables created by helen inside new_database ? I don't want to create helen as administrator. User helen should be able to create any number of databases and tables inside database she created. whether this is possible ?
   new_database
         table1
         table2
         .....
         .....
         tableN

推荐答案

As checkconstrants 不能使用 user.您可以使用 TRIGGERS 来强制执行约束，例如 fiddle:

As check constrants cannot use user. you can use TRIGGERS to enforce constraints like fiddle:

插入:

CREATE TRIGGER enforce_insert
BEFORE
INSERT ON mytable
FOR EACH ROW
  IF NEW.owner != substring_index(user(), '@', 1) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'conflict of owner on insert';
  END IF

更新:

CREATE TRIGGER enforce_update
BEFORE
UPDATE ON mytable
FOR EACH ROW
  IF OLD.owner != substring_index(user(), '@', 1)
     OR NEW.owner != substring_index(user(), '@', 1) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'conflict of owner on update';
  END IF

删除:

CREATE TRIGGER enforce_delete
BEFORE
DELETE ON mytable
FOR EACH ROW
  IF OLD.owner != substring_index(user(), '@', 1) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'conflict of owner on delete';
  END IF

但通常它建议应用程序强制执行数据结构的示意图.

Generally however its recommended that the application enforce the schematics of the data structure.

参考:触发手册

相关文章