活动第40/41次（2018-07-18星期三，2018-07-21星期六上午）
查询段头块（放的本块最重要的元数据：位图或者freelist）:selects.header_block
from dba_segments s where s.tablespace_name='EXAMPLE'
and s.segment_name='EMPLOYEES';
HEADER_BLOCK
1202
查询段的第一个块:
select e.block_id
from dba_extents e
where e.segment_name='EMPLOYEES';
BLOCK_ID
1200
------------------------------------------------------------------------------用户账号的4个方面
1. Authentication2. Account 3. Password 4. Session
sys的两重身份：
$ sqlplus /nolog
SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 18 20:21:43 2018
Copyright (c) 1982, 2014, Oracle.All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> conn / as sysoper
Connected.
SQL> show user
USER is "PUBLIC"
SQL>
举个外部验证的例子：
SQL> show parameter authen
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix string ops$
remote_os_authent boolean FALSE
SQL> create user ops$oracle identified externally ;
User created.
SQL>
SQL> grant connect to ops$oracle;
Grant succeeded.
SQL>
SQL> conn /
Connected.
SQL> show user
USER is "OPS$ORACLE"
SQL>
SQL> conn /
Connected.
SQL> show user
USER is "OPS$ORACLE"
SQL> create table t_ops( anumber );
create table t_ops( anumber )
*
ERROR at line 1:
ORA-01031: insufficient privileges
SYS授权：
grant create table to ops$oracle;
select* from dba_sys_privs sp
where sp.grantee='OPS$ORACLE';
GRANTEEPRIVILEGEADMIN_OPTIONCOMMON
1OPS$ORACLECREATE TABLENONO
SQL> conn /
Connected.
SQL> show user
USER is "OPS$ORACLE"
SQL> create table t_ops( anumber );
create table t_ops( anumber )
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL>
SQL>
SQL> create table t_ops( anumber );
Table created.
SQL>
现在如果要插入数据，就会报错：
SQL> create table t_ops( anumber );
Table created.
SQL> insert into t_ops values (1) ;
insert into t_ops values (1)
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'
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
alter user ops$oracle quota 64K on users;
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
SQL> insert into t_ops values (1) ;
insert into t_ops values (1)
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'
SQL> insert into t_ops values (1) ;
1 row created.
SQL> commit;
Commit complete.
SQL>
如果回收quota，还能插入数据吗？
alter user ops$oracle quota 0 on users;
SQL> insert into t_ops values (2) ;
1 row created.
SQL> commit;
Commit complete.
关于quota的使用的简单的实验而复杂的原理：
SQL> begin
2 for i in 3..4000
3 loop
4 insert into t_ops values (i);
5 end loop;
6 commit;
7end;
8/
begin
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USERS'
ORA-06512: at line 4
SQL> selectcount(*) from t_ops;
COUNT(*)
----------
2
SQL> begin
2 for i in 3..2000
3 loop
4 insert into t_ops values (i);
5 end loop;
6 commit;
7end;
8/
PL/SQL procedure successfully completed.
SQL> selectcount(*) from t_ops;
COUNT(*)
----------
2000
SQL>
其他建用户的方法：
grant connect to user1 identified by oracle_4U;
grant create session to user2 identified by oracle_4U;
select* from system_privilege_map spm
where spm.name='CREATE SESSION';
PRIVILEGENAMEPROPERTY
1-5CREATE SESSION0
select * from role_sys_privs rsp
where rsp.role='CONNECT';
ROLEPRIVILEGEADMIN_OPTIONCOMMON
1CONNECTSET CONTAINERNOYES
2CONNECTCREATE SESSIONNOYES
--------------------------------------------------------之后经常会使用以下的语句查询一个账户的Account和Athentication状态：
selectu.username,
u.password,
u.account_status,
u.lock_date,
u.expiry_date,
u.authentication_type
from dba_users u
where u.username like 'USER%' or
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：
alter user user1 account lock;
SQL> conn user1/oracle_4U
ERROR:
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：
alter user user2 password expire;
SQL> conn user2/oracle_4U
ERROR:
ORA-28001: the password has expired
Changing password for user2
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
他自己就能解决：
Changing password for user2
New password: *********
Retype new password: *********
Password changed
Connected.
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：
CREATE PROFILE "PROFILE1" LIMIT CPU_PER_SESSION DEFAULT
CPU_PER_CALL DEFAULT
CONNECT_TIME DEFAULT
IDLE_TIME DEFAULT
SESSIONS_PER_USER DEFAULT
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL DEFAULT
PRIVATE_SGA DEFAULT
COMPOSITE_LIMIT DEFAULT
PASSWORD_LIFE_TIME DEFAULT
PASSWORD_GRACE_TIME DEFAULT
PASSWORD_REUSE_MAX DEFAULT
PASSWORD_REUSE_TIME DEFAULT
PASSWORD_LOCK_TIME 0.000694444
FAILED_LOGIN_ATTEMPTS 1
PASSWORD_VERIFY_FUNCTION DEFAULT
selectu.username,
u.password,
u.account_status,
u.lock_date,
u.expiry_date,
u.authentication_type,
u.profile
from dba_users u
where u.username='USER2';
alter user user2 profile profile1;
select* from dba_profiles p
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
试错一次密码：
$ sqlplus /nolog
SQL*Plus: Release 12.1.0.2.0 Production on Sat Jul 21 09:23:27 2018
Copyright (c) 1982, 2014, Oracle.All rights reserved.
SQL> conn user2/oracle_4U
Connected.
SQL> conn user2/oracle_5U
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn user2/oracle_4U
ERROR:
ORA-28000: the account is locked
SQL> conn user2/oracle_4U
Connected.
SQL>
selectu.username,
u.password,
u.account_status,
u.lock_date,
u.expiry_date,
u.authentication_type,
u.profile
from dba_users u
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：
上面图中的第一个lock和第2个lock不是同一个意思，第1个lock 实际上是“过期”。
刚刚改完口令生命期的时候，马上查看用户：
selectu.username,
u.password,
u.account_status,
u.lock_date,
u.expiry_date,
u.authentication_type,
u.profile
from dba_users u
where u.username='USER2';
USERNAMEPASSWORDACCOUNT_STATUSLOCK_DATEEXPIRY_DATEAUTHENTICATION_TYPEPROFILE
1USER2 OPEN 7/18/2018 9:22:24 PMPASSWORDPROFILE1
------------------------------
selectu.username,
u.password,
u.account_status,
u.lock_date,
u.expiry_date,
u.authentication_type,
u.profile
from dba_users u
where u.username='USER2';
USERNAMEPASSWORDACCOUNT_STATUSLOCK_DATEEXPIRY_DATEAUTHENTICATION_TYPEPROFILE
1USER2 EXPIRED(GRACE) 7/21/2018 9:40:45 AMPASSWORDPROFILE1
SQL> conn user2/oracle_4U
Connected.
SQL> conn user2/oracle_4U
ERROR:
ORA-28002: the password will expire within 0 days
......
7/21/2018 9:40:45 AM之后
Connected.
SQL> conn user2/oracle_4U
ERROR:
ORA-28001: the password has expired
Changing password for user2
New password:
selectu.username,
u.password,
u.account_status,
u.lock_date,
u.expiry_date,
u.authentication_type,
u.profile
from dba_users u
where u.username='USER2';
USERNAMEPASSWORDACCOUNT_STATUSLOCK_DATEEXPIRY_DATEAUTHENTICATION_TYPEPROFILE
1USER2 EXPIRED 7/21/2018 9:40:45 AMPASSWORDPROFILE1
然后用户在提示符下改密码：
New password: *********
Retype new password: *********
Password changed
Connected.
SQL> conn user2/oracle_4U
Connected.
SQL> conn user2/oracle_4U
Connected.
SQL>
selectu.username,
u.password,
u.account_status,
u.lock_date,
u.expiry_date,
u.authentication_type,
u.profile
from dba_users u
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：
只好在命令行上改：
alter profile profile1 limit
PASSWORD_REUSE_TIME 0.010416667
PASSWORD_REUSE_MAX 1;
select* from dba_profiles p
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
经过一次不同的“改”，如果这时时间不够，仍然无法重用特别喜欢的旧密码：
SQL> alter user user2 identified by oracle_4U ;
alter user user2 identified by oracle_4U
*
ERROR at line 1:
ORA-28007: the password cannot be reused
通过等待，能解决这个问题，（等过15分钟）:
SQL> alter user user2 identified by oracle_4U ;
alter user user2 identified by oracle_4U
*
ERROR at line 1:
ORA-28007: the password cannot be reused
......
等过15分钟
SQL> alter user user2 identified by oracle_4U ;
User altered.
SQL>
-------------------------------------------------密码复杂性函数：
$ 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
以下关于角色：
selectu.username,
u.password,
u.account_status,
u.lock_date,
u.expiry_date,
u.authentication_type
from dba_users u
where u.username like 'USER%' or
u.username='OPS$ORACLE';
alter user user2 password expire;
selectu.name, u.passwordfrom user$ u ;
revoke select any table from hr;
grant select_catalog_role to hr;
revoke select_catalog_role from hr;
select* from dba_role_privs rp
where rp.grantee='HR';
create role role1 identified by oracle_4U;
grant role1 to hr;
alter user hr default role none;
SQL> conn hr/oracle_4U
Connected.
SQL>selectpasswordfrom dba_users;
selectpasswordfrom dba_users
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> conn hr/oracle_4U
Connected.
SQL> select * from session_roles;
ROLE
--------------------------------------------------------------------------------
RESOURCE
SQL> conn hr/oracle_4U
Connected.
SQL> select * from session_roles;
no rows selected
SQL> set role resource;
Role set.
SQL> select * from session_roles;
ROLE
--------------------------------------------------------------------------------
RESOURCE
SQL> set role resource,role1;
set role resource,role1
*
ERROR at line 1:
ORA-01979: missing or invalid password for role 'ROLE1'
SQL> set role resource,role1 identified by oracle_4U;
Role set.
SQL> select * from session_roles;
ROLE
--------------------------------------------------------------------------------
RESOURCE
ROLE1
SQL>
页:
[1]