活动第40/41次（2018-07-18星期三，2018-07-21星期六上午）

botang

查询段头块（放的本块最重要的元数据：位图或者freelist）:

1. select  s.header_block
   
2.   from dba_segments s where s.tablespace_name='EXAMPLE'
   
3.   and s.segment_name='EMPLOYEES';

复制代码

	HEADER_BLOCK
1	202

查询段的第一个块:

1. select e.block_id
   
2. from dba_extents e
   
3.   where e.segment_name='EMPLOYEES';

复制代码

	BLOCK_ID
1	200

------------------------------------------------------------------------------用户账号的4个方面
1. Authentication  2. Account 3. Password 4. Session

sys的两重身份：

1. [oracle@station90 ~]$ sqlplus /nolog
   
2. 
   
3. SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 18 20:21:43 2018
   
4. 
   
5. Copyright (c) 1982, 2014, Oracle.  All rights reserved.
   
6. 
   
7. SQL> conn / as sysdba
   
8. Connected.
   
9. SQL> show user
   
10. USER is "SYS"
    
11. SQL> conn / as sysoper
    
12. Connected.
    
13. SQL> show user
    
14. USER is "PUBLIC"
    
15. SQL>
    

复制代码

举个外部验证的例子：

1. SQL> show parameter authen
   
2. 
   
3. NAME                                     TYPE         VALUE
   
4. ------------------------------------ ----------- ------------------------------
   
5. os_authent_prefix                     string         ops$
   
6. remote_os_authent                     boolean         FALSE
   
7. SQL> create user ops$oracle identified externally ;
   
8. 
   
9. User created.
   
10. 
    
11. SQL>
    

复制代码

1. SQL> grant connect to ops$oracle;
   
2. 
   
3. Grant succeeded.
   
4. 
   
5. SQL>
   

复制代码

1. SQL> conn /
   
2. Connected.
   
3. SQL> show user
   
4. USER is "OPS$ORACLE"
   
5. SQL>
   

复制代码

1. SQL> conn /
   
2. Connected.
   
3. SQL> show user
   
4. USER is "OPS$ORACLE"
   
5. SQL> create table t_ops( a  number );
   
6. create table t_ops( a  number )
   
7. *
   
8. ERROR at line 1:
   
9. ORA-01031: insufficient privileges
   

复制代码

SYS授权：

1. grant create table to ops$oracle;
   
2. 
   
3. select  * from dba_sys_privs sp
   
4. where sp.grantee='OPS$ORACLE';

复制代码

	GRANTEE	PRIVILEGE	ADMIN_OPTION	COMMON
1	OPS$ORACLE	CREATE TABLE	NO	NO

1. SQL> conn /
   
2. Connected.
   
3. SQL> show user
   
4. USER is "OPS$ORACLE"
   
5. SQL> create table t_ops( a  number );
   
6. create table t_ops( a  number )
   
7. *
   
8. ERROR at line 1:
   
9. ORA-01031: insufficient privileges
   
10. 
    
11. 
    
12. SQL>
    
13. SQL>
    
14. SQL> create table t_ops( a  number );
    
15. 
    
16. Table created.
    
17. 
    
18. SQL>
    

复制代码

现在如果要插入数据，就会报错：

1. SQL> create table t_ops( a  number );
   
2. 
   
3. Table created.
   
4. 
   
5. SQL> insert into t_ops values (1) ;
   
6. insert into t_ops values (1)
   
7.             *
   
8. ERROR at line 1:
   
9. ORA-01950: no privileges on tablespace 'USERS'
   
10. 
    
11. 
    

复制代码

1. select  * from dba_ts_quotas;

复制代码

	TABLESPACE_NAME	USERNAME	BYTES	MAX_BYTES	BLOCKS	MAX_BLOCKS	DROPPED
1	SYSAUX	AUDSYS	1507328	-1	184	-1	NO
2	SYSAUX	GSMADMIN_INTERNAL	1441792	104857600	176	12800	NO
3	SYSAUX	APPQOSSYS	0	-1	0	-1	NO
4	SYSAUX	OLAPSYS	0	-1	0	-1	NO
5	SYSAUX	FLOWS_FILES	0	-1	0	-1	NO
6	EXAMPLE	OE	10420224	-1	1272	-1	NO

1. alter user ops$oracle quota 64K on users;

复制代码

1. select  * from dba_ts_quotas;

复制代码

	TABLESPACE_NAME	USERNAME	BYTES	MAX_BYTES	BLOCKS	MAX_BLOCKS	DROPPED
1	SYSAUX	FLOWS_FILES	0	-1	0	-1	NO
2	SYSAUX	OLAPSYS	0	-1	0	-1	NO
3	SYSAUX	APPQOSSYS	0	-1	0	-1	NO
4	SYSAUX	GSMADMIN_INTERNAL	1441792	104857600	176	12800	NO
5	SYSAUX	AUDSYS	1507328	-1	184	-1	NO
6	USERS	OPS$ORACLE	0	65536	0	8	NO
7	EXAMPLE	OE	10420224	-1	1272	-1	NO

1. SQL> insert into t_ops values (1) ;
   
2. insert into t_ops values (1)
   
3.             *
   
4. ERROR at line 1:
   
5. ORA-01950: no privileges on tablespace 'USERS'
   
6. 
   
7. 
   
8. SQL> insert into t_ops values (1) ;
   
9. 
   
10. 1 row created.
    
11. 
    
12. SQL> commit;
    
13. 
    
14. Commit complete.
    
15. 
    
16. SQL>
    

复制代码

如果回收quota，还能插入数据吗？

1. alter user ops$oracle quota 0 on users;
   

复制代码

1. SQL> insert into t_ops values (2) ;
   
2. 
   
3. 1 row created.
   
4. 
   
5. SQL> commit;
   
6. 
   
7. Commit complete.
   
8. 
   

复制代码

关于quota的使用的简单的实验而复杂的原理：

1. SQL> begin
   
2.   2   for i in 3..4000
   
3.   3   loop
   
4.   4     insert into t_ops values (i);
   
5.   5   end loop;
   
6.   6   commit;
   
7.   7  end;
   
8.   8  /
   
9. begin
   
10. *
    
11. ERROR at line 1:
    
12. ORA-01536: space quota exceeded for tablespace 'USERS'
    
13. ORA-06512: at line 4
    
14. 
    
15. 
    
16. SQL> select  count(*) from t_ops;
    
17. 
    
18.   COUNT(*)
    
19. ----------
    
20.          2
    
21. 
    
22. SQL> begin
    
23.   2   for i in 3..2000
    
24.   3   loop
    
25.   4     insert into t_ops values (i);
    
26.   5   end loop;
    
27.   6   commit;
    
28.   7  end;
    
29.   8  /
    
30. 
    
31. PL/SQL procedure successfully completed.
    
32. 
    
33. SQL> select  count(*) from t_ops;
    
34. 
    
35.   COUNT(*)
    
36. ----------
    
37.       2000
    
38. 
    
39. SQL>
    

复制代码

其他建用户的方法：

1. grant connect to user1 identified by oracle_4U;
   
2. 
   
3. grant create session to user2 identified by oracle_4U;
   
4. 
   
5. select  * from system_privilege_map spm
   
6. where spm.name='CREATE SESSION';

复制代码

	PRIVILEGE	NAME	PROPERTY
1	-5	CREATE SESSION	0

1. select * from role_sys_privs rsp
   
2. where rsp.role='CONNECT';

复制代码

	ROLE	PRIVILEGE	ADMIN_OPTION	COMMON
1	CONNECT	SET CONTAINER	NO	YES
2	CONNECT	CREATE SESSION	NO	YES

--------------------------------------------------------之后经常会使用以下的语句查询一个账户的Account和Athentication状态：

1. select  u.username,
   
2.             u.password,
   
3.             u.account_status,
   
4.             u.lock_date,
   
5.             u.expiry_date,
   
6.             u.authentication_type            
   
7. from dba_users u
   
8. where u.username like 'USER%' or
   
9.             u.username='OPS$ORACLE';

复制代码

	USERNAME	PASSWORD	ACCOUNT_STATUS	LOCK_DATE	EXPIRY_DATE	AUTHENTICATION_TYPE
1	OPS$ORACLE	EXTERNAL	OPEN			EXTERNAL
2	USER1		OPEN		1/14/2019 9:01:20 PM	PASSWORD
3	USER2		OPEN		1/14/2019 9:01:51 PM	PASSWORD

------------------------------------------------以下研究ORA-28000：

1. alter user user1 account lock;         

复制代码

1. SQL> conn user1/oracle_4U
   
2. ERROR:
   
3. ORA-28000: the account is locked
   

复制代码

user1账号永久被锁定。

	USERNAME	PASSWORD	ACCOUNT_STATUS	LOCK_DATE	EXPIRY_DATE	AUTHENTICATION_TYPE
1	OPS$ORACLE	EXTERNAL	OPEN			EXTERNAL
2	USER1		LOCKED	7/18/2018 9:14:00 PM	1/14/2019 9:01:20 PM	PASSWORD
3	USER2		OPEN		1/14/2019 9:01:51 PM	PASSWORD

--------------------------------------------------以下研究ORA-28001：

1. alter user user2 password expire;        

复制代码

1. SQL> conn user2/oracle_4U
   
2. ERROR:
   
3. ORA-28001: the password has expired
   
4. 
   
5. 
   
6. Changing password for user2
   
7. New password:
   

复制代码

	USERNAME	PASSWORD	ACCOUNT_STATUS	LOCK_DATE	EXPIRY_DATE	AUTHENTICATION_TYPE
1	OPS$ORACLE	EXTERNAL	OPEN			EXTERNAL
2	USER1		LOCKED	7/18/2018 9:14:00 PM	1/14/2019 9:01:20 PM	PASSWORD
3	USER2		EXPIRED		7/18/2018 9:19:32 PM	PASSWORD

他自己就能解决：

1. Changing password for user2
   
2. New password: *********
   
3. Retype new password: *********
   
4. Password changed
   
5. Connected.
   
6. SQL>
   

复制代码

	USERNAME	PASSWORD	ACCOUNT_STATUS	LOCK_DATE	EXPIRY_DATE	AUTHENTICATION_TYPE
1	OPS$ORACLE	EXTERNAL	OPEN			EXTERNAL
2	USER1		LOCKED	7/18/2018 9:14:00 PM	1/14/2019 9:01:20 PM	PASSWORD
3	USER2		OPEN		1/14/2019 9:21:24 PM	PASSWORD

-------------------------------------------------------------------------以下研究ORA-28002（过了宽限期后伴随ORA-28001出现）/ORA-28007：

1. CREATE PROFILE "PROFILE1" LIMIT CPU_PER_SESSION DEFAULT
   
2. CPU_PER_CALL DEFAULT
   
3. CONNECT_TIME DEFAULT
   
4. IDLE_TIME DEFAULT
   
5. SESSIONS_PER_USER DEFAULT
   
6. LOGICAL_READS_PER_SESSION DEFAULT
   
7. LOGICAL_READS_PER_CALL DEFAULT
   
8. PRIVATE_SGA DEFAULT
   
9. COMPOSITE_LIMIT DEFAULT
   
10. PASSWORD_LIFE_TIME DEFAULT
    
11. PASSWORD_GRACE_TIME DEFAULT
    
12. PASSWORD_REUSE_MAX DEFAULT
    
13. PASSWORD_REUSE_TIME DEFAULT
    
14. PASSWORD_LOCK_TIME 0.000694444
    
15. FAILED_LOGIN_ATTEMPTS 1
    
16. PASSWORD_VERIFY_FUNCTION DEFAULT
    

复制代码

1. select  u.username,
   
2.             u.password,
   
3.             u.account_status,
   
4.             u.lock_date,
   
5.             u.expiry_date,
   
6.             u.authentication_type,
   
7.             u.profile            
   
8. from dba_users u
   
9. where u.username  ='USER2';
   
10. 
    
11. alter user user2 profile profile1;
    
12. 
    
13. select  * from dba_profiles p
    
14.   where p.profile='PROFILE1' and p.resource_type <> 'KERNEL';

复制代码

	PROFILE	RESOURCE_NAME	RESOURCE_TYPE	LIMIT	COMMON
1	PROFILE1	FAILED_LOGIN_ATTEMPTS	PASSWORD	1	NO
2	PROFILE1	PASSWORD_LIFE_TIME	PASSWORD	DEFAULT	NO
3	PROFILE1	PASSWORD_REUSE_TIME	PASSWORD	DEFAULT	NO
4	PROFILE1	PASSWORD_REUSE_MAX	PASSWORD	DEFAULT	NO
5	PROFILE1	PASSWORD_VERIFY_FUNCTION	PASSWORD	DEFAULT	NO
6	PROFILE1	PASSWORD_LOCK_TIME	PASSWORD	.0006	NO
7	PROFILE1	PASSWORD_GRACE_TIME	PASSWORD	DEFAULT	NO

试错一次密码：

1. [oracle@station90 ~]$ sqlplus /nolog
   
2. 
   
3. SQL*Plus: Release 12.1.0.2.0 Production on Sat Jul 21 09:23:27 2018
   
4. 
   
5. Copyright (c) 1982, 2014, Oracle.  All rights reserved.
   
6. 
   
7. SQL> conn user2/oracle_4U
   
8. Connected.
   
9. SQL> conn user2/oracle_5U
   
10. ERROR:
    
11. ORA-01017: invalid username/password; logon denied
    
12. 
    
13. 
    
14. Warning: You are no longer connected to ORACLE.
    
15. SQL> conn user2/oracle_4U
    
16. ERROR:
    
17. ORA-28000: the account is locked
    
18. 
    
19. 
    
20. SQL> conn user2/oracle_4U
    
21. Connected.
    
22. SQL>
    

复制代码

1. select  u.username,
   
2.             u.password,
   
3.             u.account_status,
   
4.             u.lock_date,
   
5.             u.expiry_date,
   
6.             u.authentication_type,
   
7.             u.profile            
   
8. from dba_users u
   
9. where u.username  ='USER2';

复制代码

	USERNAME	PASSWORD	ACCOUNT_STATUS	LOCK_DATE	EXPIRY_DATE	AUTHENTICATION_TYPE	PROFILE
1	USER2		LOCKED(TIMED)	7/21/2018 9:25:21 AM	1/14/2019 9:21:24 PM	PASSWORD	PROFILE1

ora-28002：


上面图中的第一个lock和第2个lock不是同一个意思，第1个lock 实际上是“过期”。


刚刚改完口令生命期的时候，马上查看用户：

1. select  u.username,
   
2.             u.password,
   
3.             u.account_status,
   
4.             u.lock_date,
   
5.             u.expiry_date,
   
6.             u.authentication_type,
   
7.             u.profile            
   
8. from dba_users u
   
9. where u.username  ='USER2';

复制代码

	USERNAME	PASSWORD	ACCOUNT_STATUS	LOCK_DATE	EXPIRY_DATE	AUTHENTICATION_TYPE	PROFILE
1	USER2		OPEN		7/18/2018 9:22:24 PM	PASSWORD	PROFILE1

------------------------------

1. select  u.username,
   
2.             u.password,
   
3.             u.account_status,
   
4.             u.lock_date,
   
5.             u.expiry_date,
   
6.             u.authentication_type,
   
7.             u.profile            
   
8. from dba_users u
   
9. where u.username  ='USER2';

复制代码

	USERNAME	PASSWORD	ACCOUNT_STATUS	LOCK_DATE	EXPIRY_DATE	AUTHENTICATION_TYPE	PROFILE
1	USER2		EXPIRED(GRACE)		7/21/2018 9:40:45 AM	PASSWORD	PROFILE1

1. SQL> conn user2/oracle_4U
   
2. Connected.
   
3. SQL> conn user2/oracle_4U
   
4. ERROR:
   
5. ORA-28002: the password will expire within 0 days
   
6. ......
   
7. 7/21/2018 9:40:45 AM之后
   
8. 
   
9. Connected.
   
10. SQL> conn user2/oracle_4U
    
11. ERROR:
    
12. ORA-28001: the password has expired
    
13. 
    
14. 
    
15. Changing password for user2
    
16. New password:
    

复制代码

1. select  u.username,
   
2.             u.password,
   
3.             u.account_status,
   
4.             u.lock_date,
   
5.             u.expiry_date,
   
6.             u.authentication_type,
   
7.             u.profile            
   
8. from dba_users u
   
9. where u.username  ='USER2';

复制代码

	USERNAME	PASSWORD	ACCOUNT_STATUS	LOCK_DATE	EXPIRY_DATE	AUTHENTICATION_TYPE	PROFILE
1	USER2		EXPIRED		7/21/2018 9:40:45 AM	PASSWORD	PROFILE1

然后用户在提示符下改密码：

1. New password: *********
   
2. Retype new password: *********
   
3. Password changed
   
4. Connected.
   
5. SQL> conn user2/oracle_4U
   
6. Connected.
   
7. SQL> conn user2/oracle_4U
   
8. Connected.
   
9. SQL>
   

复制代码

1. select  u.username,
   
2.             u.password,
   
3.             u.account_status,
   
4.             u.lock_date,
   
5.             u.expiry_date,
   
6.             u.authentication_type,
   
7.             u.profile            
   
8. from dba_users u
   
9. where u.username  ='USER2';

复制代码

	USERNAME	PASSWORD	ACCOUNT_STATUS	LOCK_DATE	EXPIRY_DATE	AUTHENTICATION_TYPE	PROFILE
1	USER2		OPEN		7/21/2018 9:50:34 AM	PASSWORD	PROFILE1

在7/21/2018 9:50:34 AM之前正常登录，之后 ORA-28002
还有1分钟，然后被逼着改密码：


------------------------------------------------------------------------
Password history: Checks the new password to ensure that the password is not reused for a specified amount of time or a specified number of password changes. （说明Oracle会记住旧的密码，不想让你使用旧的密码）These checks can be implemented by using one of the following:

PASSWORD_REUSE_TIME: Specifies that a user cannot reuse a password for a given number of days
PASSWORD_REUSE_MAX: Specifies the number of password changes that are required before the current password can be reused


If both password history parameters have a value of UNLIMITED, Oracle Database ignores both.（默认没限制，导致用户在grace期过以后，还是老是用同一个密码）。

If both parameters are set, password reuse is allowed—but only after meeting both conditions.（“与”的关系） The user must have changed the password the specified number of times, and the specified number of days must have passed since the old password was last used.

For example, the profile of user ALFRED has PASSWORD_REUSE_MAX set to 10 and PASSWORD_REUSE_TIME set to 30. User ALFRED cannot reuse a password until he has reset the password 10 times and until 30 days have passed since the password was last used.
If one parameter is set to a number and the other parameter is specified as UNLIMITED, then the user can never reuse a password.（书上本句是错的，正确的是只听一个指令的，同时图形界面也有bug）：以下是图形界面最的大的bug：

只好在命令行上改：

1.   alter profile profile1 limit
   
2.   PASSWORD_REUSE_TIME 0.010416667
   
3.    PASSWORD_REUSE_MAX 1;

复制代码

1. 
   
2. select  * from dba_profiles p
   
3.   where p.profile='PROFILE1' and p.resource_type <> 'KERNEL';

复制代码

	PROFILE	RESOURCE_NAME	RESOURCE_TYPE	LIMIT	COMMON
1	PROFILE1	FAILED_LOGIN_ATTEMPTS	PASSWORD	DEFAULT	NO
2	PROFILE1	PASSWORD_LIFE_TIME	PASSWORD	.0006	NO
3	PROFILE1	PASSWORD_REUSE_TIME	PASSWORD	.0104	NO
4	PROFILE1	PASSWORD_REUSE_MAX	PASSWORD	1	NO
5	PROFILE1	PASSWORD_VERIFY_FUNCTION	PASSWORD	DEFAULT	NO
6	PROFILE1	PASSWORD_LOCK_TIME	PASSWORD	DEFAULT	NO
7	PROFILE1	PASSWORD_GRACE_TIME	PASSWORD	.0006	NO

经过一次不同的“改”，如果这时时间不够，仍然无法重用特别喜欢的旧密码：

1. SQL> alter user user2 identified by oracle_4U ;
   
2. alter user user2 identified by oracle_4U
   
3. *
   
4. ERROR at line 1:
   
5. ORA-28007: the password cannot be reused
   

复制代码

通过等待，能解决这个问题，（等过15分钟）:

1. SQL> alter user user2 identified by oracle_4U ;
   
2. alter user user2 identified by oracle_4U
   
3. *
   
4. ERROR at line 1:
   
5. ORA-28007: the password cannot be reused
   
6. ......
   
7. 等过15分钟
   
8. SQL> alter user user2 identified by oracle_4U ;
   
9. 
   
10. User altered.
    
11. 
    
12. SQL>
    

复制代码

-------------------------------------------------密码复杂性函数：

[oracle@station90 admin]$ ls -l `pwd`/utlpwdmg.sql
-rw-r--r-- 1 oracle oinstall 12543 11月  7 2013 /u01/app/oracle/product/12.1.0/dbhome_2/rdbms/admin/utlpwdmg.sql




以下关于角色：

1. select  u.username,
   
2.             u.password,
   
3.             u.account_status,
   
4.             u.lock_date,
   
5.             u.expiry_date,
   
6.             u.authentication_type            
   
7. from dba_users u
   
8. where u.username like 'USER%' or
   
9.             u.username='OPS$ORACLE';
   
10.             
    
11. alter user user2 password expire;   
    
12. 
    
13. select  u.name, u.password  from user$ u ;      
    
14. 
    
15. revoke select any table from hr;
    
16. 
    
17. grant select_catalog_role to hr;
    
18. 
    
19. revoke select_catalog_role from hr;
    
20. 
    
21. select  * from dba_role_privs rp
    
22.   where rp.grantee='HR';
    
23.   
    
24.   create role role1 identified by oracle_4U;
    
25.   
    
26.   grant role1 to hr;
    
27.   
    
28.   alter user hr default role none;

复制代码

1. SQL> conn hr/oracle_4U
   
2. Connected.
   
3. SQL>  select  password  from dba_users;   
   
4. select  password  from dba_users
   
5.                         *
   
6. ERROR at line 1:
   
7. ORA-00942: table or view does not exist
   
8. 
   
9. 
   
10. SQL> conn hr/oracle_4U
    
11. Connected.
    
12. SQL> select * from session_roles;
    
13. 
    
14. ROLE
    
15. --------------------------------------------------------------------------------
    
16. RESOURCE
    
17. 
    
18. SQL> conn hr/oracle_4U
    
19. Connected.
    
20. SQL> select * from session_roles;
    
21. 
    
22. no rows selected
    
23. 
    
24. SQL> set role resource;
    
25. 
    
26. Role set.
    
27. 
    
28. SQL> select * from session_roles;
    
29. 
    
30. ROLE
    
31. --------------------------------------------------------------------------------
    
32. RESOURCE
    
33. 
    
34. SQL> set role resource,role1;
    
35. set role resource,role1
    
36. *
    
37. ERROR at line 1:
    
38. ORA-01979: missing or invalid password for role 'ROLE1'
    
39. 
    
40. 
    
41. SQL> set role resource,role1 identified by oracle_4U;
    
42. 
    
43. Role set.
    
44. 
    
45. SQL> select * from session_roles;
    
46. 
    
47. ROLE
    
48. --------------------------------------------------------------------------------
    
49. RESOURCE
    
50. ROLE1
    
51. 
    
52. SQL>
    

复制代码