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);
Is there any way to detect current state inside view like select, insert, update, delete
within where clause ?
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 |
+----+-------------------+-------+
User helen and lina has SELECT, INSERT, UPDATE, DELETE
grants on contents view
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 check constrants cannot use user
. you can use TRIGGERS to enforce constraints like fiddle:
insert:
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
update:
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
delete:
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.
ref: trigger manual