Oracle | Directory Objects

Oracle Directory Objects Version 21c

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> [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;