我在协作表中创建了以下视图
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
?
我希望有像下面这样的,不知道如何在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
授权
- 用户
helen
也是普通用户,如何授予创建new_database的权限,并继承helen在new_database内创建的任何新表的权限?我不想创建海伦作为管理员。用户helen应该能够在她创建的数据库中创建任意数量的数据库和表。这是否可能
new_database
table1
table2
.....
.....
tableN
As check constants不能使用user
。您可以使用TRIGGERS来强制执行像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
然而,通常建议应用程序强制执行数据结构的示意图。
参考:触发手动