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.
