第31次：2015-04-07星期二

botang

2015-04-07-a.sql：

1. select  * from dba_sys_privs sp   where sp.grantee='U1';
   
2. 
   
3. select  * from dba_tab_privs  tp where tp.grantee='U1';
   
4. select * from dba_col_privs cp  where cp.grantee='U1';
   
5. 
   
6. select  * from dba_role_privs rp where  rp.grantee='U1';
   
7. 
   
8. select  * from role_sys_privs rsp where rsp.role='CONNECT';
   
9. 
   
10. select * from role_tab_privs rtp where rtp.role='CONNECT';
    
11. 
    
12. select  * from role_role_privs rrp where rrp.role='CONNECT';
    
13. 
    
14. ----
    
15. select  * from dba_objects o where o.owner='HR' and o.object_type='PROCEDURE';
    
16. 
    
17. select * from dba_source  s where s.owner='HR' and s.name='ADD_JOB_HISTORY';
    
18. 
    
19. select  * from hr.job_history;
    
20. ----
    
21. grant create procedure  to u1;
    
22. ----
    
23. select * from  dba_roles;
    
24. 
    
25. select * from role_sys_privs rsp where rsp.role='RESOURCE';
    
26. 
    
27. select  * from role_tab_privs rtp where rtp.role='RESOURCE';
    
28. 
    
29. select * from role_role_privs rrp where rrp.role='RESOURCE';
    
30. 
    
31. 
    
32. ----
    
33. create user u2 identified by oracle_4U;
    
34. 
    
35. grant connect,resource to u2;
    
36. 
    
37. revoke resource from u2;
    
38. -----
    
39. 
    
40. revoke update on hr.employees from u1;
    
41. 
    
42. create role role1;
    
43. 
    
44. select * from role_sys_privs rsp where rsp.role='ROLE1';
    
45. 
    
46. select  * from role_tab_privs rtp where rtp.role='ROLE1';
    
47. 
    
48. select * from role_role_privs rrp where rrp.role='ROLE1';
    
49. 
    
50. grant role1 to u1;
    

复制代码

2015-04-07-b.sql：

1. select  * from dba_sys_privs sp where sp.grantee='HR';
   
2. 
   
3. select  * from dba_tab_privs tp where tp.grantee='U1';
   
4. 
   
5. select  * from dba_role_privs rp where rp.grantee='HR';
   
6. 
   
7. select  * from role_sys_privs  rsp where rsp.role='RESOURCE';
   
8. 
   
9. ---
   
10. 
    
11. revoke   create procedure  from u1;
    
12. 
    
13. revoke resource from u2;
    
14. 
    
15. -----
    
16. 
    
17. grant create procedure to hr with admin option;
    
18. 
    
19. ---
    
20. select  * from role_sys_privs where role='ROLE1';
    
21. 
    
22. select  * from role_tab_privs where role='ROLE1';
    
23. 
    
24. select  * from role_role_privs where role='ROLE1';
    
25. 
    
26. ---
    
27. 
    
28. grant role1 to u1 with admin option;
    
29. 
    
30. revoke role1 from u1;
    
31. 
    
32. -----
    
33. 
    
34. revoke update on hr.employees from role1;

复制代码

2015-04-07-c.sql：

1. grant select any table to u2;
   
2. 
   
3. select  * from dba_role_privs rp where rp.granted_role='ROLE2';
   
4. 
   
5. 
   
6. 
   
7. alter user u2 default role  connect,select_catalog_role;
   
8. 
   
9. create role role2 identified by  java_4U ;
   
10. 
    
11. grant drop any table to role2;
    
12. 
    
13. grant role2 to u2;
    
14. 
    
15. alter  role role2 identified by  redhat_4U;
    
16. 
    
17. select  * from dba_role_privs rp where rp.grantee='U1';
    
18. 
    
19. 
    
20. grant role2 to role1   ;
    
21. ----------
    
22. 
    
23. 
    
24. grant role1 to u1;
    
25. 
    
26. ---
    
27. 
    
28. alter user u1 default role none;
    
29. ---
    
30. select  * from dba_profiles p where p.profile='DEFAULT';

复制代码