Pete Finnigan's Oracle Security Weblog

One of the interesting discussions with the delegates that took place in my Oracle security class in Prague last week was a discussion around the difference between privileges granted via roles and those granted directly and the effects of privileges in PL/SQL code that is written as definer rights or written as invoker rights.

In the class I made an example where I was specifically comparing the difference between the statement GRANT ALL PRIVILEGES and also GRANT DBA for a specific user; i.e. whats the difference (only in terms of system privileges) if a single user is granted the DBA role and then if he instead had ALL PRIVILEGES granted. The DBA role amongst other things has almost all of the system privileges granted to it so a good comparision can be made between GRANT ALL PRIVILEGES to a single user and GRANT DBA to another. In essense they are similar in terms of power BUT not exactly the same. I wrote an article about the differences in the privileges for the new Oracle scene magazine for the UKOUG which has just been published as the first in my new regular Oracle security column for the http://www.ukoug.org/publications/index.jsp?parent=0&id=1 - (broken link) Oracle Scene magazine - you need to be a membeer to read it or attend a conference to receive a paper printed version.

The issue of power is not simple. The main difference is what happens when the user runs definer rights code or invoker rights code. In definer rights PL/SQL code roles; and therefore the privileges assigned to them are not recognised - of course the code is also run as the owner not the invoker. In invoker rights code the owner of the codes privileges are ignored completely and the code runs with the rights of the invoker and roles are not ignored. Rather than set up some examples of this I am going to defer to a nice article written recently by Martin Widlake on his blog titled "Accessing Roles in stored PL/SQL".

So my comment was that the grant of direct privileges (via GRANT ALL PRIVILEGES - in this example) is more dangerous than the GRANT DBA - again in this example but this needed to be qualified at the time. A direct privilege; in this case all of them; well almost all of them; see the Oracle scene article for why is always "ON" whilst system privileges granted via the role can sometimes be off. Well the qualification is that this of course applies if the user is primarily accessing functionallity via PL/SQL and this depends on whether its definer rights or invoker rights - if a user is designed as an admin type user and is not primarily accessing code then its a different issue. Thats one issue, the second is that if the code is definer rights then its safer if the owner has been granted a role because its "OFF" during the definer rights code and definer rights code has the problem that if its exploited then the exploiter gets access to the owners privileges if the exploit allows this but not the owners privileges via his roles.

So there are lots of factors involved. The discussion was comparing ALL PRIVILEGES with DBA and importantly for a schema account - now the downside is that simply saying use a ROLE because its turned OFF during definer rights code is irrelevant if the code was invoker rights (in a good way as the privileges of the invoker count not the owner) - in other words the type of code matters. roles for schema owners are going to be safer BUT the issue is really about least privileges. Whether its definer rights code or invoker rights code, reduce the privileges of the owner (definer) or user (invoker) to the bare minimum necessary to do the business function exposed by the code.

Good privilege design and code design are very closely linked - unfortunately in real life we don't see too many good designs! but beware of how privileges are exposed.