什么是角色
角色是数据库各种权限的集合,使用角色可以方便地管理数据库特权,角色可以赋予其他用户也可以赋予其他角色。
关于角色的描述都存储在数据字典的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;
角色已删除。