Pete Finnigan's Oracle Security Weblog

During testing of the DDL capture trigger to see if we can react to disable or enable of a trigger I created a USER and there is a silent GRANT done
whilst creating the user.

I have come across something similar to this many many years ago as when the RESOURCE role was granted to a user they silently were granted the system privilege UNLIMITED TABLESPACE. This is fixed now.

I am not sure what I think about silent GRANTS. I am not sure if this is a bug or feature. If I issue a single DDL command I think the database should not issue others at the same time.

My method of testing this is to create a DDL trigger that outputs all DDL issued as print statements so we can see the command we issued and the DDL actually issued. I know if we run a trace we see a lot more details such as the recursive SQL that has been run and sometimes that is useful to understand exactly what happens when a SQL command is issued. First lets create the simple toolkit:

SQL> get ddl
  1  create or replace trigger atk_ddl
  2  after ddl on database
  3  declare
  4     lv_sql ora_name_list_t;
  5     lv_stmt varchar2(32767);
  6     lv_n pls_integer:=0;
  7  begin
  8     --
  9     lv_n:=ora_sql_txt(lv_sql);
 10     for i in 1 .. lv_n loop
 11             lv_stmt:=lv_stmt||lv_sql(i);
 12     end loop;
 13     --
 14     dbms_output.put_line('DDL>'||lv_stmt);
 15* end;
SQL> @ddl

Trigger created.

SQL>

Now let us create a simple user:

SQL> set serveroutput on
SQL> create user tst1 identified by tst1;
DDL>GRANT INHERIT PRIVILEGES ON USER "TST1" TO PUBLIC
DDL>create user tst1 identified by *

User created.

SQL>

Wow, first attempt and we get lucky and not only does the simple tool catch "create user tst1 identified by*" but we also capture the "GRANT INHERIT PRIVILEGES ON USER "TST1" TO PUBLIC"

I am also not sure why each DDL is not in the right order; clearly we cannot grant inherited until the user actually exists. Must be a trigger or DBMS_OUTPUT issue. A trace would resolve this, maybe.

Now lets drop the user:

SQL> set serveroutput on
SQL> drop user tst1;
DDL>drop user tst1

User dropped.

SQL>

No additional DDL. never mind, lets create a role:

SQL> create role atk_ddl;
DDL>create role atk_ddl

Role created.

SQL>

This is interesting as there is just one DDL command shown BUT we know that the creator of a role is granted the role. We can see this here:

SQL> select grantee from dba_role_privs where granted_role='ATK_DDL';

GRANTEE
--------------------------------------------------------------------------------
SYS

SQL>

So, there is also a silent grant to SYS as well as the role create. This is not shown in the DDL grabber. If we do an alter system we can see if that is captured:

SQL> alter system set "_system_trig_enabled"=true;

System altered.

SQL>

This as expected is not captured. What if we create a procedure:

SQL> create or replace procedure atk_proc
  2  is
  3  begin
  4  null;
  5  end;
  6  /
DDL>create or replace procedure atk_proc
is
begin
null;
end;

Procedure created.

SQL>

No hidden DDL. What about granting our role:

SQL> grant atk_ddl to pete;
DDL>grant atk_ddl to pete

Grant succeeded.

SQL>

No hidden DDL, what about a table:

SQL> create table atk_test2 (col1 number);
DDL>create table atk_test2 (col1 number)

Table created.

SQL>

What if we grant resource?

SQL> grant resource to pete;
DDL>grant resource to pete

Grant succeeded.

SQL>

Nothing, seems like we know of two so far, CREATE USER and CREATE ROLE where there is silent DDL; one that was shown and one that was not. Also we proved that ALTER SYSTEM whilst being DDL is not DDL for the DDL trigger.

If I find more in the future I will let you know here

#oracleace #sym_42 #oracle #database #security #ddl #grants #privileges #hacking