I am creating a fresh schema for a new project, My basic requirements are 2 users with different privileges. One is a regular user and have minimum privileges and other is the administrator who have some elevated privileges than the regular user.
I am taking this scenario for the instance:
basicUser have INSERT Privilege over Table1 butNOPrivilege over Table2 and Table3
adminUser have INSERT Privilege over All the tables
Now, Table1 have a Trigger defined for INSERT option which INSERT the records into Table2 and Table3 taking the values from Table1 as soon as the record is inserted into table1.
I am wondering whether the trigger will work when basicUser insert the data into Table1 ?
Remember He can insert the data in Table1 but He have no privilege over other tables being populated by the trigger, and this trigger is raised by basicUser's action.
My first guess is Yes! This should work. but I am not sure. Please correct me if I am wrong.
If yes, then is there any way I can prevent the trigger's action if the record is inserted by basicUser ?
I'll need a fail-safe workaround because I still need Table1 to hold the record inserted by basicUser but I dont want it to insert the data into other tables.
and if No, Then is there any way I can log some of basicUser's action, bearing in mind that He have No privilege over the table which is being used for storing logs ?