实训目的:
1、理解用户与模式的概念,掌握oracle中用户管理的基本方法
2、理解系统权限、对象权限的概念,掌握分配权限的方法
3、理解角色的概念,掌握角色的应用方法
实训内容:
一、用户管理与应用
1、查看用户与模式
select * from dba_users;
select * from all_users;
select * from user_users;
select * from user_sys_privs;
2.创建用户
create user 用户名 identified by 密码
create user s224364345 identified by "123456";
3.给予新用户基本权限 create session , create any table
GRANT create session,create any table TO s224364345;
4.调整用户的密码、锁定状态、配额等
调整密码:
alter user s224364345 identified by "123";
锁定状态:
alter user s224364345 account lock;
alter user s224364345 account unlock;
配额:
alter user test_quota quota 100M on s224364345;
alter user test_quota quota unlimited on s224364345;
5.删除用户!!
drop user s224364345;
二、权限管理与应用
系统权限system privilege
对象权限object privilege 多 on objectName
1.授予或回收系统权限
CREATE USER s224364345 identified by "123456";
GRANT create table,create view to s224364345;
REVOKE create table,create view from s224364345;
2.授予或回收对象权限
GRANT delete,insert on HELP to s224364345;
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='s224364345';
3、转交管理权限或收回管理权限(权限传递)
GRANT alter any table to s224364345 whit admin option;--管理员给s224364345授权
create user s224364333 identified by 123456;--创建一个新用户
grant alter any table to s224364333; --权限传递
revoke alter any table from s224364345 whit admin option;-- 回收管理权限
三、角色管理与应用
role 一系列权限的命名集合,目的是简化权限管理的复杂性
1.创建角色
create role role224364345;
2.将各类权限放入角色
GRANT create table,create view to role224364345;
GRANT select,insert ON HELP TO role224364345;
3.将角色授予某用户
将角色授予用户s224364345
grant role224364345 to s224364345;