Tom,

Can directory permissions be granted in a role or must they be directly granted to a user/schema? I have a situation where I can't write to a file on a Unix file system. The customer I work for insists that most or all privileges be in a role and not directly granted to the user/schema because of security reasons. I'm not allowed CREATE ANY DIRECTORY privileges on my database so I can't experiment by granting the privs directly to the user/schema. Be advised that I sanitized this example so I haven't tested this exact code.

Here is my example:

In Oracle...

CREATE OR REPLACE DIRECTORY MY_DIR AS '/my_apps/my_files';

CREATE ROLE MY_ROLE NOT IDENTIFIED;
GRANT MY_ROLE TO MY_USER;
GRANT READ, WRITE ON DIRECTORY MY_DIR TO MY_ROLE;

GRANT EXECUTE ON SYS.UTL_FILE TO MY_USER;

CREATE OR REPLACE PROCEDURE MY_USER.TEST_WRITEFILE IS
out_File UTL_FILE.FILE_TYPE;
BEGIN
out_File := UTL_FILE.FOPEN ('MY_DIR', 'test.txt', 'W');
UTL_FILE.PUT_LINE (out_File, 'hello world');
UTL_FILE.FCLOSE (out_File);
END;


In Unix...

On our system, Oracle always writes files as owner "oracle" and group "dba". I have a service account called "my_account" and group "my_group". Directory "/my_apps/my_files" is owned by "my_account" and in group "my_group". The permissions on "/my_apps/my_files" are 770 or read/write/execute for both owner and group and no perms for others. I had the Unix admin add group "my_group" to the Oracle user "oracle".

/my_apps>groups oracle
dba my_group
/my_apps>ll
drwxrwx--- 2 my_account my_group 1024 Jun 9 10:04 my_files


The Error...

When I execute procedure MY_USER.TEST_WRITEFILE, I get this error:

declare
begin
my_user.test_writefile;
end;
Error at line 1
ORA-29289: directory access denied
ORA-06512: at "SYS.UTL_FILE", line 41
ORA-06512: at "SYS.UTL_FILE", line 478
ORA-06512: at "MY_USER.TEST_WRITEFILE", line 4
ORA-06512: at line 3

What am I doing wrong?

SQL> conn / as sysdba
Connected.

SQL> CREATE OR REPLACE DIRECTORY MY_DIR AS 'c:\temp';

Directory created.

SQL>
SQL> CREATE ROLE MY_ROLE;

Role created.

SQL> GRANT READ, WRITE ON DIRECTORY MY_DIR TO MY_ROLE;

Grant succeeded.

SQL>
SQL> GRANT connect, MY_ROLE TO MY_USER identified by my_user;

Grant succeeded.

SQL>
SQL> GRANT EXECUTE ON SYS.UTL_FILE TO MY_USER;

Grant succeeded.

SQL>
SQL> CREATE OR REPLACE PROCEDURE MY_USER.TEST_WRITEFILE IS
  2    out_File UTL_FILE.FILE_TYPE;
  3  BEGIN
  4    out_File := UTL_FILE.FOPEN ('MY_DIR', 'test.txt', 'W');
  5    UTL_FILE.PUT_LINE (out_File, 'hello world');
  6    UTL_FILE.FCLOSE (out_File);
  7  END;
  8  /

Procedure created.

SQL>
SQL>
SQL> conn my_user/my_user
Connected.

SQL> exec TEST_WRITEFILE
BEGIN TEST_WRITEFILE; END;

*
ERROR at line 1:
ORA-29289: directory access denied
ORA-06512: at "SYS.UTL_FILE", line 41
ORA-06512: at "SYS.UTL_FILE", line 478
ORA-06512: at "MY_USER.TEST_WRITEFILE", line 4
ORA-06512: at line 1

SQL> conn / as sysdba
Connected.

SQL> GRANT READ, WRITE ON DIRECTORY MY_DIR TO my_user;

Grant succeeded.

SQL> conn my_user/my_user
Connected.

SQL>
SQL> exec TEST_WRITEFILE

PL/SQL procedure successfully completed.

SQL>
SQL>