Oracle用户权限及角色管理

--用户权限及角色管理


create user c##mldnuser
identified by java_android
default tablespace system
quota 30m on system
quota 20m on system
account unlock
password expire;


select username,user_id,default_tablespace,temporary_tablespace,created,lock_date,profile
from dba_users
where username='C##MLDNUSER';


select * from dba_ts_quotas where username='C##MLDNUSER';


CREATE PROFILE c##mldn_profile LIMIT
CPU_PER_SESSION 10000
LOGICAL_READS_PER_SESSION 20000
CONNECT_TIME 60
IDLE_TIME 30
SESSIONS_PER_USER 10
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_LIFE_TIME 60
PASSWORD_REUSE_TIME 30
PASSWORD_GRACE_TIME 6;


SELECT * FROM dba_profiles WHERE PROFILE='C##MLDN_PROFILE';


create user c##mldnjava identified by hello
profile c##mldn_profile;


--配置已存在用户使用的概要文件
alter user c##mldnuser profile c##mldn_profile;


select username,user_id,default_tablespace,temporary_tablespace,created,lock_date,profile
from dba_users
where username in('C##MLDNJAVA','C##MLDNUSER');


--修改该要文件
alter profile c##mldn_profile limit
cpu_per_session 1000
password_life_time 10;


--删除该要文件
drop profile c##mldn_profile cascade;


--维护用户
--修改用户密码
alter user c##mldnuser identified by hellojava;
--控制用户锁定
alter user c##mldnuser account lock;


select username,user_id,default_tablespace,temporary_tablespace,created,lock_date,profile
from dba_users
where username='C##MLDNUSER';


alter user c##mldnuser account unlock;


--让密码失效
alter user c##mldnuser password expire;
--修改用户表空间配额
alter user c##mldnuser
quota 20m on system
quota 35m on system;


select * from dba_ts_quotas where username='C##MLDNUSER';


--删除用户
drop user c##mldnuser;
--权限管理
grant create session to c##mldnuser;


grant create table,create sequence,create view to c##mldnuser with admin option;
grant create table,create sequence to c##mldnjava;


--查看用户权限
select *
from dba_sys_privs
where grantee in('C##MLDNJAVA','C##MLDNUSER')
ORDER BY GRANTEE DESC;


--撤销用户权限
revoke create table，create view from c##mldnuser;


select *
from dba_sys_privs
where grantee in('C##MLDNJAVA','C##MLDNUSER')
ORDER BY GRANTEE DESC;


revoke create sequence from c##mldnjava;


--对象权限
select * from scott.dept;


grant select,insert on scott.dept to c##mldnuser;


grant update(dname) on scott.dept to c##mldnuser;


--角色
create role c##mldn_role_a;
create role c##mldn_role_b identified by hellojava;
--角色授权
grant create session,create table,create view,create sequence to c##mldn_role_a;
grant create session,create any table,insert any table to c##mldn_role_b;


select * from role_sys_privs
where role in('C##MLDN_ROLE_A','C##MLDN_ROLE_B')
order by role;


--为用户授予角色
grant c##mldn_role_a to c##mldnuser;
grant c##mldn_role_a,c##mldn_role_b to c##mldnjava;


--修改角色及回收角色权限
alter role c##mldn_role_a identified by hellomldn;
alter role c##mldn_role_b not identified;


revoke create session from c##mldn_role_a;


select * from role_sys_privs
where role='C##MLDN_ROLE_A'
order by role;


--删除角色
drop role c##mldn_role_b;


--预定义角色
select * from role_sys_privs
where role in('CONNECT','RESOURCE')
order by role;


grant connect,resource to c##mldnuser;