How to query the permissions on an Oracle directory?

I have a directory in all_开发者_C百科directories, but I need to find out what permissions are associated with it, i.e. what has been granted on it?

This should give you the roles, users and permissions granted on a directory:

SELECT * 
  FROM all_tab_privs 
 WHERE table_name = 'your_directory';  --> needs to be upper case

And yes, it IS in the all_TAB_privs view ;-) A better name for that view would be something like "ALL_OBJECT_PRIVS", since it also includes PL/SQL objects and their execute permissions as well.

You can see all the privileges for all directories wit the following

SELECT *
from all_tab_privs
where table_name in
  (select directory_name 
   from dba_directories);

The following gives you the sql statements to grant the privileges should you need to backup what you've done or something

select 'Grant '||privilege||' on directory '||table_schema||'.'||table_name||' to '||grantee 
from all_tab_privs 
where table_name in (select directory_name from dba_directories);

Wasn't sure if you meant which Oracle users can read\write with the directory or the correlation of the permissions between Oracle Directory Object and the underlying Operating System Directory.

As DCookie has covered the Oracle side of the fence, the following is taken from the Oracle documentation found here.

Privileges granted for the directory are created independently of the permissions defined for the operating system directory, and the two may or may not correspond exactly. For example, an error occurs if sample user hr is granted READ privilege on the directory object but the corresponding operating system directory does not have READ permission defined for Oracle Database processes.

With Oracle 11g R2 (at least with 11.2.02) there is a view named datapump_dir_objs.

SELECT * FROM datapump_dir_objs;

The view shows the NAME of the directory object, the PATH as well as READ and WRITE permissions for the currently connected user. It does not show any directory objects which the current user has no permission to read from or write to, though.

Expanding on this a bit, this script will allow you to see the privileges for any object type and name that appears in all_tab_privs.

/*

usage: @obj-privs <object-type> <object-name>

object-type can be any type of object; table, directory, index, etc.
case does not matter

object-name can be any legal name - case matters

Wild cards work for both object-type and object-name


@obj-privs dir% MYDIR
@obj-privs table INV%
@obj-privs synonym %


*/


set pagesize 100
set linesize 200 trimspool off

col grantor format a15
col grantee format a30
col table_schema format a30 head 'OWNER'
col table_name format a30 head 'OBJECT_NAME'
col privilege format a15

col  v_object_name new_value v_object_name noprint
col  v_object_type new_value v_object_type noprint

set feed off term off echo off pause off verify off
select upper('&1') v_object_type from dual;
select '&2' v_object_name from dual;

set feed on term on feed on


select
   p.table_name
   , p.table_schema
   , p.privilege
   , p.grantee
   , p.grantor
   , o.object_type
from all_tab_privs p
join all_objects o on o.owner = p.table_schema
   and o.object_name = p.table_name
   and p.table_name like '&v_object_name'
   and o.object_type like '&v_object_type'
order by p.table_name, p.table_schema, p.grantee
/

Here is an example for directories:

SQL# @obj-privs dir% %

OBJECT_NAME                    OWNER                          PRIVILEGE       GRANTEE                        GRANTOR         OBJECT_TYPE
------------------------------ ------------------------------ --------------- ------------------------------ --------------- ---------------------------------------------------------------------
DATA_PUMP_DIR                  SYS                            WRITE           EXP_FULL_DATABASE              SYS             DIRECTORY
DATA_PUMP_DIR                  SYS                            READ            EXP_FULL_DATABASE              SYS             DIRECTORY
DATA_PUMP_DIR                  SYS                            WRITE           IMP_FULL_DATABASE              SYS             DIRECTORY
DATA_PUMP_DIR                  SYS                            READ            IMP_FULL_DATABASE              SYS             DIRECTORY
ORACLE_OCM_CONFIG_DIR          SYS                            READ            ORACLE_OCM                     SYS             DIRECTORY
ORACLE_OCM_CONFIG_DIR          SYS                            WRITE           ORACLE_OCM                     SYS             DIRECTORY
ORACLE_OCM_CONFIG_DIR2         SYS                            WRITE           ORACLE_OCM                     SYS             DIRECTORY
ORACLE_OCM_CONFIG_DIR2         SYS                            READ            ORACLE_OCM                     SYS             DIRECTORY

8 rows selected.

Here is another for tables with USER in the name:

SQL# @obj-privs tab% %USER%

OBJECT_NAME                    OWNER                          PRIVILEGE       GRANTEE                        GRANTOR         OBJECT_TYPE
------------------------------ ------------------------------ --------------- ------------------------------ --------------- ---------------------------------------------------------------------
BDSQL_USER_MAP                 SYS                            INSERT          BDSQL_ADMIN                    SYS             TABLE
BDSQL_USER_MAP                 SYS                            DELETE          BDSQL_ADMIN                    SYS             TABLE
BDSQL_USER_MAP                 SYS                            SELECT          BDSQL_ADMIN                    SYS             TABLE
BDSQL_USER_MAP                 SYS                            READ            BDSQL_USER                     SYS             TABLE
KU$_USER_MAPPING_VIEW_TBL      SYS                            SELECT          SELECT_CATALOG_ROLE            SYS             TABLE
SDO_PREFERRED_OPS_USER         MDSYS                          UPDATE          PUBLIC                         MDSYS           TABLE
SDO_PREFERRED_OPS_USER         MDSYS                          INSERT          PUBLIC                         MDSYS           TABLE
SDO_PREFERRED_OPS_USER         MDSYS                          SELECT          PUBLIC                         MDSYS           TABLE
SDO_PREFERRED_OPS_USER         MDSYS                          DELETE          PUBLIC                         MDSYS           TABLE
USER_PRIVILEGE_MAP             SYS                            READ            PUBLIC                         SYS             TABLE

10 rows selected.