Hi there,
Is it possible to dynamically rewrite an SQL query for all operations (SELECT, INSERT, UPATE, DELETE etc.)?
It seems that using RULES it's possible to replace one query with another, but can the actual query that was executed by retrieved so it can be dynamically modified?
I.e. I'd like to create a rule along the lines of:
CREATE RULE txn_vpd ON
SELECT TO Transaction_Tbl
DO INSTEAD replace('WHERE', 'INNER JOIN User_Tbl ON Transaction_Tbl.userid = User_Tbl.id WHERE User_Tbl.name = \'CURRENT_USER\'', $SQL)
Where $SQL represents the actual SQL query that was executed on the table and the table has a column: userid which indicates the owner of the row.
Essentially what I'd like to do is implement row-level security (what Oracle calls "Virtual Private Database") but as far as I can find the last time this was discussed is several years ago and the general consensus ended up being "use veil".
Veil seems overly like an complicated approach for something that (in theroy) should be possible with a dynamic query rewrite using search and replace prior to execution.
Oracle's implementation seems quiete elegant for this, please see
http://www.oracle.com/technology/pub/articles/10gdba/week14_10gdba.html or
http://www.devshed.com/c/a/Oracle/RowLevel-Security-with-Virtual-Private-Database/ for examples.
One other approach that I could think of, would be to create a view for every table and use the view for accessing the data:
CREATE VIEW Transaction_Vw AS
SELECT * FROM Transaction_Tbl Txn
INNER JOIN User_Tbl U ON Txn.userid = U.id
WHERE U.name = 'CURRENT_USER'
However, can usage this view be enforced by the database by removing SELECT priviliges from Transaction_Tbl?
Also, I suspect that performance would go down the drain if complex joins are used? There could potentially be a lot of unnecessary joins to User_Tbl if multiple "secure view" where joined together.
Is the planner capable of taking this into account and auto-magically optimize the query?
Are there any other alternatives to implementing row-level security that can be enforced at the database level?
Appreciate the input
Cheers
Jonatan