Call: +44 (0)1904 557620 Call
Blog

Pete Finnigan's Oracle Security Weblog

This is the weblog for Pete Finnigan. Pete works in the area of Oracle security and he specialises in auditing Oracle databases for security issues. This weblog is aimed squarely at those interested in the security of their Oracle databases.

[Previous entry: "Do You Worry Your Companies data is Being Stolen?"]

Granting ALL on Database Objects

I was asked by a friend a few days ago a few questions related to the granting of ALL on a database object such as a table or a PL/SQL package. For example - GRANT ALL ON OWNER.TABLE TO DAVE. Let me list the questions and then we will explore the answers:

  1. Does Oracle document the exact individual grants made to an object (i.e. a TABLE / PROCEDURE/... when GRANT ALL.... is executed

  2. Can we tell if a grant has been made via a GRANT ALL.... or an individual grant such as GRANT SELECT ON OWNER.TABLE TO DAVE;

  3. How can we find all objects in the database that have been granted ALL


As far as I can tell to answer question (1) - please correct me if I am wrong - Oracle does not document the exact privileges granted when you GRANT ALL on an object such as a table. We can resolve this for all object types by creating one of each type and issuing a GRANT ALL... and then using this information to test each object to see what was granted and whether we can tell if GRANT ALL... was granted.

For example lets connect to my 19c database and create a table and then issue GRANT ALL on it:

SQL> connect orablog/orablog@//192.168.56.77:1521/orclpdb.localdomain
Connected.
SQL> create table test (test01 number);

Table created.

SQL> grant all on test to emil;

Grant succeeded.

SQL>

Now we can check the actual grants that have been made:

SQL> connect sys/oracle1@//192.168.56.77:1521/orclpdb.localdomain as sysdba
Connected.
SQL> sho user
USER is "SYS"
SQL>
SQL> set lines 220
SQL> col grantee for a30
SQL> col grantor for a30
SQL> col privilege for a40
SQL> l
1* select grantee, grantor, privilege from dba_tab_privs where owner='ORABLOG' and table_name='TEST'
SQL> /

GRANTEE GRANTOR PRIVILEGE
------------------------------ ------------------------------ ----------------------------------------
EMIL ORABLOG ALTER
EMIL ORABLOG DELETE
EMIL ORABLOG INDEX
EMIL ORABLOG INSERT
EMIL ORABLOG SELECT
EMIL ORABLOG UPDATE
EMIL ORABLOG REFERENCES
EMIL ORABLOG READ
EMIL ORABLOG ON COMMIT REFRESH
EMIL ORABLOG QUERY REWRITE
EMIL ORABLOG DEBUG

GRANTEE GRANTOR PRIVILEGE
------------------------------ ------------------------------ ----------------------------------------
EMIL ORABLOG FLASHBACK

12 rows selected.

SQL>

So, in 19c 12 privileges are granted to a user when GRANT ALL is issued. This will vary per object and also version of Oracle. What if we revoke all and then check whats granted:

SQL> connect orablog/orablog@//192.168.56.77:1521/orclpdb.localdomain
Connected.
SQL> revoke all on test from emil;

Revoke succeeded.

SQL>

Lets check the grants:

SQL> connect sys/oracle1@//192.168.56.77:1521/orclpdb.localdomain as sysdba
Connected.
SQL> sho user
USER is "SYS"
SQL> select grantee, grantor, privilege from dba_tab_privs where owner='ORABLOG' and table_name='TEST';

no rows selected

SQL>

This is simple for one object type (table) and for grant and revoke. What if we make it more complex. Lets first grant ALTER on the table to EMIL and check permissions and then GRANT ALL and then REVOKE ALL. What happens:

Make the the single grant

SQL> connect orablog/orablog@//192.168.56.77:1521/orclpdb.localdomain
Connected.
SQL> sho user
USER is "ORABLOG"
SQL> grant alter on test to emil;

Grant succeeded.

SQL> connect sys/oracle1@//192.168.56.77:1521/orclpdb.localdomain as sysdba
Connected.
SQL> sho user
USER is "SYS"
SQL> select grantee, grantor, privilege from dba_tab_privs where owner='ORABLOG' and table_name='TEST';

GRANTEE GRANTOR PRIVILEGE
------------------------------ ------------------------------ ----------------------------------------
EMIL ORABLOG ALTER

SQL>

Now GRANT ALL on the same table and check the permissions:

SQL> connect orablog/orablog@//192.168.56.77:1521/orclpdb.localdomain
Connected.
SQL> sho user
USER is "ORABLOG"
SQL> grant all on test to emil;

Grant succeeded.

SQL> connect sys/oracle1@//192.168.56.77:1521/orclpdb.localdomain as sysdba
Connected.
SQL> sho user
USER is "SYS"
SQL> select grantee, grantor, privilege from dba_tab_privs where owner='ORABLOG' and table_name='TEST';

GRANTEE GRANTOR PRIVILEGE
------------------------------ ------------------------------ ----------------------------------------
EMIL ORABLOG ALTER
EMIL ORABLOG DELETE
EMIL ORABLOG INDEX
EMIL ORABLOG INSERT
EMIL ORABLOG SELECT
EMIL ORABLOG UPDATE
EMIL ORABLOG REFERENCES
EMIL ORABLOG READ
EMIL ORABLOG ON COMMIT REFRESH
EMIL ORABLOG QUERY REWRITE
EMIL ORABLOG DEBUG

GRANTEE GRANTOR PRIVILEGE
------------------------------ ------------------------------ ----------------------------------------
EMIL ORABLOG FLASHBACK

12 rows selected.

SQL>

The same 12 grants now exist to EMIL on the table ORABLOG.TEST. This GRANT ALL overwrites the single grant of ALTER on the same table - It is lost and this is a problem for your management of grants on objects and indeed the same applies for system grants.

This is a classic problem I have seen many times that occurs in a number of places in the Oracle database. A slightly different example on the same area would be that an application or DBA grants one right on an object and then another application or DBA grants two more rights where one of the two new grants is the same as the first. At this point the permissions work BUT if the second DBA then revokes the two grants he/her made then the first grant is also removed and again the security model is broken.

Another example where the same issue occurs is audit trail. If one application or DBA turns on two audit settings and then another application or DBA turns on two more and one is the same as the first. If one of the applications is removed or audit disabled the first is affected.

How can we fix this grants problem?

One idea; obviously is to add the grants for DBA 1 to a role - say DBA_ROLE_1 and add the grants for DBA2 to another role, say DBA_ROLE_2. In this way we revoke the role we need to revoke and remove the grants BUT we also need to then grant back the other role where there is a privilege cross over to put the security model back correctly

Each object type has a different grants possible so there is no consistent way to test all objects on the database to see if ALL has been granted other than creating one object and then listing the grants and then checking all objects of the same type to see if they match.

If we want to try and find all objects where GRANT ALL has been made then we also have the issue that we can GRANT ALL ON ... TO ... but we can also make 12 individual grants on a table individually and we would not be able to tell the difference between these individual grants and the GRANT ALL.

I have written a script to look for potential GRANT ALL (bear in mind someone can grant each right individually BUT the result is the same as GRANT ALL) and to keep this post shorter I will present that script in part 2 of this blog post and demo looking for users / schemas that have been granted ALL.