将角色授予角色oracle,Oracle教程之Oracle管理角色（一）

1、角色的功能：角色(role)是相关权限的集合，使用角色的主要目的是简化权限管理。

建立角色――给角色授权――将角色授予用户/角色

2、查看系统建立的role

09:05:09 SQL> select * from dba_roles;

ROLE                           PASSWORD

------------------------------ --------

CONNECT                        NO

RESOURCE                       NO

DBA                            NO

SELECT_CATALOG_ROLE            NO

EXECUTE_CATALOG_ROLE           NO

DELETE_CATALOG_ROLE            NO

EXP_FULL_DATABASE              NO

IMP_FULL_DATABASE              NO

RECOVERY_CATALOG_OWNER         NO

GATHER_SYSTEM_STATISTICS       NO

LOGSTDBY_ADMINISTRATOR         NO

AQ_ADMINISTRATOR_ROLE          NO

AQ_USER_ROLE                   NO

GLOBAL_AQ_USER_ROLE            GLOBAL

SCHEDULER_ADMIN                NO

HS_ADMIN_ROLE                  NO

AUTHENTICATEDUSER              NO

ROLE                           PASSWORD

------------------------------ --------

OEM_ADVISOR                    NO

OEM_MONITOR                    NO

WM_ADMIN_ROLE                  NO

JAVAUSERPRIV                   NO

JAVAIDPRIV                     NO

JAVASYSPRIV                    NO

JAVADEBUGPRIV                  NO

EJBCLIENT                      NO

JAVA_ADMIN                     NO

JAVA_DEPLOY                    NO

CTXAPP                         NO

XDBADMIN                       NO

XDBWEBSERVICES                 NO

OLAP_DBA                       NO

OLAP_USER                      NO

MGMT_USER                      NO

PLUSTRACE                      NO

34 rows selected.

3、建立角色( create role)

SQL> create role pub_role;

Role created.

SQL> create role prv_role identified by oralce;【带口令的，一般非默认角色都应该加上口令，便于分配和管理】

Role created.

4、给角色授权

SQL> grant create session,create table to pub_role;

Grant succeeded.

SQL> grant select on scott.emp to prv_role;

Grant succeeded.

5、查看角色拥有的权限

――SYSTEM PRIVILEGE

SQL> select * from role_sys_privs where role='&name';

Enter value for name: DBA

old   1: select * from role_sys_privs where role='&name'

new   1: select * from role_sys_privs where role='DBA'

ROLE                           PRIVILEGE                                ADM

------------------------------ ---------------------------------------- ---

DBA                            CREATE SESSION                           YES

DBA                            ALTER SESSION                            YES

DBA                            DROP TABLESPACE                          YES

DBA                            BECOME USER                              YES

DBA                            DROP ROLLBACK SEGMENT                    YES

DBA                            SELECT ANY TABLE                         YES

DBA                            INSERT ANY TABLE                         YES

DBA                            UPDATE ANY TABLE                         YES

...............

DBA                            READ ANY FILE GROUP                      YES

DBA                            CREATE EXTERNAL JOB                      YES

160 rows selected.

SQL> select * from role_sys_privs where role='&name';

Enter value for name: CONNECT

old   1: select * from role_sys_privs where role='&name'

new   1: select * from role_sys_privs where role='CONNECT'

ROLE                           PRIVILEGE                                ADM

------------------------------ ---------------------------------------- ---

CONNECT                        CREATE SESSION                           NO

SQL> select * from role_sys_privs where role='&name';

Enter value for name: RESOURCE

old   1: select * from role_sys_privs where role='&name'

new   1: select * from role_sys_privs where role='RESOURCE'

――隐含unlimited tablespace 权限(可以在任何一个表空间上拥有配额)

如果将该角色分配给用户，一般都会将该权限收回，在进行表空间配额的分配

ROLE                 PRIVILEGE                      ADMIN_OPT

-------------------- ------------------------------ ---------

RESOURCE             CREATE SEQUENCE                NO

RESOURCE             CREATE TRIGGER                 NO

RESOURCE             CREATE CLUSTER                 NO

RESOURCE             CREATE PROCEDURE               NO

RESOURCE             CREATE TYPE                    NO

RESOURCE             CREATE OPERATOR                NO

RESOURCE             CREATE TABLE                   NO

RESOURCE             CREATE INDEXTYPE               NO

8 rows selected.

SQL> select * from role_sys_privs where role='&name';

Enter value for name: PUB_ROLE

old   1: select * from role_sys_privs where role='&name'

new   1: select * from role_sys_privs where role='PUB_ROLE'

ROLE                 PRIVILEGE                      ADMIN_OPT

-------------------- ------------------------------ ---------

PUB_ROLE             CREATE TABLE                   NO

PUB_ROLE             CREATE SESSION                 NO

――OBJECT PRIVILEGE

SQL> COL PRIVILEGE FOR A20

SQL> select * from role_tab_privs where role='&name';

Enter value for name: PRV_ROLE

old   1: select * from role_tab_privs where role='&name'

new   1: select * from role_tab_privs where role='PRV_ROLE'

ROLE                 OWNER           TABLE_NAME      COLUMN_NAME     PRIVILEGE            GRANTABLE

-------------------- --------------- --------------- --------------- -------------------- ---------------

PRV_ROLE             SCOTT           EMP                             SELECT               NO