设为首页收藏本站

Botang唐波's Oracle Station

查看: 198|回复: 0

活动第40/41次(2018-07-18星期三,2018-07-21星期六上午)

[复制链接]

731

主题

1102

帖子

8002

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
8002
发表于 2018-7-18 20:06:54 | 显示全部楼层 |阅读模式
查询段头块(放的本块最重要的元数据:位图或者freelist):
  1. select  s.header_block
  2.   from dba_segments s where s.tablespace_name='EXAMPLE'
  3.   and s.segment_name='EMPLOYEES';
复制代码
   HEADER_BLOCK
1202
查询段的第一个块:
  1. select e.block_id
  2. from dba_extents e
  3.   where e.segment_name='EMPLOYEES';
复制代码
   BLOCK_ID
1200

a.png

b.png

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

sys的两重身份:
  1. [oracle@station90 ~]$ sqlplus /nolog

  2. SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 18 20:21:43 2018

  3. Copyright (c) 1982, 2014, Oracle.  All rights reserved.

  4. SQL> conn / as sysdba
  5. Connected.
  6. SQL> show user
  7. USER is "SYS"
  8. SQL> conn / as sysoper
  9. Connected.
  10. SQL> show user
  11. USER is "PUBLIC"
  12. SQL>
复制代码

举个外部验证的例子:
  1. SQL> show parameter authen

  2. NAME                                     TYPE         VALUE
  3. ------------------------------------ ----------- ------------------------------
  4. os_authent_prefix                     string         ops$
  5. remote_os_authent                     boolean         FALSE
  6. SQL> create user ops$oracle identified externally ;

  7. User created.

  8. SQL>
复制代码
  1. SQL> grant connect to ops$oracle;

  2. Grant succeeded.

  3. 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. select  * from dba_sys_privs sp
  3. where sp.grantee='OPS$ORACLE';
复制代码
   GRANTEEPRIVILEGEADMIN_OPTIONCOMMON
1OPS$ORACLECREATE TABLENONO

  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. SQL>
  11. SQL>
  12. SQL> create table t_ops( a  number );

  13. Table created.

  14. SQL>
复制代码
现在如果要插入数据,就会报错:
  1. SQL> create table t_ops( a  number );

  2. Table created.

  3. SQL> insert into t_ops values (1) ;
  4. insert into t_ops values (1)
  5.             *
  6. ERROR at line 1:
  7. ORA-01950: no privileges on tablespace 'USERS'


复制代码
d.png

  1. select  * from dba_ts_quotas;
复制代码
   TABLESPACE_NAMEUSERNAMEBYTESMAX_BYTESBLOCKSMAX_BLOCKSDROPPED
1SYSAUXAUDSYS1507328-1184-1NO
2SYSAUXGSMADMIN_INTERNAL144179210485760017612800NO
3SYSAUXAPPQOSSYS0-10-1NO
4SYSAUXOLAPSYS0-10-1NO
5SYSAUXFLOWS_FILES0-10-1NO
6EXAMPLEOE10420224-11272-1NO

  1. alter user ops$oracle quota 64K on users;
复制代码
  1. select  * from dba_ts_quotas;
复制代码
   TABLESPACE_NAMEUSERNAMEBYTESMAX_BYTESBLOCKSMAX_BLOCKSDROPPED
1SYSAUXFLOWS_FILES0-10-1NO
2SYSAUXOLAPSYS0-10-1NO
3SYSAUXAPPQOSSYS0-10-1NO
4SYSAUXGSMADMIN_INTERNAL144179210485760017612800NO
5SYSAUXAUDSYS1507328-1184-1NO
6USERSOPS$ORACLE06553608NO
7EXAMPLEOE10420224-11272-1NO

  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. SQL> insert into t_ops values (1) ;

  7. 1 row created.

  8. SQL> commit;

  9. Commit complete.

  10. SQL>
复制代码
如果回收quota,还能插入数据吗?
  1. alter user ops$oracle quota 0 on users;
复制代码
  1. SQL> insert into t_ops values (2) ;

  2. 1 row created.

  3. SQL> commit;

  4. Commit complete.

复制代码

关于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. SQL> select  count(*) from t_ops;

  15.   COUNT(*)
  16. ----------
  17.          2

  18. SQL> begin
  19.   2   for i in 3..2000
  20.   3   loop
  21.   4     insert into t_ops values (i);
  22.   5   end loop;
  23.   6   commit;
  24.   7  end;
  25.   8  /

  26. PL/SQL procedure successfully completed.

  27. SQL> select  count(*) from t_ops;

  28.   COUNT(*)
  29. ----------
  30.       2000

  31. SQL>
复制代码
其他建用户的方法:
  1. grant connect to user1 identified by oracle_4U;

  2. grant create session to user2 identified by oracle_4U;

  3. select  * from system_privilege_map spm
  4. where spm.name='CREATE SESSION';
复制代码
   PRIVILEGENAMEPROPERTY
1-5CREATE SESSION0

  1. select * from role_sys_privs rsp
  2. where rsp.role='CONNECT';
复制代码
   ROLEPRIVILEGEADMIN_OPTIONCOMMON
1CONNECTSET CONTAINERNOYES
2CONNECTCREATE SESSIONNOYES

--------------------------------------------------------之后经常会使用以下的语句查询一个账户的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';
复制代码
   USERNAMEPASSWORDACCOUNT_STATUSLOCK_DATEEXPIRY_DATEAUTHENTICATION_TYPE
1OPS$ORACLEEXTERNALOPEN EXTERNAL
2USER1 OPEN 1/14/2019 9:01:20 PMPASSWORD
3USER2 OPEN 1/14/2019 9:01:51 PMPASSWORD

------------------------------------------------以下研究ORA-28000:
  1. alter user user1 account lock;         
复制代码
  1. SQL> conn user1/oracle_4U
  2. ERROR:
  3. ORA-28000: the account is locked
复制代码
user1账号永久被锁定。


   USERNAMEPASSWORDACCOUNT_STATUSLOCK_DATEEXPIRY_DATEAUTHENTICATION_TYPE
1OPS$ORACLEEXTERNALOPEN EXTERNAL
2USER1 LOCKED7/18/2018 9:14:00 PM1/14/2019 9:01:20 PMPASSWORD
3USER2 OPEN 1/14/2019 9:01:51 PMPASSWORD


--------------------------------------------------以下研究ORA-28001:
  1. alter user user2 password expire;        
复制代码
  1. SQL> conn user2/oracle_4U
  2. ERROR:
  3. ORA-28001: the password has expired


  4. Changing password for user2
  5. New password:
复制代码

   USERNAMEPASSWORDACCOUNT_STATUSLOCK_DATEEXPIRY_DATEAUTHENTICATION_TYPE
1OPS$ORACLEEXTERNALOPEN EXTERNAL
2USER1 LOCKED7/18/2018 9:14:00 PM1/14/2019 9:01:20 PMPASSWORD
3USER2 EXPIRED
7/18/2018 9:19:32 PMPASSWORD

他自己就能解决:
  1. Changing password for user2
  2. New password: *********
  3. Retype new password: *********
  4. Password changed
  5. Connected.
  6. SQL>
复制代码
   USERNAMEPASSWORDACCOUNT_STATUSLOCK_DATEEXPIRY_DATEAUTHENTICATION_TYPE
1OPS$ORACLEEXTERNALOPEN EXTERNAL
2USER1 LOCKED7/18/2018 9:14:00 PM1/14/2019 9:01:20 PMPASSWORD
3USER2 OPEN 1/14/2019 9:21:24 PMPASSWORD

-------------------------------------------------------------------------以下研究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. alter user user2 profile profile1;

  11. select  * from dba_profiles p
  12.   where p.profile='PROFILE1' and p.resource_type <> 'KERNEL';
复制代码
   PROFILERESOURCE_NAMERESOURCE_TYPELIMITCOMMON
1PROFILE1FAILED_LOGIN_ATTEMPTSPASSWORD1NO
2PROFILE1PASSWORD_LIFE_TIMEPASSWORDDEFAULTNO
3PROFILE1PASSWORD_REUSE_TIMEPASSWORDDEFAULTNO
4PROFILE1PASSWORD_REUSE_MAXPASSWORDDEFAULTNO
5PROFILE1PASSWORD_VERIFY_FUNCTIONPASSWORDDEFAULTNO
6PROFILE1PASSWORD_LOCK_TIMEPASSWORD.0006NO
7PROFILE1PASSWORD_GRACE_TIMEPASSWORDDEFAULTNO

试错一次密码:
  1. [oracle@station90 ~]$ sqlplus /nolog

  2. SQL*Plus: Release 12.1.0.2.0 Production on Sat Jul 21 09:23:27 2018

  3. Copyright (c) 1982, 2014, Oracle.  All rights reserved.

  4. SQL> conn user2/oracle_4U
  5. Connected.
  6. SQL> conn user2/oracle_5U
  7. ERROR:
  8. ORA-01017: invalid username/password; logon denied


  9. Warning: You are no longer connected to ORACLE.
  10. SQL> conn user2/oracle_4U
  11. ERROR:
  12. ORA-28000: the account is locked


  13. SQL> conn user2/oracle_4U
  14. Connected.
  15. 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';
复制代码
   USERNAMEPASSWORDACCOUNT_STATUSLOCK_DATEEXPIRY_DATEAUTHENTICATION_TYPEPROFILE
1USER2 LOCKED(TIMED)7/21/2018 9:25:21 AM1/14/2019 9:21:24 PMPASSWORDPROFILE1

ora-28002:
a.png

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

刚刚改完口令生命期的时候,马上查看用户:
  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';
复制代码
   USERNAMEPASSWORDACCOUNT_STATUSLOCK_DATEEXPIRY_DATEAUTHENTICATION_TYPEPROFILE
1USER2 OPEN 7/18/2018 9:22:24 PMPASSWORDPROFILE1

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

  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';
复制代码
   USERNAMEPASSWORDACCOUNT_STATUSLOCK_DATEEXPIRY_DATEAUTHENTICATION_TYPEPROFILE
1USER2 EXPIRED(GRACE) 7/21/2018 9:40:45 AMPASSWORDPROFILE1


  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. Connected.
  9. SQL> conn user2/oracle_4U
  10. ERROR:
  11. ORA-28001: the password has expired


  12. Changing password for user2
  13. 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';
复制代码
   USERNAMEPASSWORDACCOUNT_STATUSLOCK_DATEEXPIRY_DATEAUTHENTICATION_TYPEPROFILE
1USER2 EXPIRED 7/21/2018 9:40:45 AMPASSWORDPROFILE1

然后用户在提示符下改密码:
  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';
复制代码
   USERNAMEPASSWORDACCOUNT_STATUSLOCK_DATEEXPIRY_DATEAUTHENTICATION_TYPEPROFILE
1USER2 OPEN 7/21/2018 9:50:34 AMPASSWORDPROFILE1
在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:
bug.png
只好在命令行上改:
  1.   alter profile profile1 limit
  2.   PASSWORD_REUSE_TIME 0.010416667
  3.    PASSWORD_REUSE_MAX 1;
复制代码

  1. select  * from dba_profiles p
  2.   where p.profile='PROFILE1' and p.resource_type <> 'KERNEL';
复制代码
   PROFILERESOURCE_NAMERESOURCE_TYPELIMITCOMMON
1PROFILE1FAILED_LOGIN_ATTEMPTSPASSWORDDEFAULTNO
2PROFILE1PASSWORD_LIFE_TIMEPASSWORD.0006NO
3PROFILE1PASSWORD_REUSE_TIMEPASSWORD.0104NO
4PROFILE1PASSWORD_REUSE_MAXPASSWORD1NO
5PROFILE1PASSWORD_VERIFY_FUNCTIONPASSWORDDEFAULTNO
6PROFILE1PASSWORD_LOCK_TIMEPASSWORDDEFAULTNO
7PROFILE1PASSWORD_GRACE_TIMEPASSWORD.0006NO



经过一次不同的“改”,如果这时时间不够,仍然无法重用特别喜欢的旧密码:
  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. User altered.

  10. 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. select  u.name, u.password  from user$ u ;      

  13. revoke select any table from hr;

  14. grant select_catalog_role to hr;

  15. revoke select_catalog_role from hr;

  16. select  * from dba_role_privs rp
  17.   where rp.grantee='HR';
  18.   
  19.   create role role1 identified by oracle_4U;
  20.   
  21.   grant role1 to hr;
  22.   
  23.   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. SQL> conn hr/oracle_4U
  9. Connected.
  10. SQL> select * from session_roles;

  11. ROLE
  12. --------------------------------------------------------------------------------
  13. RESOURCE

  14. SQL> conn hr/oracle_4U
  15. Connected.
  16. SQL> select * from session_roles;

  17. no rows selected

  18. SQL> set role resource;

  19. Role set.

  20. SQL> select * from session_roles;

  21. ROLE
  22. --------------------------------------------------------------------------------
  23. RESOURCE

  24. SQL> set role resource,role1;
  25. set role resource,role1
  26. *
  27. ERROR at line 1:
  28. ORA-01979: missing or invalid password for role 'ROLE1'


  29. SQL> set role resource,role1 identified by oracle_4U;

  30. Role set.

  31. SQL> select * from session_roles;

  32. ROLE
  33. --------------------------------------------------------------------------------
  34. RESOURCE
  35. ROLE1

  36. SQL>
复制代码









回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Botang唐波's Oracle Station   

GMT+8, 2018-10-22 03:22 , Processed in 0.177055 second(s), 27 queries .

快速回复 返回顶部 返回列表