创建使用口令的角色,并分配给用户

郑全

创建使用口令的角色,并分配给用户

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

   权限启用成功.

   

 

郑全

顶一下