- Does Oracle document the exact individual grants made to an object (i.e. a TABLE / PROCEDURE/... when GRANT ALL.... is executed
- 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;
- 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.
