我在all_directories中有一个目录,但我需要找出它关联的权限,也就是在它上面授予了什么权限?
这会给你一个目录上授予的角色、用户和权限:
SELECT *
FROM all_tab_privs
WHERE table_name = 'your_directory'; --> needs to be upper case
是的,在all_TAB_privs视图中可以找到;-) 更好的名称应该是"ALL_OBJECT_PRIVS",因为它还包括PL/SQL对象及其执行权限。
您可以使用以下命令查看所有目录的权限
SELECT *
from all_tab_privs
where table_name in
(select directory_name
from dba_directories);
以下是授予权限的 SQL 语句,用于在需要备份已完成工作时使用。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);
在Oracle 11g R2(至少11.2.02版本)中,有一个名为datapump_dir_objs的视图。
SELECT * FROM datapump_dir_objs;
名称
,路径
以及当前连接的用户的读取
和写入
权限。它不会显示当前用户没有读取或写入权限的任何目录对象。我不确定你是指哪些Oracle用户可以读写该目录,还是Oracle目录对象和底层操作系统目录之间的权限关系。
DCookie已经涵盖了Oracle方面的内容,以下内容摘自这里的Oracle文档。
为目录授予的权限与定义操作系统目录的权限是独立创建的,两者可能完全对应,也可能不完全对应。例如,如果将样本用户hr授予目录对象上的READ权限,但相应的操作系统目录未定义Oracle数据库进程的READ权限,则会发生错误。
/*
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
/
这里是关于目录的一个例子:
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.
这是另一个名称中带有USER的表:
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.