Oracle Directory Objects
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
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>
[SHARING=<METADATA | NONE>] AS
'<operating_system_path>';
conn sys@pdbdev 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;
Create A Directory with Metadata Sharing CREATE OR REPLACE DIRECTORY <directory_name>
[SHARING=<METADATA | NONE>] AS
'<operating_system_path>';
conn sys@pdbdev 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_shared
SHARING=METADATA 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;

Related Topics
ADDM
DataPump
DBMS_DATA_PUMP
DBMS_FILE_TRANSFER
DBMS_HPROF
DBMS_LOB
DICOM
External Tables
Multimedia Audio
Multimedia Image
Multimedia Video
Transportable Tablespaces
UTL_FILE
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx