Oracle数据库用户权限分析

文档课题：Oracle数据库用户权限分析.
1、查询权限
普通用户查询自己所拥有的所有权限.
> show user
USER is "LEO"
> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SYNONYM
CREATE PUBLIC SYNONYM
DROP PUBLIC SYNONYM
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
SET CONTAINER

14 rows selected.
2、DBA查普通用户权限
说明：通常情况下，DBA并不知道普通用户的密码.
2.1、直接授予
DBA查询LEO拥有的权限，此处只显示直接授予LEO的权限.
> show user
USER is "SYS"
> select * from dba_sys_privs where grantee='LEO';

GRA PRIVILEGE                                ADM COM INH
--- ---------------------------------------- --- --- ---
LEO DROP PUBLIC SYNONYM                      NO  NO  NO
LEO CREATE TABLE                             NO  NO  NO
LEO CREATE PUBLIC SYNONYM                    NO  NO  NO
LEO UNLIMITED TABLESPACE                     NO  NO  NO
LEO CREATE SYNONYM                           NO  NO  NO
> select * from dba_tab_privs where grantee='LEO';

no rows selected
2.2、角色授予
说明：一般情况授权普通用户权限，是通过角色的方式授予.
DBA查询LEO拥有的角色.
> COL GRANTEE for a15
> col granted_role for a15
> set line 200
> select * from dba_role_privs where grantee='LEO'

GRANTEE         GRANTED_ROLE    ADM DEL DEF COM INH
--------------- --------------- --- --- --- --- ---
LEO             RESOURCE        NO  NO  YES NO  NO
LEO             CONNECT         NO  NO  YES NO  NO
用户LEO查询.
> select * from user_role_privs;

USERNAME        GRANTED_ROLE    ADM DEL DEF OS_ COM INH
--------------- --------------- --- --- --- --- --- ---
LEO             CONNECT         NO  NO  YES NO  NO  NO
LEO             RESOURCE        NO  NO  YES NO  NO  NO
说明：用户LEO拥有RESOURCE、CONNECT两个角色.
2.3、角色权限
DBA查询角色所拥有的权限.
> select * from dba_sys_privs where GRANTEE='CONNECT';

GRANTEE PRIVILEGE                                ADM COM INH
------- ---------------------------------------- --- --- ---
CONNECT CREATE SESSION                           NO  YES YES
CONNECT SET CONTAINER                            NO  YES YES
> select * from role_sys_privs where role='CONNECT';

ROLE            PRIVILEGE            ADM COM INH
--------------- -------------------- --- --- ---
CONNECT         SET CONTAINER        NO  YES YES
CONNECT         CREATE SESSION       NO  YES YES
SQL> select * from dba_tab_privs where grantee='CONNECT';

no rows selected

> select * from dba_sys_privs where grantee='RESOURCE';

GRANTEE  PRIVILEGE                                ADM COM INH
-------- ---------------------------------------- --- --- ---
RESOURCE CREATE TABLE                             NO  YES YES
RESOURCE CREATE TYPE                              NO  YES YES
RESOURCE CREATE OPERATOR                          NO  YES YES
RESOURCE CREATE INDEXTYPE                         NO  YES YES
RESOURCE CREATE CLUSTER                           NO  YES YES
RESOURCE CREATE PROCEDURE                         NO  YES YES
RESOURCE CREATE SEQUENCE                          NO  YES YES
RESOURCE CREATE TRIGGER                           NO  YES YES

8 rows selected.
> select * from dba_tab_privs where grantee='RESOURCE';

no rows selected

结论：核对权限无误.