Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle user lock how to know what causes     - Windows 7 hard disk to install Ubuntu 15.04 (Linux)

- About Samba certification process and permissions (Linux)

- Kubernetes resolve application deployment model (Server)

- Applications Linux leap second problem caused (Linux)

- J2EE Example of Filter (Programming)

- Linux System Getting Started Learning: Linux how to install 7zip (Linux)

- CentOS install video converter FFmpeg and cutting tools segmenter (Linux)

- How to upgrade to Ubuntu 14.04 Ubuntu 14.10 (Linux)

- Linux upgrade Glibc (Linux)

- Retro terminal in Linux (Linux)

- Gentoo: startx problem appears Failed to load module (Linux)

- Subquery Oracle study notes (Database)

- Easy to get hidden administrator account (Linux)

- Shell Script: create a function and specify a directory for download (Programming)

- Effect MongoDB plan cache (Database)

- Vagrant Getting Start (Linux)

- Novice Advanced Ubuntu7.10 configured IP address (Linux)

- Linux command Detailed chpasswd bulk edit user password (Linux)

- Python pickle module for simple use notes (Programming)

- Ubuntu 14.10 used ifconfig commands to manage your network configuration (Linux)

 
         
  Oracle user lock how to know what causes
     
  Add Date : 2018-11-21      
         
         
         
  Problem
Oracle database user is locked, and how to view what causes it to be locked

in conclusion
1, dba_users by the underlying tables user $, profile $, profname $ correlation table structure, of course, ts $
 2, dba_users and account lockout or expiration-related lists: created, account_status, lock_date, expiry_date, profile
 3, in the end is how to analyze specific dba_profiles in which the parameters for the user to lock the reason, the meaning of the data in conjunction with these columns, as well as related records dba_profiles
  One by one excluded

4, find the profile to the meaning of methods: in oracle sql rererence looking to create profile
  Or find a resource plan from the administrator guide, you can also navigate to create profile to

5, profile plainly, is to control how resources are used, the details you can refer to the official manual

Test
--oracle version
 SQL> select * from v $ version where rownum = 1;


 BANNER
 -------------------------------------------------- ------------------------------
 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

- User dictionary table
SQL> desc dba_users;
  Name Null? Type
  ----------------------------------------- ---------- ---------------------------
 USERNAME NOT NULL VARCHAR2 (30)
  USER_ID NOT NULL NUMBER
  PASSWORD VARCHAR2 (30)
  ACCOUNT_STATUS NOT NULL VARCHAR2 (32)
  LOCK_DATE DATE - account lockout time
 EXPIRY_DATE DATE
  DEFAULT_TABLESPACE NOT NULL VARCHAR2 (30)
  TEMPORARY_TABLESPACE NOT NULL VARCHAR2 (30)
  CREATED NOT NULL DATE
  PROFILE NOT NULL VARCHAR2 (30)
  INITIAL_RSRC_CONSUMER_GROUP VARCHAR2 (30)
  EXTERNAL_NAME VARCHAR2 (4000)
  PASSWORD_VERSIONS VARCHAR2 (8)
  EDITIONS_ENABLED VARCHAR2 (1)
  AUTHENTICATION_TYPE VARCHAR2 (8)

 SQL> create user test_user identified by system account unlock;

 User created.

Visible user default profile created by default
 SQL> select username, password, account_status, lock_date, profile from dba_users where lower (username) = 'test_user';

 USERNAME PASSWORD ACCOUNT_ST LOCK_DATE PROFILE
 -------------------- ------------------------------ ---------- ------------ ---------------------------- --------------------------------
 TEST_USER OPEN DEFAULT

See default profile
About profile meaning, you can look up the official manual that is known
SQL> select profile, resource_name, resource_type, limit from dba_profiles where profile = 'DEFAULT';


 PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT
 ------------------------------ -------------------- ------------------------------ ---------------- ---- -------------------------------------------------- --------------------------
 DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
 DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
 DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
 DEFAULT CPU_PER_CALL KERNEL UNLIMITED
 DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
 DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED
 DEFAULT IDLE_TIME KERNEL UNLIMITED
 DEFAULT CONNECT_TIME KERNEL UNLIMITED
 DEFAULT PRIVATE_SGA KERNEL UNLIMITED
 DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10 - note the records associated with password
DEFAULT PASSWORD_LIFE_TIME PASSWORD 180


 PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT
 ------------------------------ -------------------- ------------------------------ ---------------- ---- -------------------------------------------------- --------------------------
 DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED
 DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
 DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
 DEFAULT PASSWORD_LOCK_TIME PASSWORD 1
 DEFAULT PASSWORD_GRACE_TIME PASSWORD 7


 16 rows selected.


In testing, several times in succession with an incorrect password, up to 10 times, the account locked
[Oracle @ seconary ~] $ sqlplus test_user / p1


 SQL * Plus: Release 11.2.0.1.0 Production on Mon Nov 16 03:54:36 2015


 Copyright (c) 1982, 2009, Oracle. All rights reserved.


 ERROR:
 ORA-01017: invalid username / password; logon denied


- In the middle like is slightly
Enter user-name:
 ERROR:
 ORA-01017: invalid username / password; logon denied

 


 SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL * Plus
 [Oracle @ seconary ~] $ sqlplus test_user / p1


 SQL * Plus: Release 11.2.0.1.0 Production on Mon Nov 16 03:54:48 2015


 Copyright (c) 1982, 2009, Oracle. All rights reserved.


 ERROR:
 ORA-28000: the account is locked


 account_status and lock_date has a value indicating Huan account set up, if you want to know why in the end was locked up, only to find the bottom of the associated base table
SQL> select username, password, account_status, lock_date, profile from dba_users where lower (username) = 'test_user';


 USERNAME PASSWORD ACCOUNT_STATUS LOCK_DATE PROFILE
 -------------------- -------------------- ---------- -------------------- ------------------- ----------- -------------------------------------------------
 TEST_USER LOCKED (TIMED) 2015-11-16 03:54:43 DEFAULT

 


From the definition of the underlying base table, the only user $ profile $ and I think the analysis is associated with the base table
SQL> set long 99999999
 SQL> set pagesize 300
 SQL> select view_name, text from dba_views where view_name = 'DBA_USERS';


 VIEW_NAME TEXT
 -------------------------------------------------- ---------- ---------------------------------------- ----------------------------------------
 DBA_USERS select u.name, u.user #,
                                                                    decode (u.password, 'GLOBAL', u.password,
                                                                                        'EXTERNAL', u.password,
                                                                                        NULL),
                                                                    m.status,
                                                                    decode (u.astatus, 4, u.ltime,
                                                                                      5, u.ltime,
                                                                                      6, u.ltime,
                                                                                      8, u.ltime,
                                                                                      9, u.ltime,
                                                                                      10, u.ltime, to_date (NULL)),
                                                                    decode (u.astatus,
                                                                            1, u.exptime,
                                                                            2, u.exptime,
                                                                            5, u.exptime,
                                                                            6, u.exptime,
                                                                            9, u.exptime,
                                                                            10, u.exptime,
                                                                            decode (u.ptime, '', to_date (NULL),
                                                                              decode (pr.limit #, 2147483647, to_date (NULL),
                                                                              decode (pr.limit #, 0,
                                                                                decode (dp.limit #, 2147483647, to_date (NULL), u.ptime +
                                                                                  dp.limit # / 86400),
                                                                                u.ptime + pr.limit # / 86400)))),
                                                                    dts.name, tts.name, u.ctime, p.name,
                                                                    nvl (cgm.consumer_group, 'DEFAULT_CONSUMER_GROUP'),
                                                                    u.ext_username,
                                                                    decode (length (u.password), 16, '10G', NULL) || NVL2 (u.spare4, '11G', NULL),
                                                                    decode (bitand (u.spare1, 16),
                                                                            16, 'Y',
                                                                                'N'),
                                                                    decode (u.password, 'GLOBAL', 'GLOBAL',
                                                                                        'EXTERNAL', 'EXTERNAL',
                                                                                        'PASSWORD')
                                                                    from sys.user $ u left outer join sys.resource_group_mapping $ cgm
                                                                          on (cgm.attribute = 'ORACLE_USER' and cgm.status = 'ACTIVE' and
                                                                              cgm.value = u.name),
                                                                          sys.ts $ dts, sys.ts $ tts, sys.profname $ p,
                                                                          sys.user_astatus_map m, sys.profile $ pr, sys.profile $ dp
                                                                    where u.datats # = dts.ts #
                                                                    and u.resource $ = p.profile #
                                                                    and u.tempts # = tts.ts #
                                                                    and u.astatus = m.status #
                                                                    and u.type # = 1
                                                                    and u.resource $ = pr.profile #
                                                                    and dp.profile # = 0
                                                                    and dp.type # = 1
                                                                    and dp.resource # = 1
                                                                    and pr.type # = 1
                                                                    and pr.resource # = 1
Look under the user $, only column resource $ is a column of our attention
create table user $ / * user table * /
 (User # number not null, / * user identifier number * /
  name varchar2 ( "M_IDEN") not null, / * name of user * /
                / * 0 = role, 1 = user, 2 = adjunct schema, 3 = schema synonym * /
  type # number not null,
  password varchar2 ( "M_IDEN"), / * encrypted password * /
  datats # number not null, / * default tablespace for permanent objects * /
  tempts # number not null, / * default tablespace for temporary tables * /
  ctime date not null, / * user account creation time * /
  ptime date, / * password change time * /
  exptime date, / * actual password expiration time * /
  ltime date, / * time when account is locked * /
  resource $ number not null, / * resource profile # * /
  audit $ varchar2 ( "S_OPFL"), / * user audit options * /
  defrole number not null, / * default role indicator: * /
                / * 0 = no roles, 1 = all roles granted, 2 = roles in defrole $ * /
  defgrp # number, / * default undo group * /
  defgrp_seq # number, / * global sequence number for the grp *
  spare varchar2 ( "M_IDEN"), / * reserved for future * /
  astatus number default 0 not null, / * status of the account * /
                / * 0x00 = 0 = Open * /
                / * 0x01 = 1 = Locked * /
                / * 0x02 = 2 = Expired * /
                / * 0x03 = 3 = Locked and Expired * /
                / * 0x10 = 16 = Password matches a default value * /
  lcount number default 0 not null, / * count of failed login attempts * /
  defschclass varchar2 ( "M_IDEN"), / * initial consumer group * /
  ext_username varchar2 ( "M_VCSZ"), / * external username * /
                              / * Also as base schema name for adjunct schemas * /
  spare1 number, / * used for schema level supp logging:. see ktscts.h * /
  spare2 number, / * used to store edition id for adjunct schemas * /
  spare3 number,
  spare4 varchar2 (1000),
  spare5 varchar2 (1000),
  spare6 date
 )
 cluster c_user # (user #)


According to the foregoing, user $ of resource $ is associated with the table profile $ profile #, so we should analyze profile $ table


VIEW_NAME TEXT
 -------------------------------------------------- ---------- ---------------------------------------- ----------------------------------------
 DBA_PROFILES select
                                                                n.name, m.name, - corresponding to the resource_name dba_profiles
                                                                decode (u.type #, 0, 'KERNEL', 1, 'PASSWORD', 'INVALID'),
                                                                decode (u.limit #,
                                                                        0, 'DEFAULT',
                                                                        2147483647, decode (u.resource #,
                                                                                          4, decode (u.type #,
                                                                                                    1, 'NULL', 'UNLIMITED'),
                                                                                          'UNLIMITED'),
                                                                        decode (u.resource #,
                                                                              4, decode (u.type #, 1, o.name, u.limit #), - the corresponding dba_profiles of resource_type
                                                                              decode (u.type #,
                                                                                      0, u.limit #,
                                                                                      decode (u.resource #,
                                                                                            1, trunc (u.limit # / 86400, 4),
                                                                                            2, trunc (u.limit # / 86400, 4),
                                                                                            5, trunc (u.limit # / 86400, 4),
                                                                                            6, trunc (u.limit # / 86400, 4),
                                                                                            u.limit #)))) - corresponding to the limit dba_profiles
                                                                from sys.profile $ u, sys.profname $ n, sys.resource_map m, sys.obj $ o
                                                                where u.resource # = m.resource #
                                                                and u.type # = m.type #
                                                                and o.obj # (+) = u.limit #
                                                                and n.profile # = u.profile #

 


 SQL> desc dba_profiles;
  Name Null? Type
  ----------------------------------------- ---------- ---------------------------
 PROFILE NOT NULL VARCHAR2 (30)
  RESOURCE_NAME NOT NULL VARCHAR2 (32)
  RESOURCE_TYPE VARCHAR2 (8)
  LIMIT VARCHAR2 (40)


The foregoing analysis under, as long as you focus on resource_type = 'password', and do not limit the unlimited and null you tube (the reason I do not), so I marked only a few records
SQL> select profile, resource_name, resource_type, limit from dba_profiles where profile = 'DEFAULT';


 PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT
 ------------------------------ -------------------- ------------------------------ ---------------- ---- -------------------------------------------------- --------------------------
 DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
 DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
 DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
 DEFAULT CPU_PER_CALL KERNEL UNLIMITED
 DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
 DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED
 DEFAULT IDLE_TIME KERNEL UNLIMITED
 DEFAULT CONNECT_TIME KERNEL UNLIMITED
 DEFAULT PRIVATE_SGA KERNEL UNLIMITED
 DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10 --- concerned by the investigation, which is the number of landing
DEFAULT PASSWORD_LIFE_TIME PASSWORD 180 - attention - this is the number of days the account is valid, that is, using the same account password


PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT
 ------------------------------ -------------------- ------------------------------ ---------------- ---- -------------------------------------------------- --------------------------
 DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED
 DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
 DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
 DEFAULT PASSWORD_LOCK_TIME PASSWORD 1 --- attention, show row after multiple failed login, the number of days the account locked, the default is 1 day
DEFAULT PASSWORD_GRACE_TIME PASSWORD 7 - attention, specify how many days, that if you do not change the password within a specified number of days will be unable to access the password prompt fail


 Focus on the meaning of the above parameters, see:


Oracle? Database SQL Language Reference
 11g Release 2 (11.2)
 Part Number E26088-03


It
create profile


That is visible when expiry_date account of expire, this column has been the data, as long as the user-created
SQL> select username, password, account_status, expiry_date, lock_date, profile from dba_users;


 USERNAME PASSWORD ACCOUNT_STATUS EXPIRY_DATE LOCK_DATE PROFILE
 -------------------- -------------------- ---------- -------------------- ------------------- ----------- -------- ------------------------------
 SCOTT OPEN 2016-03-06 06:58:08 DEFAULT
 TEST1 OPEN 2016-05-03 11:59:30 DEFAULT
 TEST_USER LOCKED (TIMED) 2016-05-14 03:45:06 2015-11-16 03:54:43 DEFAULT


But the password associated with the dba_profiles there are a few records, we know which record is causing the user to lock the reason it


 So the combination of user-created test can rule out time dba_users.created password_life_time, password_lock_time also be ruled out because it is the number of the account is locked tables, that have occurred in the operation of the account lockout after
 Thus, only failed_login_attempts and password_grace_time
Our investigation found the official manual, password_grace_time password_life_time general and with the use of that which is less than the former, that is, if the number of days specified in the latter does not modify the password, the user can not log will expire, so
 Only parameter failed_login_attempts
 SQL> select username, password, account_status, expiry_date, lock_date, profile from dba_users where lower (username) = 'test_user';


 USERNAME PASSWORD ACCOUNT_STATUS EXPIRY_DATE LOCK_DATE PROFILE
 -------------------- -------------------- ---------- -------------------- ------------------- ----------- -------- ------------------------------
 TEST_USER LOCKED (TIMED) 2016-05-14 03:45:06 2015-11-16 03:54:43 DEFAULT
     
         
         
         
  More:      
 
- iostat command Detailed (Linux)
- High-performance JavaScript loaded and executed (Programming)
- Ubuntu 14.04 Nvidia proprietary drivers for install two graphic cards (Linux)
- CentOS Linux firewall configuration and Close (Linux)
- Java to achieve local fileCopy (Programming)
- How to build Mono 3.4.0 / 3.4.1 on Windows (Linux)
- File SUID, SGID, Sticky property (Linux)
- Ubuntu upgrade to Linux Kernel 4.2.3 Kernel (Linux)
- 12 novice Linux command must learn (Linux)
- Inxi: Get Linux system and hardware information (Linux)
- Linux system started to learn: how to view the Linux thread of a process (Linux)
- Linux set to select the appropriate level of security of the network according to deployment (Linux)
- Binary Packages Golang (Linux)
- Ubuntu 14.04 install PostgreSQL 9.2 (Database)
- Computer security protection remove local and remote system log files (Linux)
- Zabbix configure DataGuard monitoring (Database)
- Shared directory settings between Linux and Linux (Linux)
- VMware Workstation virtual machine Ubuntu achieve shared with the host (Linux)
- JavaScript cross-browser event object library (Programming)
- To create a secure network firewall with iptables Under Linux (Linux)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.