Unexpected Database Directory Permissions

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> /

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s