Security in Stored Procedures
cjohn001 Mar 10, 2019 5:44 PMHello together,
I have a question regarding security in stored procedures. I have written a stored procedure which should update some configuration values of a user. Let"s assume the following example. I have a table with configuration records. Each user has 7 records for each day of the week in this table which has a column param that can take the value A or B. Hence the table looks something like
id, userid, dayOfWeek, param
0 1 0 A
1 1 1 B
2 1 2 B
...
7 2 0 B
...
now the stored procedure should take the param value of a given day from the user and change the values of param for the user days to the value of the selected day. I want ensure that a user now can only change his records and not call the procedure to change records of other users.
I am using Teiid together with keycloak, which allows me for tables to use row based security constraints. For table access something like the following results in a where clause that constrains access to the logged in user()
ConfigOptions.fkProfile in (SELECT Account.idProfile FROM Account WHERE Account.uuidUser = LEFT(user(), 36))
No my first question is
1.How can I reach the same for a stored procedure?
2. Morover, I am wondering what I can configure regarding security in the model view of teiid designer. For my stored procedure called "ApplyMealConfigToEntireWeek" I can set constraints for CREATE, READ, WRITE, DELETE (see attached screenshot). I am wondering what this denotes in presence of a stored procedure. Would a disallowed WRITE for example denote that the stored procedure should not be allowed to do any changes in the database? It would be great if you could explain me how security is meant to be configured for stored procedures. Thanks for your help.
Best regards,
Christoph