Oracle（6）

Oracle基础六

一、用户管理与应用

sqlplus sys/oracle123@orcl as sysdba;
conn u15436350/u15436350@orcl;

1、查看用户与模式

prompt 2、创建用户
prompt drop user u15436350 cascade;
prompt create user  u15436350 identified by u15436350;
drop user u15436350 cascade;
create user  u15436350 identified by u15436350;

prompt  3、给予新用户基本权限  create session , resource, create any table
prompt  grant create session to u15436350;
prompt  grant resource to u15436350;
prompt  grant create any table to u15436350;
prompt  grant  create session,resource,create any table to u15436350;

grant create session to u15436350;
grant resource to u15436350;
grant create any table to u15436350;
grant  create session,resource,create any table to u15436350;

prompt  4、调整用户的密码、锁定状态、配额等
prompt alter user u15436350 identified by newpassword;
prompt alter user u15436350 account lock;
alter user u15436350 identified by newpassword;
alter user u15436350 account lock;
--unlock
prompt  5、删除用户！！
prompt drop user u15436350 cascade;
drop user u15436350 cascade;

二、权限管理与应用

系统权限system privilege
对象权限object privilege on objectName

prompt  1、授予或回收系统权限
prompt  2、授予或回收对象权限
prompt create user  u15436350 identified by u15436350;
prompt grant create any table to u15436350;
prompt revoke create any table from u15436350;
create user  u15436350 identified by u15436350;
grant create any table to u15436350;
revoke create any table from u15436350;
-- 在任何地方创建表
prompt grant select on scott.emp to u15436350;
prompt grant select on scott.emp to u15436350 with grant option ;
grant select on scott.emp to u15436350;
grant select on scott.emp to u15436350 with grant option ;
--可传递
--grant 会被截断，admin不会

三、角色管理与应用

role 一系列权限的命名集合,目的是简化权限管理的复杂性

prompt  1、创建角色 
prompt  2、将各类权限放入角色
prompt  3、将角色授予某用户
prompt  create role newUser5;
prompt  grant  create session,resource,create any table to newUser5
prompt  create  user n15436366 identified by n15436366;
create role newUser5;
grant  create session,resource,create any table to newUser5;
create  user n15436366 identified by n15436366;

prompt  conn n15436350/n15436350@orcl;
grant newUser1 to n15436350;
revoke create any table from newUser1;
grant select on scott.emp to newUser1;
select * from scott.emp;
grant newDb to s15436350;
rollback;