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: "Adding Scripting Languages to PL/SQL Applications - Part 1"]

Looking for GRANT ALL on objects

This is the second part of the GRANT ALL on objects post that I made recently. This final look at this issue covers a simple SQL script that can be used to locate common objects where GRANT ALL has been used.

We found in the last post - Part 1 - that a grant of ALL on a table results in 12 individual grants in 19c. First let's find out how many grants are made on typical objects such as table, view, PL/SQL and a type:

SQL> connect orablog/orablog@//192.168.56.77:1521/orclpdb.localdomain
Connected.
SQL> create or replace type test_type as object (cc_col1 number);
2 /

Type created.

SQL> create table test_table (cc_col1 number);

Table created.

SQL> create or replace procedure test_pl as begin null; end;
2 /

Procedure created.

SQL> create view test_view as select * from test_table;

View created.

SQL>

Now lets grant all on each to a user and then get the counts of privileges:

SQL> grant all on test_type to emil;

Grant succeeded.

SQL> grant all on test_table to emil;

Grant succeeded.

SQL> grant all on test_pl to emil;

Grant succeeded.

SQL> grant all on test_view to emil;

Grant succeeded.

SQL>

Now we can check the number of grants on each:

SQL> connect sys/oracle1@//192.168.56.77:1521/orclpdb.localdomain as sysdba
Connected.
SQL>
SQL> col cnt for 9999
SQL> col table_name for a20
SQL> select count(*) cnt,table_name
2 from dba_tab_privs
3 where table_name in ('TEST_TABLE','TEST_VIEW','TEST_TYPE','TEST_PL')
4 group by table_name;

CNT TABLE_NAME
----- --------------------
12 TEST_TABLE
2 TEST_TYPE
2 TEST_PL
11 TEST_VIEW

SQL>

Now we can look for any object and grantee that has these grants - remember from the last post that a schema/user/DBA could grant all individual grants and there is no way we can tell the difference between that and GRANT ALL other than if there was an audit trail to capture the actions.

Now, can we find any objects where GRANT ALL has been done? We need to extend and include the GRANTEE and OWNER otherwise we could incorrectly count the grants across multiple users or objects. The example above was correct as I controlled the creation of the object and also the grants but it would not be correct for random objects

SQL> col grantee for a20
SQL> col owner for a20
SQL> select count(*) cnt,table_name,grantee, owner
2 from dba_tab_privs
3 where table_name in ('TEST_TABLE','TEST_VIEW','TEST_TYPE','TEST_PL')
4 group by table_name,grantee,owner;

CNT TABLE_NAME GRANTEE OWNER
----- -------------------- -------------------- --------------------
12 TEST_TABLE EMIL ORABLOG
2 TEST_TYPE EMIL ORABLOG
11 TEST_VIEW EMIL ORABLOG
2 TEST_PL EMIL ORABLOG

SQL>

Lets find objects:

SQL> @sc_grant_all.sql

OWNER TABLE_NAME TYPE GRANTEE
------------------------------ ------------------------------ -------------------- ------------------------------
MDSYS SDO_TFM_CHAIN TYPE PUBLIC
ORABLOG TEST_PL PROCEDURE EMIL
ORABLOG TEST_TYPE TYPE EMIL
MDSYS TFM_PLAN TYPE PUBLIC
SYS UTL_FILE PACKAGE ORDPLUGINS
SYS UTL_FILE PACKAGE ORDSYS
SYS UTL_FILE PACKAGE WMSYS
XDB XDB_PRIVILEGES TYPE PUBLIC
ORABLOG TEST_VIEW VIEW EMIL
ORABLOG TEST_TABLE TABLE EMIL

10 rows selected.

SQL>

The SQL used is this:

select owner,table_name,type,grantee from (
select count(*) cnt,table_name,grantee, owner, type
from dba_tab_privs
where type='TABLE'
group by table_name,grantee,owner, type
having(count(*)>=12)
union
select count(*) cnt,table_name,grantee, owner, type
from dba_tab_privs
where type='VIEW'
group by table_name,grantee,owner, type
having(count(*)>=11)
union
select count(*) cnt,table_name,grantee, owner, type
from dba_tab_privs
where type='TYPE'
group by table_name,grantee,owner, type
having(count(*)>=2)
union
select count(*) cnt,table_name,grantee, owner, type
from dba_tab_privs
where type in('PACKAGE','PROCEDURE','FUNCTION')
group by table_name,grantee,owner, type
having(count(*)>=2))
/

This is a first example for common objects. This needs to be expanded for all object types and also adjusted for different versions of Oracle where the number of grants can be different for each version but it is useful as a starting point to find where a DBA or developer has issues GRANT ALL... on some object.