创建使用口令的角色,并分配给用户
1.创建使用口令的角色
CREATE ROLE role_name
[NOT IDENTIFIED(默认) | IDENTIFIED BY password | EXTERNALLY |GLOBALLY];
sql>create role manager identified by oracle;
2.创建后查看角色
SQL> select role,password_required,AUTHENTICATION_TYPE from dba_roles where role='MANAGER';
ROLE PASSWORD AUTHENTICAT
------------------------------ -------- -----------
MANAGER YES PASSWORD
3.赋予系统权限
SQL> GRANT CREATE TABLE,CREATE VIEW,CREATE SESSION TO manager WITH ADMIN OPTION;
Grant succeeded.
4.赋予对象权限
SQL> GRANT SELECT ,INSERT ,UPDATE ON hr.employees TO manager;
Grant succeeded.
5.查看角色的系统权限（role_sys_privs）
SQL> SELECT * FROM role_sys_privs WHERE role = 'MANAGER';
ROLE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
MANAGER CREATE SESSION YES
MANAGER CREATE TABLE YES
MANAGER CREATE VIEW YES
6.查看角色的对象权限(role_tab_privs)
SQL> SELECT * FROM role_tab_privs WHERE role = 'MANAGER';
ROLE OWNER TABLE_NAME COLUMN_NAME PRIVILEGE GRA
---------- ---------- ---------- -------------------- --------------- ---
MANAGER HR EMPLOYEES UPDATE NO
MANAGER HR EMPLOYEES INSERT NO
MANAGER HR EMPLOYEES SELECT NO
6.创建用户
sql>create user sztech identified by sztech;
7.将角色赋予用户（grant）：
语法：
GRANT role_name [, role_name, ...]
TO user_name | role | PUBLIC [, user_name | role | PUBLIC, ...]
[WITH ADMIN OPTION];
sql>GRANT manager TO sztech;
8.查看角色授予了哪些用户(dba_role_privs)
SQL> SELECT * FROM dba_role_privs WHERE granted_role = 'MANAGER';
GRANTEE GRANTED_ROLE ADM DEF
---------- -------------------- --- ---
SYS MANAGER YES YES
SZTECH MANAGER NO YES
9.以sztech用户登陆系统
SQL> conn sztech/sztech
ERROR:
ORA-01045: user SZTECH lacks CREATE SESSION privilege; logon denied
这个时候,无法登陆系统,权限无效
10.单独授予创建session的权限给sztech
sql>grant connect to sztech;
SELECT * FROM dba_role_privs WHERE grantee = 'SZTECH';
11.查看获得的权限
SQL> SELECT * FROM dba_role_privs WHERE grantee = 'SZTECH';
GRANTEE GRANTED_ROLE ADM DEF
---------- -------------------- --- ---
SZTECH CONNECT NO NO
SZTECH MANAGER NO YES
12.设置默认角色
sql>ALTER USER sztech DEFAULT ROLE all;
再次查看默认权限
SQL> SELECT * FROM dba_role_privs WHERE grantee = 'SZTECH';
GRANTEE GRANTED_ROLE ADM DEF
---------- -------------------- --- ---
SZTECH CONNECT NO YES
SZTECH MANAGER NO YES
13.查看用户拥有哪些权限
SQL> CONN sztech/sztech;
SQL> select * from session_privs;
PRIVILEGE
---------------
CREATE SESSION
目前看,权限只有connect中的权限,而manger中的权限没有,需要单独激活.
14.激活角色
SQL> set role connect,manager identified by oracle;
注意,这里角色manager有口令,所以,需要identified by oracle.
15.查看当前用户sztech权限
SQL> select * from session_privs;
PRIVILEGE
---------------
CREATE SESSION
CREATE TABLE
CREATE VIEW
权限启用成功.
作者：思庄学习中心
链接：https://www.jianshu.com/p/7e6aeabcc18a