如何管理數據庫權限與角色

下面一起來了解下如何管理數據庫權限與角色，相信大家看完肯定會受益匪淺，文字在精不在多，希望如何管理數據庫權限與角色這篇短內容是你想要的。                                                           

授予用戶的系統權限
SQL> grant create table,create sequence,create view to tpcc;
Grant succeeded.

查詢授予用戶的系統權限
SQL> col grantee for a20
SQL> col privilege for a30
SQL> col admin_option for a15
SQL> select * from dba_sys_privs where grantee ='TPCC';

GRANTEE         PRIVILEGE                      ADMIN_OPTION
--------------- ------------------------------ ---------------
TPCC            CREATE TABLE                   NO
TPCC            UNLIMITED TABLESPACE           NO
TPCC            CREATE VIEW                    NO
TPCC            ALTER SESSION                  NO
TPCC            CREATE SEQUENCE                NO

撤銷授予用戶的系統權限
SQL> revoke create sequence from tpcc;
Revoke succeeded.

SQL> select * from dba_sys_privs where grantee ='TPCC';
GRANTEE         PRIVILEGE                      ADMIN_OPTION
--------------- ------------------------------ ---------------
TPCC            CREATE TABLE                   NO
TPCC            UNLIMITED TABLESPACE           NO
TPCC            CREATE VIEW                    NO
TPCC            ALTER SESSION                  NO

授予用戶的對象權限
SQL> grant select on scott.emp to tpcc;
Grant succeeded.

查詢授予用戶的對象權限
SQL> col owner for a20
SQL> col table_name for a20
SQL> col grantee for a15
SQL> col grantor for a15
SQL> col privilege for a30
SQL> select grantee,owner,table_name,grantor,privilege from dba_tab_privs where grantee = 'TPCC';
GRANTEE         OWNER                TABLE_NAME           GRANTOR         PRIVILEGE
--------------- -------------------- -------------------- --------------- ------------------------------
TPCC            SYS                  DBMS_LOCK            SYS             EXECUTE
TPCC            SCOTT                EMP                  SCOTT           SELECT

撤銷授予用戶的對象權限
SQL> revoke select on scott.emp from tpcc;
Revoke succeeded.

SQL> select grantee,owner,table_name,grantor,privilege from dba_tab_privs where grantee = 'TPCC';

GRANTEE         OWNER                TABLE_NAME           GRANTOR         PRIVILEGE
--------------- -------------------- -------------------- --------------- ------------------------------
TPCC            SYS                  DBMS_LOCK            SYS             EXECUTE

查詢數據庫的角色
SQL> col role for a30
SQL> select * from dba_roles;

ROLE                           PASSWORD_REQUIRED        AUTHENTICATION_TYPE
------------------------------ ------------------------ ---------------------------------
CONNECT                        NO                       NONE
RESOURCE                       NO                       NONE
DBA                            NO                       NONE
SELECT_CATALOG_ROLE            NO                       NONE
EXECUTE_CATALOG_ROLE           NO                       NONE
DELETE_CATALOG_ROLE            NO                       NONE
EXP_FULL_DATABASE              NO                       NONE
IMP_FULL_DATABASE              NO                       NONE
LOGSTDBY_ADMINISTRATOR         NO                       NONE
DBFS_ROLE                      NO                       NONE
AQ_ADMINISTRATOR_ROLE          NO                       NONE

查詢授予角色的權限
SQL> select * from role_sys_privs where role in ('CONNECT','RESOURCE');

ROLE                           PRIVILEGE                      ADMIN_OPTION
------------------------------ ------------------------------ ---------------
RESOURCE                       CREATE SEQUENCE                NO
RESOURCE                       CREATE TRIGGER                 NO
RESOURCE                       CREATE CLUSTER                 NO
RESOURCE                       CREATE PROCEDURE               NO
RESOURCE                       CREATE TYPE                    NO
CONNECT                        CREATE SESSION                 NO
RESOURCE                       CREATE OPERATOR                NO
RESOURCE                       CREATE TABLE                   NO
RESOURCE                       CREATE INDEXTYPE               NO

查詢授予用戶的角色
SQL> col admin_option for a15
SQL> col default_role for a15
SQL> col granted_role for a30
SQL> select * from dba_role_privs where grantee = 'TPCC';

GRANTEE         GRANTED_ROLE                   ADMIN_OPTION    DEFAULT_ROLE
--------------- ------------------------------ --------------- ---------------
TPCC            RESOURCE                       NO              YES
TPCC            CONNECT                        NO              YES

查詢用戶獲得的權限
SQL> conn tpcc/tpcc
Connected.

SQL> select * from session_privs;   

PRIVILEGE
------------------------------
CREATE SESSION
ALTER SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE VIEW
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR

PRIVILEGE
------------------------------
CREATE INDEXTYPE

看完如何管理數據庫權限與角色這篇文章后，很多讀者朋友肯定會想要了解更多的相關內容，如需獲取更多的行業信息，可以關注我們的行業資訊欄目。