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

  • 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

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x