Database Reference
In-Depth Information
Public Privileges
The main cause of privilege escalation in Oracle databases is the fact that a low-privileged user automatically gains
the PUBLIC role. That role includes 36,872 privileges in 12c, and that number has been rising in each new release.
Following is a query you can execute to see the number of privileges assigned to the role in your own database:
select count(*) from dba_tab_privs where grantee='PUBLIC';
36872
You can see the 36,872 privileges assigned to PUBLIC in 12c. Why is this so? To find the answer, we need to isolate
the cause. Are there other privileges that get assigned to public, or is it just object privileges?
select count(*) from dba_role_privs where grantee='PUBLIC';
0
select count(*) from dba_sys_privs where grantee='PUBLIC';
0
Many object privileges are granted to PUBLIC, but no SYSTEM or ROLE privileges are granted to PUBLIC.
Why do object privileges in particular need to be granted to PUBLIC? What is special about these object privileges?
I wonder if they are needed by dependencies?
select count(distinct referenced_name) from dba_dependencies;
43654
select table_name from dba_tab_privs where grantee='PUBLIC' intersect select referenced_name from
dba_dependencies;
35003
What the above shows is that the vast majority of the public privileges are on objects that are dependencies, for
example when another package needs access to that package. Code is calling on other code using the public role to
hold the object privilege. This is statistical evidence of what most Oracle developers will already know from practice:
the only way of granting access to a large group of object privileges so that another schema's package can access them
is by granting those object privileges to the PUBLIC role. This is because definer's rights have never supported roles
(other than PUBLIC), so only direct privileges could be inherited by a schema's package from the schema owner.
Following is a link to an article I wrote on this topic in 2009:
http://www.oracleforensics.com/wordpress/index.php/2009/11/22/public-role-and-definer-rights/
So folks that blame DBAs, developers, and users for too many public privileges have been a little unfair. It is
actually the design of the Oracle database that has led its technologists to depend on PUBLIC.
Thankfully, the design fault is fixed through a new feature known as “definer's roles.” It should be said that this is
potentially the best new feature in Oracle for the 12c release.
Definer's Roles
Let's test the new definer's roles feature and see how it works. In this section's example, we will inherit the DBA role
through defroletest 's procedure when that procedure is invoked by the low-privileged apptest account in order
to grant DBA to public. This is not normal usage, but will demonstrate the functionality of an invoker using the
role granted to a procedure.
 
Search WWH ::




Custom Search