Oracle Database Directories

System Privileges Needed

GRANT create any directory TO <user_name>;
GRANT drop any directory TO <user_name>;

Create A Directory

CREATE OR REPLACE DIRECTORY <directory_name> AS '<operating_system_path>';

conn / as sysdba

desc dba_directories

set linesize 121
col owner format a15
col directory_name format a20
col directory_path format a70

SELECT * FROM dba_directories;

CREATE OR REPLACE DIRECTORY temp AS '/opt/app/user/';

SELECT * FROM dba_directories;

Grant Read On A Directory

GRANT READ ON DIRECTORY <directory_name> TO <schema_name>

col grantor format a20
col grantee format a20
col table_schema format a20
col table_name format a20
col privilege format a10

SELECT grantor, grantee, table_schema, table_name, privilege FROM all_tab_privs WHERE table_name = 'EMP';

GRANT READ ON DIRECTORY temp TO testuser;

SELECT grantor, grantee, table_schema, table_name, privilege
FROM all_tab_privs
WHERE table_name = 'EMP';

Grant Write On A Directory

GRANT WRITE ON DIRECTORY <directory_name> TO <schema_name>

SELECT grantor, grantee, table_schema, table_name, privilege
FROM all_tab_privs
WHERE table_name = 'EMP';

Revoke Read On A Directory

REVOKE READ ON DIRECTORY <directory_name> FROM <schema_name>

Revoke Write On A Directory REVOKE WRITE ON DIRECTORY <directory_name> FROM <schema_name>

Drop A Directory

DROP DIRECTORY <directory_name>;

SELECT * FROM dba_directories;

DROP DIRECTORY temp;

SELECT * FROM dba_directories;