第37次活动：2017-11-29（星期三晚上7:00-9:30）

botang

1. select * from system_privilege_map
   
2.   order by name;
   
3. 
   
4. select * from table_privilege_map;
   
5. 
   
6. select * from dba_roles;
   
7. 
   
8. select  * from user$ where name='SH' ;
   
9. 
   
10. grant sysdba to hr;
    
11. 
    
12. grant sysdba to oe;
    
13. 
    
14. alter user oe identified by oracle_4U account unlock;
    
15. 
    
16. select  * from dba_sys_privs sp where sp.privilege='CREATE TABLE';
    
17. 
    
18. revoke sysdba from oe;
    
19. 
    
20. select  * from dba_sys_privs sp where sp.grantee='HR';
    
21. 
    
22. select  * from dba_tab_privs tp where tp.grantee='HR';
    
23. 
    
24. select  * from dba_role_privs rp where rp.grantee='HR';
    
25. 
    
26. grant select_catalog_role to hr;
    
27. 
    
28. 
    
29. select  * from dba_users u
    
30. where u.username='SYS';
    
31. 
    
32. select  * from dba_users u
    
33. where u.username='USER3';

复制代码

SQLPLUS：

1. [root@station90 桌面]# su - oracle
   
2. [oracle@station90 ~]$ cd /u01/app/oracle/product/
   
3. [oracle@station90 product]$ ls
   
4. 11.2.0  11.2.0.3
   
5. [oracle@station90 product]$ cd 11.2.0
   
6. [oracle@station90 11.2.0]$ ls
   
7. dbhome_1  grid
   
8. [oracle@station90 11.2.0]$ cd dbhome_1/
   
9. [oracle@station90 dbhome_1]$ ls
   
10. apex         config  dc_ocm       has            javavm  log      odbc         oui      rdbms         sqlplus                     usm
    
11. assistants   crs     deinstall    hs             jdbc    md       olap         owb      relnotes      srvm                        utl
    
12. bin          csmig   demo         ide            jdev    mesg     OPatch       owm      root.sh       station90.example.com_orcl  wwg
    
13. ccr          css     diagnostics  install        jdk     mgw      opmn         perl     scheduler     sysman                      xdk
    
14. cdata        ctx     dv           instantclient  jlib    network  oracore      plsql    slax          timingframework
    
15. cfgtoollogs  cv      emcli        inventory      ldap    nls      oraInst.loc  precomp  sqldeveloper  ucp
    
16. clone        dbs     EMStage      j2ee           lib     oc4j     ord          racg     sqlj          uix
    
17. [oracle@station90 dbhome_1]$ cd dbs
    
18. [oracle@station90 dbs]$ ls
    
19. hc_DBUA0.dat  hc_orcl.dat  hc_rcat.dat  init.ora  initorcl.ora  lkORCL  orapworcl  orapwrcat  snapcf_orcl.f  spfilercat.ora
    
20. [oracle@station90 dbs]$ vim orapworcl
    
21. [oracle@station90 dbs]$ vim orapworcl
    
22. [oracle@station90 dbs]$ sqlplus /nolog
    
23. 
    
24. SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 29 20:00:11 2017
    
25. 
    
26. Copyright (c) 1982, 2011, Oracle.  All rights reserved.
    
27. 
    
28. SQL> conn hr/oracle_4U@orcl as sysdba
    
29. ERROR:
    
30. ORA-01031: insufficient privileges
    
31. 
    
32. 
    
33. SQL> conn hr/oracle_4U as sysdba
    
34. Connected.
    
35. SQL> conn / as sysdba
    
36. Connected.
    
37. SQL> alter user sys account lock;
    
38. 
    
39. User altered.
    
40. 
    
41. SQL> conn / as sysdba
    
42. Connected.
    
43. SQL> conn sys/oracle_4U@orcl as sysdba
    
44. Connected.
    
45. SQL> conn / as sysdba
    
46. Connected.
    
47. SQL> drop user sys;
    
48. drop user sys
    
49. *
    
50. ERROR at line 1:
    
51. ORA-01031: insufficient privileges
    
52. 
    
53. 
    
54. SQL> drop user sys;
    
55. drop user sys
    
56. *
    
57. ERROR at line 1:
    
58. ORA-01031: insufficient privileges
    
59. 
    
60. 
    
61. SQL> drop user system ;
    
62. drop user system
    
63. *
    
64. ERROR at line 1:
    
65. ORA-01922: CASCADE must be specified to drop 'SYSTEM'
    
66. 
    
67. 
    
68. SQL> drop user system cascade ;
    
69. drop user system cascade
    
70. *
    
71. ERROR at line 1:
    
72. ORA-00604: error occurred at recursive SQL level 1
    
73. ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
    
74. 
    
75. 
    
76. SQL> conn system/oracle_$U
    
77. ERROR:
    
78. ORA-01017: invalid username/password; logon denied
    
79. 
    
80. 
    
81. Warning: You are no longer connected to ORACLE.
    
82. SQL> conn system/oracle_4U
    
83. ERROR:
    
84. ORA-28002: the password will expire within 7 days
    
85. 
    
86. 
    
87. Connected.
    
88. SQL> conn / as sysdba
    
89. Connected.
    
90. SQL> alter user sys password expire;
    
91. 
    
92. User altered.
    
93. 
    
94. SQL> conn / as sysdba
    
95. Connected.
    
96. SQL> conn sys/oracle_4U@orcl as sysdba
    
97. Connected.
    
98. SQL> show parameter authen
    
99. 
    
100. NAME                                     TYPE         VALUE
     
101. ------------------------------------ ----------- ------------------------------
     
102. os_authent_prefix                     string         ops$
     
103. remote_os_authent                     boolean         FALSE
     
104. SQL> create user ops$oracle identified externally  ;
     
105. 
     
106. User created.
     
107. 
     
108. SQL> conn /
     
109. ERROR:
     
110. ORA-01045: user OPS$ORACLE lacks CREATE SESSION privilege; logon denied
     
111. 
     
112. 
     
113. Warning: You are no longer connected to ORACLE.
     
114. SQL> conn / as sysdba
     
115. Connected.
     
116. SQL> grant create session to ops$oracle;
     
117. 
     
118. Grant succeeded.
     
119. 
     
120. SQL> conn /
     
121. Error accessing PRODUCT_USER_PROFILE
     
122. Warning:  Product user profile information not loaded!
     
123. You may need to run PUPBLD.SQL as SYSTEM
     
124. Connected.
     
125. SQL> show user
     
126. USER is "OPS$ORACLE"
     
127. SQL>
     
128. SQL>
     
129. SQL>
     
130. SQL> conn hr/oracle_4U
     
131. ERROR:
     
132. ORA-28002: the password will expire within 7 days
     
133. 
     
134. 
     
135. Error accessing PRODUCT_USER_PROFILE
     
136. Warning:  Product user profile information not loaded!
     
137. You may need to run PUPBLD.SQL as SYSTEM
     
138. Connected.
     
139. SQL> exit
     
140. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
     
141. With the Partitioning, Automatic Storage Management, OLAP, Data Mining
     
142. and Real Application Testing options
     
143. [oracle@station90 dbs]$ cd /u01/app/oracle/product/11.2.0
     
144. [oracle@station90 11.2.0]$ ls
     
145. dbhome_1  grid
     
146. [oracle@station90 11.2.0]$ cd dbhome_1/
     
147. [oracle@station90 dbhome_1]$ ls
     
148. apex         config  dc_ocm       has            javavm  log      odbc         oui      rdbms         sqlplus                     usm
     
149. assistants   crs     deinstall    hs             jdbc    md       olap         owb      relnotes      srvm                        utl
     
150. bin          csmig   demo         ide            jdev    mesg     OPatch       owm      root.sh       station90.example.com_orcl  wwg
     
151. ccr          css     diagnostics  install        jdk     mgw      opmn         perl     scheduler     sysman                      xdk
     
152. cdata        ctx     dv           instantclient  jlib    network  oracore      plsql    slax          timingframework
     
153. cfgtoollogs  cv      emcli        inventory      ldap    nls      oraInst.loc  precomp  sqldeveloper  ucp
     
154. clone        dbs     EMStage      j2ee           lib     oc4j     ord          racg     sqlj          uix
     
155. [oracle@station90 dbhome_1]$ cd sql
     
156. sqldeveloper/ sqlj/         sqlplus/      
     
157. [oracle@station90 dbhome_1]$ cd sqlplus/
     
158. [oracle@station90 sqlplus]$ ls
     
159. admin  bin  doc  lib  mesg
     
160. [oracle@station90 sqlplus]$ cd admin/
     
161. [oracle@station90 admin]$ ls
     
162. glogin.sql  help  libsqlplus.def  plustrce.sql  pupbld.sql
     
163. [oracle@station90 admin]$ sqlplus /nolog
     
164. 
     
165. SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 29 20:21:49 2017
     
166. 
     
167. Copyright (c) 1982, 2011, Oracle.  All rights reserved.
     
168. 
     
169. SQL> conn system/oracle_4U
     
170. ERROR:
     
171. ORA-28002: the password will expire within 7 days
     
172. 
     
173. 
     
174. Connected.
     
175. SQL> @pupbld.sql
     
176. DROP SYNONYM PRODUCT_USER_PROFILE
     
177.              *
     
178. ERROR at line 1:
     
179. ORA-01434: private synonym to be dropped does not exist
     
180. 
     
181. 
     
182.   DATE_VALUE FROM PRODUCT_USER_PROFILE
     
183.                   *
     
184. ERROR at line 3:
     
185. ORA-00980: synonym translation is no longer valid
     
186. 
     
187. 
     
188. DROP TABLE PRODUCT_USER_PROFILE
     
189.            *
     
190. ERROR at line 1:
     
191. ORA-00942: table or view does not exist
     
192. 
     
193. 
     
194. ALTER TABLE SQLPLUS_PRODUCT_PROFILE ADD (LONG_VALUE LONG)
     
195. *
     
196. ERROR at line 1:
     
197. ORA-00942: table or view does not exist
     
198. 
     
199. 
     
200. 
     
201. Table created.
     
202. 
     
203. DROP TABLE PRODUCT_PROFILE
     
204.            *
     
205. ERROR at line 1:
     
206. ORA-00942: table or view does not exist
     
207. 
     
208. 
     
209. DROP VIEW PRODUCT_PRIVS
     
210. *
     
211. ERROR at line 1:
     
212. ORA-00942: table or view does not exist
     
213. 
     
214. 
     
215. 
     
216. View created.
     
217. 
     
218. 
     
219. Grant succeeded.
     
220. 
     
221. 
     
222. Synonym dropped.
     
223. 
     
224. 
     
225. Synonym created.
     
226. 
     
227. DROP SYNONYM PRODUCT_USER_PROFILE
     
228.              *
     
229. ERROR at line 1:
     
230. ORA-01434: private synonym to be dropped does not exist
     
231. 
     
232. 
     
233. 
     
234. Synonym created.
     
235. 
     
236. 
     
237. Synonym dropped.
     
238. 
     
239. 
     
240. Synonym created.
     
241. 
     
242. SQL> conn /
     
243. Connected.
     
244. SQL> conn hr/oracle_4U
     
245. ERROR:
     
246. ORA-28002: the password will expire within 7 days
     
247. 
     
248. 
     
249. Connected.
     
250. SQL> !pwd
     
251. /u01/app/oracle/product/11.2.0/dbhome_1/sqlplus/admin
     
252. 
     
253. SQL> conn /
     
254. Connected.
     
255. SQL> show user
     
256. USER is "OPS$ORACLE"
     
257. SQL> conn / as sysdba
     
258. Connected.
     
259. SQL> conn /
     
260. Connected.
     
261. SQL> conn hr/oracle_4U@orcl
     
262. ERROR:
     
263. ORA-28002: the password will expire within 7 days
     
264. 
     
265. 
     
266. Connected.
     
267. SQL> conn /@orcl
     
268. ERROR:
     
269. ORA-01017: invalid username/password; logon denied
     
270. 
     
271. 
     
272. Warning: You are no longer connected to ORACLE.
     
273. SQL> conn /
     
274. Connected.
     
275. SQL> conn / as sysdba
     
276. Connected.
     
277. SQL> exit
     
278. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
     
279. With the Partitioning, Automatic Storage Management, OLAP, Data Mining
     
280. and Real Application Testing options
     
281. [oracle@station90 admin]$ echo $TNS_ADMIN
     
282. /u01/app/oracle/product/11.2.0/grid/network/admin
     
283. [oracle@station90 admin]$ sqlplus /nolog
     
284. 
     
285. SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 29 20:27:08 2017
     
286. 
     
287. Copyright (c) 1982, 2011, Oracle.  All rights reserved.
     
288. 
     
289. SQL> conn /
     
290. ERROR:
     
291. ORA-01017: invalid username/password; logon denied
     
292. 
     
293. 
     
294. SQL> conn / as sysdba
     
295. ERROR:
     
296. ORA-01031: insufficient privileges
     
297. 
     
298. 
     
299. SQL> conn /
     
300. ERROR:
     
301. ORA-01017: invalid username/password; logon denied
     
302. 
     
303. 
     
304. SQL> exit
     
305. [oracle@station90 admin]$ sqlplus /nolog
     
306. 
     
307. SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 29 20:27:35 2017
     
308. 
     
309. Copyright (c) 1982, 2011, Oracle.  All rights reserved.
     
310. 
     
311. SQL> conn /
     
312. Connected.
     
313. SQL> conn / as sysdba
     
314. Connected.
     
315. SQL>
     
316. 
     

复制代码

口令过期：

1. select * from dba_users u
   
2. where u.username='USER3';
   
3. 
   
4. alter user user3 account lock;
   
5. 
   
6. alter user user3 account unlock;
   
7. 
   
8. alter user user3 password expire;
   
9. 
   
10. select * from dba_users u
    
11. where u.username='USER3';

复制代码

1. select  * from dba_sys_privs sp where sp.grantee='USER3';
   
2. 
   
3. select  * from dba_tab_privs tp where tp.grantee='USER3';
   
4. 
   
5. select  * from dba_role_privs rp where rp.grantee='USER3';
   
6. 
   
7. grant create table to user3  with admin  option ;
   
8. 
   
9. revoke create table from user3;

复制代码

1. grant select  on   employees to user3 with grant option;
   
2. 
   
3. revoke select  on employees from user3;
   
4. 
   
5. grant update (email) on employees to user3;
   

复制代码

1. 
   
2. select  * from dba_col_privs  c
   
3.   where c.grantee='USER3';
   
4.   
   
5.   select  * from dba_sys_privs sp where sp.grantee='USER3';
   
6. 
   
7. select  * from dba_tab_privs tp where tp.grantee='USER3';
   
8. 
   
9. select  * from dba_role_privs rp where rp.grantee='USER3';
   
10.   
    
11.   select * from dba_roles order by 1;
    
12.   
    
13.   create role role1;
    
14.   
    
15.   select  * from role_sys_privs rsp where rsp.role='ROLE1';
    
16.   
    
17.   select * from role_tab_privs rtp where rtp.role='ROLE1';
    
18.   
    
19.   
    
20.   
    
21.   select * from role_role_privs rrp where rrp.role='ROLE1';
    
22.   
    
23.   grant select on hr.employees to role1;
    
24.   
    
25.   grant update ( email )  on hr.employees to role1;
    
26.   
    
27.   grant role1 to user3 with admin option;
    
28.   
    
29.   revoke role1 from user3;
    
30.   
    
31.   revoke update  on hr.employees from role1;
    
32.   
    
33.   revoke select  on hr.employees from role1;
    
34.   
    
35.   

复制代码

1. grant select on hr.employees to role1;
   
2. 
   
3. create role role2 ;
   
4. 
   
5. grant role1 to role2;
   
6. 
   
7. 
   
8. grant role2 to user1;
   
9. 
   
10. select * from role_role_privs rrp where rrp.role='ROLE2';
    
11. 
    
12. select  * from dba_role_privs rp
    
13. where rp.grantee='USER1';
    
14. 
    
15. alter user   user1 default role connect;
    
16. 
    
17. 
    
18. select  * from dba_roles r where r.role='ROLE2';
    
19. 
    
20. 
    
21. alter role role2 identified by oracle_4U;
    
22. 
    
23. 
    
24. grant role2 to user2;
    
25. select  * from dba_role_privs rp
    
26. where rp.grantee='USER2';
    
27. 
    
28.   alter user   user1 default role none;
    
29. 
    
30. 
    
31.    
    
32.   select  * from role_sys_privs rsp where rsp.role='RESOURCE';
    
33.   
    
34.   select * from role_tab_privs rtp where rtp.role='RESOURCE';
    
35.   
    
36.   
    
37.   
    
38.   select * from role_role_privs rrp where rrp.role='RESOURCE';
    
39.   
    
40.   grant resource to user2;

复制代码

unlimited tablespace 彩蛋：