Thread: row-level security (Dynamically rewrite queries)
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
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
Jonatan Evald Buus wrote: > 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. I've never used veil myself, but I can't believe it's less effort to re-invent the wheel on this. The query-rewrite is what the views are doing. > 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? Yes, of course. This also seems to be what veil does for you. Beware - views are basically macros that rewrite your query and you can get unexpected results when e.g. inserting multiple rows at once. See mailing-list archives for examples. > 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? There's some overhead associated with rewriting the query, but the planner should cope just fine then. Which isn't to say there aren't cases where you couldn't write a better query from scratch, but the planner optimises rewritten queries as well as any other. > Are there any other alternatives to implementing row-level security that can > be enforced at the database level? Use veil. Someone else has gone to the trouble to think this through. If you write your own solution you'll only have 1 user (you). Even if veil is only used by its author there will at least be 2 of you. I frequently use a "my" schema with views that map to the relevant base tables ("my.clients" etc). However, that's not for applications that require some guarantee of security database-wide, it's as much about simplifying my queries. -- Richard Huxton Archonet Ltd