Oracle 12c Directories

Oracle Directory Objects Version 12.1.0.1

General Information

Data Dictionary Objects

ALL_DIRECTORIES	DBA_DIRECTORIES	KU$_DIRECTORY_T
CDB_DIRECTORIES	DIR$	KU$_DIRECTORY_VIEW

System Privileges

CREATE ANY DIRECTORY

CREATE DIRECTORY

Create Directory

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 a10
col directory_name format a25
col directory_path format a82

SELECT *
FROM dba_directories;

CREATE OR REPLACE DIRECTORY ctemp AS 'c:\temp';

SELECT *
FROM dba_directories;

Manage Directory Privileges

Grant and Revoke 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 = 'CTEMP';

GRANT read ON DIRECTORY ctemp TO uwclass;

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

REVOKE read ON DIRECTORY ctemp FROM uwclass;

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

Grant and Revoke 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 = 'CTEMP';

GRANT write ON DIRECTORY ctemp TO uwclass;

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

REVOKE write ON DIRECTORY ctemp FROM uwclass;

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

Grant and Revoke Execute On A Directory

GRANT execute ON DIRECTORY <directory_name> TO <schema_name>;

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

GRANT execute ON DIRECTORY xmldir TO uwclass;

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

REVOKE execute ON DIRECTORY xmldir FROM uwclass;

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

Drop Directory

Drop A Directory

DROP DIRECTORY <directory_name>;

SELECT *
FROM dba_directories;

DROP DIRECTORY ctemp;

SELECT *
FROM dba_directories;