oracle角色管理

什么是角色

角色是数据库各种权限的集合，使用角色可以方便地管理数据库特权，角色可以赋予其他用户也可以赋予其他角色。

关于角色的描述都存储在数据字典的DBA_ROLES中

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

ROLE                                                                                       PASSWORD_REQUIRED        AUTHENTICATION_TYPE
------------------------------------------------------------------------------------------ ------------------------ ---------------------------------
AQ_USER_ROLE                                                                               NO                       NONE
DATAPUMP_EXP_FULL_DATABASE                                                                 NO                       NONE
DATAPUMP_IMP_FULL_DATABASE                                                                 NO                       NONE
ADM_PARALLEL_EXECUTE_TASK                                                                  NO                       NONE
GATHER_SYSTEM_STATISTICS                                                                   NO                       NONE
JAVA_DEPLOY                                                                                NO                       NONE
RECOVERY_CATALOG_OWNER                                                                     NO                       NONE
SCHEDULER_ADMIN                                                                            NO                       NONE
HS_ADMIN_SELECT_ROLE                                                                       NO                       NONE
HS_ADMIN_EXECUTE_ROLE                                                                      NO                       NONE
HS_ADMIN_ROLE                                                                              NO                       NONE

ROLE                                                                                       PASSWORD_REQUIRED        AUTHENTICATION_TYPE
------------------------------------------------------------------------------------------ ------------------------ ---------------------------------
GLOBAL_AQ_USER_ROLE                                                                        GLOBAL                   GLOBAL
OEM_ADVISOR                                                                                NO                       NONE
OEM_MONITOR                                                                                NO                       NONE
WM_ADMIN_ROLE                                                                              NO                       NONE
JAVAUSERPRIV                                                                               NO                       NONE
JAVAIDPRIV                                                                                 NO                       NONE
JAVASYSPRIV                                                                                NO                       NONE
JAVADEBUGPRIV                                                                              NO                       NONE
EJBCLIENT                                                                                  NO                       NONE
JMXSERVER                                                                                  NO                       NONE
JAVA_ADMIN                                                                                 NO                       NONE

ROLE                                                                                       PASSWORD_REQUIRED        AUTHENTICATION_TYPE
------------------------------------------------------------------------------------------ ------------------------ ---------------------------------
CTXAPP                                                                                     NO                       NONE
XDBADMIN                                                                                   NO                       NONE
XDB_SET_INVOKER                                                                            NO                       NONE
AUTHENTICATEDUSER                                                                          NO                       NONE
XDB_WEBSERVICES                                                                            NO                       NONE
XDB_WEBSERVICES_WITH_PUBLIC                                                                NO                       NONE
XDB_WEBSERVICES_OVER_HTTP                                                                  NO                       NONE
OLAP_DBA                                                                                   NO                       NONE
ORDADMIN                                                                                   NO                       NONE
OLAP_XS_ADMIN                                                                              NO                       NONE
OWB_USER                                                                                   NO                       NONE

ROLE                                                                                       PASSWORD_REQUIRED        AUTHENTICATION_TYPE
------------------------------------------------------------------------------------------ ------------------------ ---------------------------------
CWM_USER                                                                                   NO                       NONE
OLAP_USER                                                                                  NO                       NONE
SPATIAL_WFS_ADMIN                                                                          NO                       NONE
WFS_USR_ROLE                                                                               NO                       NONE
SPATIAL_CSW_ADMIN                                                                          NO                       NONE
CSW_USR_ROLE                                                                               NO                       NONE
MGMT_USER                                                                                  NO                       NONE
APEX_ADMINISTRATOR_ROLE                                                                    NO                       NONE
OWB$CLIENT                                                                                 YES                      PASSWORD
OWB_DESIGNCENTER_VIEW                                                                      NO                       NONE

已选择54行。

创建角色

CREATE ROLE role_name IDENTIFIED BY password



SQL> CREATE ROLE LALIN IDENTIFIED BY lalin;

角色已创建。

修改角色

角色可以修改，但是oracle只允许修改其验证方法：

ALTER ROLE role_name [NOT IDENTIFIED | IDENTIFIED BY]


SQL> ALTER ROLE LALIN IDENTIFIED BY 111111;

角色已丢弃。

SQL> ALTER ROLE LALIN NOT IDENTIFIED;

角色已丢弃。

赋予角色权限

GRANT 权限 | 角色 TO 角色名

为LALIN赋予权限

SQL> GRANT CREATE  SESSION,SELECT ANY TABLE,CREATE VIEW TO LALIN;

授权成功。

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

ROLE                                                                                       PRIVILEGE                                                                                                            ADMIN_OPT
------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ ---------
LALIN                                                                                      CREATE VIEW                                                                                                          NO
LALIN                                                                                      CREATE SESSION                                                                                                       NO
LALIN                                                                                      SELECT ANY TABLE                                                                                                     NO

将权限和角色授予MANAGER

SQL> CREATE ROLE MANAGER IDENTIFIED BY oracle;

角色已创建。

SQL> GRANT CREATE ANY TABLE,LALIN TO MANAGER; 

授权成功。

回收和删除角色

回收MANAGER的LALIN角色

SQL> revoke LALIN from MANAGER;

撤销成功。

删除MANAGER角色

SQL> drop role MANAGER;

角色已删除。