课程第47/48次（2017-02-15星期三，2017-02-17星期五）

botang

Oracle用户管理：
SYS执行:

1. select  * from database_properties;
   
2. 
   
3. select  u.USERNAME,
   
4.             u.DEFAULT_TABLESPACE,
   
5.                         u.TEMPORARY_TABLESPACE,
   
6.                         u.PROFILE,
   
7.                         u.INITIAL_RSRC_CONSUMER_GROUP,
   
8.                         u.AUTHENTICATION_TYPE,
   
9.                         u.PASSWORD
   
10.     from dba_users u
    
11. where u.USERNAME='USER1';
    
12. 
    
13. select  u.NAME,u.PASSWORD   from user$ u
    
14.   where u.NAME='USER1';
    
15.   
    
16.   ---
    
17.   select  * from system_privilege_map
    
18.     where name like '%INDEX%';
    
19.   
    
20.   select  *
    
21.     from dba_sys_privs sp
    
22.         where sp.GRANTEE='USER1';
    
23.         
    
24. select  *
    
25. from dba_role_privs rp
    
26.   where rp.GRANTEE='USER1';
    
27.   
    
28.   
    
29.   select  *
    
30.    from dba_tab_privs tp
    
31.     where tp.GRANTEE='USER1';        
    
32.         
    
33.   
    
34. grant create table to user1;
    
35. 
    
36. select  * from role_sys_privs rsp
    
37.   where rsp.ROLE='CONNECT';
    
38.   
    
39.   select  * from role_tab_privs rtp
    
40.    where rtp.ROLE='CONNECT';
    
41.    
    
42.    select  * from role_role_privs rrp
    
43.     where rrp.ROLE='CONNECT';
    
44.         ---
    
45.           select  * from table_privilege_map ;
    
46.          
    
47.           select  * from dba_roles;
    
48.    
    
49. select * from dba_ts_quotas;
    
50. 
    
51. alter user user1 quota 1M on users;
    
52. 
    
53. alter user user1 quota 0 on users;
    
54. 
    
55. ---
    
56. grant create session to user2 identified by oracle_4U;
    
57. 
    
58. grant connect to user3 identified by oracle_4U;
    
59. ---
    
60. 
    
61. select  *
    
62.     from dba_sys_privs sp
    
63.         where sp.GRANTEE='USER2';
    
64.         
    
65. select  *
    
66. from dba_role_privs rp
    
67.   where rp.GRANTEE='USER2';
    
68.   
    
69.   
    
70.   select  *
    
71.    from dba_tab_privs tp
    
72.     where tp.GRANTEE='USER2';
    
73.         
    
74.         ---
    
75.         
    
76.          select  *
    
77.     from dba_sys_privs sp
    
78.         where sp.GRANTEE='USER3';
    
79.         
    
80. select  *
    
81. from dba_role_privs rp
    
82.   where rp.GRANTEE='USER3';
    
83.   
    
84.   
    
85.   select  *
    
86.    from dba_tab_privs tp
    
87.     where tp.GRANTEE='USER3';        
    
88. 
    
89. ---
    
90. 
    
91. create user ops$oracle
    
92. identified externally;
    
93. 
    
94. select  u.USERNAME,
    
95.             u.DEFAULT_TABLESPACE,
    
96.                         u.TEMPORARY_TABLESPACE,
    
97.                         u.PROFILE,
    
98.                         u.INITIAL_RSRC_CONSUMER_GROUP,
    
99.                         u.AUTHENTICATION_TYPE,
    
100.                         u.PASSWORD
     
101.     from dba_users u
     
102. where u.USERNAME='OPS$ORACLE';
     
103. 
     
104. grant connect to ops$oracle;
     
105. 
     
106. select  password  from user$
     
107.   where name='SYS';
     
108.   
     
109.   select  username, account_Status
     
110.    from dba_users u
     
111.     where u.USERNAME ='SYS';
     
112.         
     
113.         alter user  user1 account lock;
     
114.         --
     
115. 
     
116.         
     
117.         
     
118.         ---
     
119. select  * from table_privilege_map;
     
120. 
     
121. select  *
     
122.    from dba_tab_privs tp
     
123.     where tp.GRANTEE='USER1';        
     
124.         
     
125.         ---
     
126.                 select  * from dba_col_privs cp;
     
127.         
     
128.           select  *
     
129.     from dba_sys_privs sp
     
130.         where sp.GRANTEE='USER1';
     
131.         
     
132. select  *
     
133. from dba_role_privs rp
     
134.   where rp.GRANTEE='USER1';
     
135.   
     
136.   
     
137.   select  *
     
138.    from dba_tab_privs tp
     
139.     where tp.GRANTEE='USER1';
     
140.         
     
141. 
     
142. 
     
143. ---
     
144. grant select  any table to  user1  with admin option;        
     
145. 
     
146. revoke select  any table from user1 ;
     
147. 
     
148. revoke select any table from user2;
     
149. ---
     
150. grant select  on hr.employees to user1 with grant option;
     
151. 
     
152. revoke select  on hr.employees from user1 ;
     
153. 
     
154.   
     
155.   

复制代码

HR执行：

1. create or replace procedure  proc1
   
2. is
   
3. begin
   
4.    update employees set salary=salary+1  where employee_id=100;
   
5.    commit;
   
6. end;
   
7. 
   
8. select  salary from employees
   
9.   where employee_id=100;
   
10.   
    
11.   begin
    
12.      proc1;
    
13.    end;
    
14.    ---

复制代码

关于Internal这个神奇用户：

2017-02-17：

1. 
   
2. select  * from dba_sys_privs sp where GRANTEE in  ('USER1','HR');
   
3. 
   
4. select  * from dba_tab_privs tp where GRANTEE in  ('USER1','HR');
   
5. 
   
6. select  * from dba_role_privs rp where GRANTEE in  ('USER1','HR');
   
7. 
   
8. select  * from dba_col_privs cp where GRANTEE in  ('USER1','HR');
   
9. 
   
10. ---role 1
    
11. 
    
12. create role role1;
    
13. 
    
14. select  * from dba_roles;
    
15. 
    
16. select * from role_sys_privs rsp where ROLE='RESOURCE';
    
17. 
    
18. select  * from role_tab_privs rtp where ROLE='ROLE1';
    
19. 
    
20. 
    
21. 
    
22. select  * from role_role_privs rrp where ROLE='ROLE1';
    
23. 
    
24. grant select  on hr.employees to role1;
    
25. 
    
26. grant role1 to user1 with admin option;
    
27. 
    
28. revoke role1 from user1;
    
29. 
    
30. ---role2
    
31. grant create procedure to user1;
    
32. 
    
33. grant update(salary) on hr.employees to user1;
    
34. 
    
35. revoke update  on hr.employees  from user1;
    
36. 
    
37. ---
    
38. create role role2;
    
39. 
    
40. grant update(salary) on hr.employees to role2;
    
41. 
    
42. grant role2 to user1;
    
43. 
    
44. ---
    
45. 
    
46. revoke update on hr.employees from role2;
    
47.   grant update(salary) on hr.employees to role2;
    
48.   
    
49.   ---role3
    
50. select  * from dba_role_privs rp where rp.GRANTED_ROLE='ROLE3';
    
51. 
    
52. alter user user1 default  role connect;
    
53. create role role3 identified by oracle_4U;
    
54. 
    
55. select  * from dba_roles r where r.ROLE='ROLE3';
    
56. 
    
57. grant role3 to user1;
    
58. alter user user1 default  role connect,role3;
    
59. 
    
60. -----
    
61. 
    
62. grant select  any table to user1;
    
63. 
    
64. revoke select  any table from user1;
    
65. 
    
66. grant select_catalog_role to hr;
    
67. 
    
68. grant resource to user1;
    
69. 
    
70. ---
    
71. grant select  on sh.sales to public;
    
72. 
    
73. revoke  select  on sh.sales from public;
    
74. 
    
75. ---
    
76. 
    
77. select  username, account_status, u.EXPIRY_DATE,u.LOCK_DATE,  u.PROFILE  from dba_users  u
    
78.   where username='USER3';
    
79. 
    
80. alter  user user1 account lock;
    
81.   
    
82. alter user user1 password expire;
    
83. 
    
84. alter user user1 account unlock;
    
85. 
    
86. ---
    
87. select  * from dba_profiles p
    
88. where p.RESOURCE_TYPE='PASSWORD'
    
89.   and p.PROFILE='DEFAULT';
    
90.   
    
91.   --
    
92.   create profile profile1 limit
    
93.   PASSWORD_LIFE_TIME 1/1440;
    
94.   --
    
95.   select  * from dba_profiles p
    
96. where p.RESOURCE_TYPE='PASSWORD'
    
97.   and p.PROFILE='PROFILE1';
    
98.   
    
99.   alter user user3 profile profile1;
    
100.   
     
101.   
     
102.   select  username, account_status, u.EXPIRY_DATE,u.LOCK_DATE ,u.CREATED ,u.PROFILE  from dba_users  u
     
103.   where username='USER3';
     
104.   
     
105.     select  * from dba_profiles p
     
106. where p.RESOURCE_TYPE='PASSWORD'
     
107.   and p.PROFILE='DEFAULT';
     
108. 
     
109.   alter profile profile1 limit
     
110.   PASSWORD_GRACE_TIME 1/1440;
     
111.   
     
112.   
     
113.   alter profile profile1 limit
     
114.   FAILED_LOGIN_ATTEMPTS 1;
     
115.   
     
116.   alter profile profile1 limit
     
117.   PASSWORD_LOCK_TIME 1/1440;
     
118.   
     
119.   
     
120. 
     
121. 
     

复制代码