Pete Finnigan's Oracle Security Weblog

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.