如何管理数据库权限与角色

下面一起来了解下如何管理数据库权限与角色，相信大家看完肯定会受益匪浅，文字在精不在多，希望如何管理数据库权限与角色这篇短内容是你想要的。

授予用户的系统权限SQL> grant create table,create sequence,create view to tpcc;Grant succeeded.查询授予用户的系统权限SQL> col grantee for a20SQL> col privilege for a30SQL> col admin_option for a15SQL> select * from dba_sys_privs where grantee ='TPCC';GRANTEE         PRIVILEGE                      ADMIN_OPTION--------------- ------------------------------ ---------------TPCC            CREATE TABLE                   NOTPCC            UNLIMITED TABLESPACE           NOTPCC            CREATE VIEW                    NOTPCC            ALTER SESSION                  NOTPCC            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                   NOTPCC            UNLIMITED TABLESPACE           NOTPCC            CREATE VIEW                    NOTPCC            ALTER SESSION                  NO授予用户的对象权限SQL> grant select on scott.emp to tpcc;Grant succeeded.查询授予用户的对象权限SQL> col owner for a20SQL> col table_name for a20SQL> col grantee for a15SQL> col grantor for a15SQL> col privilege for a30SQL> 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             EXECUTETPCC            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 a30SQL> select * from dba_roles;ROLE                           PASSWORD_REQUIRED        AUTHENTICATION_TYPE------------------------------ ------------------------ ---------------------------------CONNECT                        NO                       NONERESOURCE                       NO                       NONEDBA                            NO                       NONESELECT_CATALOG_ROLE            NO                       NONEEXECUTE_CATALOG_ROLE           NO                       NONEDELETE_CATALOG_ROLE            NO                       NONEEXP_FULL_DATABASE              NO                       NONEIMP_FULL_DATABASE              NO                       NONELOGSTDBY_ADMINISTRATOR         NO                       NONEDBFS_ROLE                      NO                       NONEAQ_ADMINISTRATOR_ROLE          NO                       NONE查询授予角色的权限SQL> select * from role_sys_privs where role in ('CONNECT','RESOURCE');ROLE                           PRIVILEGE                      ADMIN_OPTION------------------------------ ------------------------------ ---------------RESOURCE                       CREATE SEQUENCE                NORESOURCE                       CREATE TRIGGER                 NORESOURCE                       CREATE CLUSTER                 NORESOURCE                       CREATE PROCEDURE               NORESOURCE                       CREATE TYPE                    NOCONNECT                        CREATE SESSION                 NORESOURCE                       CREATE OPERATOR                NORESOURCE                       CREATE TABLE                   NORESOURCE                       CREATE INDEXTYPE               NO查询授予用户的角色SQL> col admin_option for a15SQL> col default_role for a15SQL> col granted_role for a30SQL> select * from dba_role_privs where grantee = 'TPCC';GRANTEE         GRANTED_ROLE                   ADMIN_OPTION    DEFAULT_ROLE--------------- ------------------------------ --------------- ---------------TPCC            RESOURCE                       NO              YESTPCC            CONNECT                        NO              YES查询用户获得的权限SQL> conn tpcc/tpccConnected.SQL> select * from session_privs;   PRIVILEGE------------------------------CREATE SESSIONALTER SESSIONUNLIMITED TABLESPACECREATE TABLECREATE CLUSTERCREATE VIEWCREATE SEQUENCECREATE PROCEDURECREATE TRIGGERCREATE TYPECREATE OPERATORPRIVILEGE------------------------------CREATE INDEXTYPE

看完如何管理数据库权限与角色这篇文章后，很多读者朋友肯定会想要了解更多的相关内容，如需获取更多的行业信息，可以关注我们的行业资讯栏目。