课程第46次：2016-07-11星期一

botang · 发表于 2016-7-12 09:37:32

【上完1Z0-052的第7章】表空间
【开始1Z0-052的第8章】用户和权限
【上完1Z0-051】：共12章（0 1 2 3 4 5 6 7 8 9 10 11）
【1Z0-052】：共14章（0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18）
【1Z0-053】：共15章（0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20）
表示已经上过的，表示还没上的。
完全按照上课顺序：
https://www.botangdb.com/mytrain/201601/00000028.html

2016-07-11a.sql：

select * from dba_segments s where s.owner='HR'
and s.segment_name='EMPLOYEES';
select * from dba_extents e where e.owner='HR' and
e.segment_name='EMPLOYEES';
select * from sys_dba_segs s where s.owner='HR'
and s.segment_name='EMPLOYEES';
---
select * from database_properties;
alter database default tablespace users;
alter tablespace users2 rename to users;
---
select * from dba_profiles where profile='DEFAULT';
---
select * from dba_roles r where r.role='CONNECT';
select * from role_sys_privs rsp where rsp.role='CONNECT';
select * from role_tab_privs rtp where rtp.role='CONNECT';
select * from role_role_privs rrp where rrp.role='CONNECT';
----
grant connect to user1 identified by oracle_4U;
grant create session to user2 identified by oracle_4U;
---
select * from dba_users where username='USER1';
alter user user1 password expire;
alter user user1 account lock;

复制代码

2016-07-11b.sql：

select count( distinct sp.privilege )
from dba_sys_privs sp;
select distinct sp.privilege
from dba_sys_privs sp order by sp.privilege;
select * from dba_tab_privs tp where tp.grantee='SH';
select * from dba_col_privs tp where tp.grantee='SH';
alter user user1 account unlock;
---
revoke select on hr.employees from sh;
select * from dba_sys_privs where grantee='SH' ;
grant select any table to sh with admin option;
---
select * from dba_sys_privs where grantee='USER1' ;
revoke select any table from user1;
grant select any table to public;
revoke select any table from user1;
revoke select any table from public;

复制代码

2016-07-11c.sql：

select * from role_role_privs rrp where rrp.role='ROLE2';
select * from role_sys_privs rrp where rrp.role='ROLE1';
select * from role_tab_privs rrp where rrp.role='ROLE1';
select * from dba_role_privs rp where rp.grantee='USER1';
revoke select on hr.employees from role1;
select * from dba_sys_privs sp where sp.grantee='HR';
select * from dba_ts_quotas;
alter user user1 quota 1M on users;
alter user user1 quota 0 on users;
grant resource to user1;
select * from role_sys_privs rsp where rsp.role='RESOURCE';
revoke resource from user1;

复制代码