Question: How can I query Oracle to see
the privileges assigned to a directory?
Answer: Display privileges for directory
objects requires querying the dba_directories view
and the OS inode for the physical OS directory (e.g. ls
-alt".
The Oracle-side privileges for an OS
directory may not always correspond to the OS-level
directory. For example, a directory may have an Oracle
read-only privilege, but the OS permission do not show
read-only permissions (550).
You can see all the
privileges for all directories with the following script
select
*
from
all_tab_privs
where
table_name in
(select
directory_name
from
dba_directories);
This script
display directory privileges for a list of granted
privileges:
set lines 110
col privilege
format a12
col
grantee format a25
col owner format a25
select
p.grantee, p.privilege, p.owner, d.directory_name
from
dba_tab_privs p,
dba_directories d
where
p.table_name=d.directory_name
and
(grantee in ('xxx', 'yyy')
or
grantee
in (select granted_role from dba_role_privs
