课程第48-49次（2017-07-04星期二，2017-07-06星期四）

botang · 发表于 2017-7-5 17:07:56

上完1Z0-052第7章第8章
1Z0-052共19章（上完15章），1Z0-053共21章（上完17章）和1Z0-063多租户部分共9章（上完0章）
总共上完全部49章中的32章

表空间：

create bigfile tablespace tbs2 datafile '/u01/app/oracle/oradata/orcl/tbs2.dbf' size 30M
autoextend on maxsize 4T;
select * from dba_tablespaces;
---
select username,
u.password
from dba_users u order by 1;
select name , password
from user$ order by 1;
grant create session to user1 identified by oracle_4U;
create user user2 identified by oracle_4U;
grant create session to user2;
grant connect to user3 identified by oracle_4U;
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 create table to user1;
select * from dba_ts_quotas;
select * from database_properties;
alter user user1 quota 1M on users;
select * from dba_ts_quotas;
alter user user1 quota 0 on users;
----------------------
select * from dba_sys_privs sp
where sp.GRANTEE='HR';
select * from dba_tab_privs tp
where tp.GRANTEE='HR';
select * from dba_role_privs rp
where rp.GRANTEE='HR';
select * from dba_col_privs cp
where cp.GRANTEE='HR';
---------------------
select * from dba_sys_privs sp
where sp.GRANTEE='USER2';
select * from dba_tab_privs tp
where tp.GRANTEE='USER2';
select * from dba_role_privs rp
where rp.GRANTEE='USER2';
select * from dba_col_privs cp
where cp.GRANTEE='USER2';
-----
select * from role_sys_privs rsp where rsp.ROLE='RESOURCE';
select * from role_tab_privs rtp where rtp.ROLE='RESOURCE';
select * from role_role_privs rrp where rrp.ROLE='RESOURCE';
---
grant resource to user2;
---
select * from dba_sys_privs sp
where sp.GRANTEE='USER2';
select * from dba_tab_privs tp
where tp.GRANTEE='USER2';
select * from dba_role_privs rp
where rp.GRANTEE='USER2';
select * from dba_col_privs cp
where cp.GRANTEE='USER2';
----
grant connect to user3;
----
grant create table to public;
grant unlimited tablespace to public;
---
revoke unlimited tablespace from public;
-----
select * from system_privilege_map order by 2 ;
select * from table_privilege_map;
------
grant select any table to user3 ;
--------------------------------------
select u.ACCOUNT_STATUS ,u.EXPIRY_DATE
from dba_users u
where u.USERNAME='HR';
alter user hr password expire;

复制代码

用户：

select u.USERNAME, u.ACCOUNT_STATUS, u.EXPIRY_DATE
from dba_users u where u.USERNAME='HR';
select *
from dba_sys_privs sp
where sp.GRANTEE='HR';
select *
from dba_tab_privs tp
where tp.GRANTEE='HR';
select *
from dba_role_privs rp
where rp.GRANTEE='HR';
select *
from dba_col_privs cp
where cp.GRANTEE='HR';
-------
select *
from dba_sys_privs sp
where sp.GRANTEE='SH';
select *
from dba_tab_privs tp
where tp.GRANTEE='SH';
select *
from dba_role_privs rp
where rp.GRANTEE='SH';
select *
from dba_col_privs cp
where cp.GRANTEE='SH';
----
grant select_catalog_role to hr;
----
select *
from dba_sys_privs sp
where sp.GRANTEE like 'USER%';
select *
from dba_tab_privs tp
where tp.GRANTEE like 'USER%';
select *
from dba_role_privs rp
where rp.GRANTEE like 'USER%';
select *
from dba_col_privs cp
where cp.GRANTEE like 'USER%';
-----
grant create table to user1 with admin option;
---
revoke create table from user1 ;
----
grant select on hr.employees to user1 with grant option;
revoke select on hr.employees from user1;
revoke select any table from user3;
---
select * from dba_roles order by 1;
select * from role_role_privs rrp
where rrp.ROLE='SELECT_CATALOG_ROLE';
---
create role role1;
grant select on hr.employees to role1;
---
select * from role_sys_privs rrp
where rrp.ROLE='ROLE1';
select * from role_tab_privs rrp
where rrp.ROLE='ROLE1';
----
grant role1 to user1 with admin option;
revoke role1 from user1 ;
----
grant role1 to user1 ;
grant create procedure to user1;
revoke select on hr.employees from role1;
grant select on hr.employees to role1;
create role role2 identified by oracle_4U;
grant role2 to user1;
---
alter user user1 default role none;

复制代码