1

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

  • 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
Helen
  • 45
  • 5

1 Answers1

2

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

danblack
  • 12,130
  • 2
  • 22
  • 41
  • Hi your solution works but problem is even root user unable to update or delete other user data, example as a root user `update mytable set title='modify helen data' where id = 4` – Helen Jul 05 '21 at 02:15
  • however with little modification in if clause now root allowed to modify other users data. could you please suggest how to add automatic timestamp for both insert and update, then if modified modified by someuser. like `created_at, modified_at, modified_by` fields – Helen Jul 05 '21 at 02:33
  • As done, `root` and other admin users would need to be logic of each trigger. `DEFAULT` value and `ON UPDATE` can fix the the timestamps (ref [manual](https://mariadb.com/kb/en/create-table/#column-definitions). `SET NEW.modified_by = ...` I think can be done in a trigger. Rules preventing the tampering of created/modified could need to be in the triggers too. `IF NEW.modified_at != NOW()` is actually correct. Also see [system versioned tables](https://mariadb.com/kb/en/system-versioned-tables/). – danblack Jul 05 '21 at 06:23