主页 / dba / 问题 / 139803
Accepted
Kshitiz Sharma
Asked: 2016-05-28 14:52:10 +0800 CST

为什么 Oracle 不显示我的权限？

  • 772

我有一个能够删除、更改、创建表和索引的用户。但是当我列出我的 Oracle 用户的权限时，它没有显示任何CREATE TABLE或其他此类权限。我如何在没有权限的情况下执行这些操作，或者为什么 Oracle 不显示它们？

运行以下查询不返回任何内容：

SELECT * FROM USER_TAB_PRIVS;
SELECT * FROM all_tab_privs_recd WHERE grantee = 'user_kshitiz';

运行SELECT * FROM USER_SYS_PRIVS回报：

USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
user_kshitiz                   UNLIMITED TABLESPACE                     NO 
user_kshitiz                   CREATE SEQUENCE                          NO

运行SELECT * FROM USER_ROLE_PRIVS回报：

USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
user_kshitiz                   CONNECT                        NO  YES NO 
user_kshitiz                   RESOURCE                       NO  YES NO 
user_kshitiz                   SCHEMA_1_RW_ROLE               NO  YES NO

检查每个角色的权限不会返回任何结果。

SELECT * FROM ROLE_TAB_PRIVS WHERE ROLE = 'CONNECT';
SELECT * FROM ROLE_TAB_PRIVS WHERE ROLE = 'RESOURCE';
SELECT * FROM ROLE_TAB_PRIVS WHERE ROLE = 'SCHEMA_1_RW_ROLE';

那么动力从何而来？

oracle permissions

1 个回答

  1. Best Answer

    如您所见，您已将RESOURCE角色授予用户user_kshitz。你必须查询ROLE_SYS_PRIVS.

    SQL> select * from role_sys_privs where role='RESOURCE';

ROLE                   PRIVILEGE                ADM
------------------------------ ---------------------------------------- ---
RESOURCE               CREATE TRIGGER               NO
RESOURCE               CREATE SEQUENCE              NO
RESOURCE               CREATE TYPE              NO
RESOURCE               CREATE PROCEDURE             NO
RESOURCE               CREATE CLUSTER               NO
RESOURCE               CREATE OPERATOR              NO
RESOURCE               CREATE INDEXTYPE             NO
RESOURCE               CREATE TABLE             NO

8 rows selected.

    您还可以查询SESSION_PRIVS以查找授予用户的所有权限。

    SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE

9 rows selected.
    • 2

相关问题