mysqlmariadbmysqladminmariadb-10.5mysqladministrator

mariadb state wise where case clause inside view


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

Here is my sample data

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

   new_database
         table1
         table2
         .....
         .....
         tableN

Solution

  • 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