【oracle】用户权限及角色管理

CREAT USER exercise IDENTIFIED BY 123456

DEFAULT TABLESPACE mldn_data

TEMPORARY mldn_temp

QUOTA 1M ON mldn_data       创建用户

QUOTA 2M ON users

ACCONT UNLOCK

PASSWORD EXPIRE;

DROP USER exercise;     删除用户

 

GRANT CREATE SESSION TO exercise WITH ADMINOPTION;     为用户授权,使用“WITH ADMIN OPTION”，表示该用户可以将这些权限继续授予别的用户

  

CREAT ROLE LICONGCONG;    创建角色

 

CREAT ROLE LICONGCONG;    删除角色

 

CREAT ROLE LICONGCONG IDENTIFIED BY456789;    创建带有密码的角色

 

SELECT * FROM DBA_ROLES;   查看角色字典

 

GRANT CREATE SESSION,CREATE TABLE,CREATEVIEW TO LICONGCONG;为角色授权

 

SELECT * FROM ROLE_SYS_PRIVS

WHERE ROLE IN (LICONGCONG)    查看角色权限

ORDER BY ROLE;

 

GRANT LICONGCONG TO exercise;   为用户授予角色

 

PRIVILEGE;      查看该用户通过角色被授予的权限（SQLPLUS）登录

 

ALTER ROLE LICONGCONG IDENTIFIED BY123456;       修改角色密码

 

ALTER ROLE LICONGCONG NOT IDENTIFIED;       取消角色密码

 

REVOKE CREATE SESSION FROM LICONGCONG;    收回角色权限

 

SELECT *

FROM ROLE_SYS_PRIVS

WHERE ROLE IN('CONNECT','RESOURCE')    查看'CONNECT'和'RESOURCE'的权限，这两个角色拥有经常用到的权限

ORDER BY ROLE;