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