加载中…Oracle12c中的sec_case_sensitive_logon设置要谨慎
(2017-10-12 09:24:55)
标签：
sec_case_sensitive_loracle12c |
分类： Oracle12c |
1、sec_case_sensitive_logon参数类型
SQL> select distinct
ISSYS_MODIFIABLE from v$parameter;
ISSYS_MOD
---------
IMMEDIATE
FALSE
DEFERRED
只有FALSE是静态参数，需要重启数据库实例才会生效，其他两个都是动态参数，修改后立即生效。
SQL> select name,
ISSYS_MODIFIABLE from v$parameter where
name='sec_case_sensitive_logon';
NAME ISSYS_MOD
------------------------------
---------
sec_case_sensitive_logon IMMEDIATE
可见sec_case_sensitive_logon
是动态参数，修改后立即生效。
2、修改sec_case_sensitive_logon
SQL> show parameter
case
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
sec_case_sensitive_logon boolean TRUE
--12c默认是TURE。
此时数据字典里记录的密码信息。
SQL> select username,password
from dba_users where username='SYSTEM';
USERNAME PASSWORD
---------------
------------------------------
SYSTEM
SQL> select name,password,spare4
from user$ where name='SYSTEM';
NAME PASSWORD
SPARE4
------------------------------
------------------------------
SYSTEM
S:1B5FF0F9E6B9BF2A5DC84D8B82B69D46D34D1D4B8C9014D2FFB6DF8D4F51;T:9FC6D7AAC93ED3571017D30114B1E6C41410686C06C4F0B99E78336A9AA91684A9E9FD4513806D29D07C5E678D7793DC87D29423141BA1AE853BC979C9853E13FE8EB48DB46E7EB21A41A79FB935CB68
可见dba_users和user$中PASSWORD都为空，只有user$.spare4记录密码加密信息。
如果此时修改为FALSE，那么之前已创建的用户的密码将失效，提示invalid username
or password。
SQL> alter system set
sec_case_sensitive_logon=FALSE;
System altered.
Elapsed: 00:00:00.01
sqlplus system
SQL*Plus: Release 12.2.0.1.0
Production on Wed Oct 11 16:00:36 2017
Copyright (c) 1982, 2016,
Oracle. All rights
reserved.
Enter password:
ERROR:
ORA-01017: 鐢ㄦ埛鍚鍙ｄ护鏃犳晥;
鐧诲綍琚嫆缁
Enter user-name:
ERROR:
ORA-01017:
invalid username/password; logon denied
Enter user-name:
3、修改sec_case_sensitive_logon为FALSE测试密码信息
SQL> create user test identified
by oracle default tablespace users quota unlimited on users profile
DEFAULT;
create user test identified by
oracle default tablespace users quota unlimited on users profile
DEFAULT
ERROR at line 1:
ORA-65096: invalid common user or
role name
Elapsed: 00:00:00.00
SQL> !oerr ora 65096
65096, 00000, "invalid common user
or role name"
//
*Cause: An attempt was made to
create a common user or role with a name
// that
was not valid for common users or roles. In addition to
the
// usual
rules for user and role names, common user and role
names
// must
consist only of ASCII characters, and must contain the
prefix
// specified
in common_user_prefix parameter.
// *Action: Specify a valid common
user or role name.
//
SQL> show parameter
common_user_prefix
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
common_user_prefix string C##
SQL> create user C##test
identified by oracle default tablespace users quota unlimited on
users profile DEFAULT;
User created.
Elapsed: 00:00:00.20
SQL> select username,password
from dba_users where username='C##TEST';
USERNAME PASSWORD
---------------
------------------------------
C##TEST
1 row selected.
Elapsed: 00:00:00.05
SQL> select name,password,spare4
from user$ where name='C##TEST';
NAME PASSWORD
SPARE4
------------------------------
------------------------------
C##TEST
S:9B00D915B8A039E4E41F4834149EF3610221251C0952508C55C078B74C47;T:DA07F279C041F4B11242281F3576B7AC392CEEC60CE55AE9556A441A0F0751EF39E13F7FCF18B687FEC3AF75E52DF73FBF58B671576070EECFD30F2667831ACC7B63436C6ADDD52575AA0CA6CE2B7281
如果要重置密码的话，执行如下命令：
alter user c#test identified by
values 'user$.spare4';
喜欢
0
赠金笔