Database Reference
In-Depth Information
Finally, log out and then back in to get the DBA role. You can also execute the
set role
command, but in the
following example I've gone with the log out and back in approach:
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
C:\Windows\System32>sqlplus apptest/lowsec12@192.168.1.3:1521/pdborcl
SQL*Plus: Release 12.1.0.1.0 Production on Mon Aug 26 19:11:29 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Last Successful login time: Mon Aug 26 2013 19:02:47 +01:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select grantee from dba_role_privs where granted_role='DBA';
GRANTEE
--------------------------------------------------------------------------------
DEFROLETEST
SYS
PUBLIC
SYSTEM
It should be remembered that in this example the schema-owning account (AKA definer) and the program unit
both need to be granted the DBA role. In other words, if the DBA role was revoked from the schema owner, then the
program unit's ability to use the DBA role would be lost. However, the authid setting of the program unit does not have
any effect on the passing of the definer's role to its program unit. This means that roles can also be granted to invoker's
rights packages, and they will work if the definer (schema owner) has that role.
In my tests this feature works reliably, so great work, Oracle! I will be using this in future. The million dollar
question is:
Has definer's roles solved the problem with public privileges and privilege escalation?
The answer is:
Not
quite
. The current dependency structure within Oracle DB has not been re-engineered to use definer's rights roles
for the built-in packages already present in the DB. Packages that depend on each other within the database still
use the
PUBLIC
role as their privilege mechanism for that dependency. It should be possible to create a new role, say
DEFINER_PUBLIC
, which is then granted all the privileges of
PUBLIC,
and then all references to
PUBLIC
can be replaced
by
DEFINER_PUBLIC
. Such a role would still inherit in the same way as
PUBLIC
but would only be granted to program
units as appropriate, rather than to any DB user via
PUBLIC
. It would be interesting to attempt this as a test, but we can
guarantee that the practice would not be so simple.
The fact is that the number of
PUBLIC
privileges has increased largely due to the new
INHERIT
privilege, which is
designed to reduce the chance of
SYSDBA
Phishing, which we will discuss next.
SYSDBA Phishing
A SYSDBA's privileges will pass through the package they are invoking into a definer's rights procedure so that a
schema owner can gain the SYSDBAs privilege even though the package is definer. This may be non-intuitive, but it
is a well-known foible. What has not been so widely published are methods for a small schema owner to collect the
