Pete Finnigan's Oracle Security Weblog

I saw an interesting thread on the Oracle-l mailing list this evening that I thought I would point at here. The thread is titled "view privilege" and the original poster asks why when an "owner" creates a view he must have explicit privileges granted on all the objects referenced in the view and not have had then granted via a role. He asks what the logic is in this design implementation. Paul Drake comes back with some great insights (as usual), such as

Roles, if granted, may or may not be enabled in a user session at runtime.
Roles may have had their sys_privs changed between compile time and runtime

and he follows with a comment

Sounds to me like roles leave holes (for privilege escalation).

Tom then follows with some comments about Oracle being a bit lazy in relation to not checking privileges enabled at run time and preferring to do it at compile time. He also points out that Oracle are consistent.

This is an interesting if quite common subject and question that is posted time and again on mailing lists and newsgroups either related to views or to other PL/SQL code. There is often confusion with this in views and people often write views and wonder why there is a problem when they have been granted access already via a role. I was particularly interested in the thread mainly for Paul's comments though, I thought they summed up the issues very well.