오라클 19c sys 유저도 lock이 걸릴까?

OS 환경 : Oracle Linux 7.6 (64bit)

 

DB 환경 : Oracle Database 19.3.0.0

 

방법 : 오라클 19c sys 유저도 lock이 걸릴까?

오라클 환경에서 일반 유저는 lock 이 걸리면 계정 접속을 하지 못함
그렇다면 sys 유저는 lock이 걸리는 경우 어떻게 접속하고 대응할수 있을지 궁금해서 테스트해봄

sys 유저는 최상위 유저인데 sys 유저가 lock 이 걸려 접속하지 못하면 문제가 발생할 수 있음
본문에서는 실제로 sys 유저에 lock 이 걸리는지, 접속이 잘 되는지를 확인해봄

 

 

테스트
1. 일반 유저 lock 테스트
2. sys 유저 lock 테스트

 

 

테스트
1. 일반 유저 lock 테스트
일반 유저 생성

1 2	SQL> create user imsi identified by imsi; SQL> grant resource, connect to imsi;

 

 

접속 시도

1 2	SQL> conn imsi/imsi Connected.

정상적으로 접속됨

 

 

일반 유저 lock

1 2 3

SQL> alter user imsi account lock;   User altered.

 

 

유저 lock 확인

1 2 3 4 5 6 7 8 9 10

SQL>  set lines 200 pages 1000 col username for a10 select username, account_status  from dba_users  where username = 'IMSI';   USERNAME   ACCOUNT_STATUS ---------- -------------------------------- IMSI       LOCKED

정상적으로 IMSI 계정이 잠김

 

 

접속 시도

1 2 3 4 5

SQL> conn imsi/"dlatl1!" ERROR: ORA-28000: The account is locked.   Warning: You are no longer connected to ORACLE.

lock이 걸려 접속이 불가능함

 

 

2. sys 유저 lock 테스트
패스워드 변경

1 2 3

SQL> alter user sys identified by "positive1!";   User altered.

 

 

패스워드 파일 재생성

1 2 3

$ cd $ORACLE_HOME/dbs $ rm orapworacle19 $ orapwd file=$ORACLE_HOME/dbs/orapworacle19 password="positive1!" format=12

 

 

sys 접속 시도

1 2	$ sqlplus sys/"positive1!" as sysdba SQL>

정상적으로 접속됨

 

 

패스워드 없이 접속 시도

1 2	$ sqlplus / as sysdba SQL>

정상적으로 접속됨

 

 

sys 유저 lock 시도

1 2 3 4 5

SQL> alter user sys account lock; alter user sys account lock * ERROR at line 1: ORA-40365: The SYS user cannot be locked while the password file is in its current format.

에러가 발생함

 

 

패스워드 파일 mv로 이름 변경 후 재시도

1 2 3 4

$ mv orapworacle19 orapworacle19bak SQL> alter user sys account lock;   User altered.

명령어가 정상적으로 실행됨

 

 

유저 lock 확인

1 2 3 4 5 6 7 8 9 10

SQL>  set lines 200 pages 1000 col username for a10 select username, account_status  from dba_users  where username = 'SYS';   USERNAME   ACCOUNT_STATUS ---------- -------------------------------- SYS        LOCKED

정상적으로 SYS 계정이 잠김

 

 

sys 접속 시도

1 2	SQL> conn sys/"positive1!" as sysdba Connected.

정상적으로 접속됨

 

 

현재 패스워드 파일 인증을 막은 상태인데, 정상적으로 접속이 됨
이 말 뜻은 현재 패스워드 파일이 아닌 다른 인증으로 접속을 한것

 

 

remote_login_passwordfile 파라미터 값 확인

1 2 3 4 5

SQL> show parameter remote_login_passwordfile   NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ remote_login_passwordfile            string      EXCLUSIVE

현재 exclusive 로 설정되어 있음

 

 

remote_login_passwordfile 파라미터 공식 문서 설명
exclusive
패스워드 파일이 존재하지 않는 경우, 그 동작은 REMOTE_LOGIN_PASSWORDFILE이 none으로 설정된 것과 동일하게 됨
이 설명은 exclusive 설정이 패스워드 파일의 사용을 단일 데이터베이스로 제한하고, SYS 사용자 외에도 다른 사용자를 포함할 수 있다는 것을 의미함
또한, 패스워드 파일이 없을 때는 원격 로그인이 불가능하다는 것을 나타냄

 

none
오라클은 모든 패스워드 파일을 무시함, 따라서 권한 있는 사용자는 운영 체제에 의해 인증되어야 함
(https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/REMOTE_LOGIN_PASSWORDFILE.html#GUID-6619299E-95E8-4821-B123-3B5899F046C7)

 

 

먼저 exclusive 설명에 의하면 패스워드 파일이 존재하지 않을때 none 으로 설정한것과 동일하게 동작한다고함
그리고 none 설명을 보면 운영 체제에 의해 인증되어야 한다고 나와있음(OS 인증)

 

 

sqlnet.ora 파일 확인

1 2 3

$ cd $ORACLE_HOME/network/admin $ vi sqlnet.ora (공백)

현재 sqlnet.ora 파일은 비어 있는 상태임

 

 

이 경우 SQLNET.AUTHENTICATION_SERVICES 의 기본값인 ALL 로 동작하게됨
ALL 은 모든 인증 방법을 상위 인증 방식부터 순차적으로 적용해보면서 인증함
참고로 NONE 은 어떠한 인증도 사용하지 않고 오직 유효한 유저명과 패스워드로만 db에 접근 가능함
현재 ALL 로 설정되어 있기때문에 OS 인증을 통해 sysdba 로 접속이 되는 상태인것을 알수 있음
(https://docs.oracle.com/en/database/oracle/oracle-database/19/netrf/parameters-for-the-sqlnet.ora.html#GUID-FFDBCCFD-87EF-43B8-84DA-113720FCC095)

 

 

OS 인증은 OS의 특정 그룹에 속한 경우 sysdba 에 접근 가능한것을 말함
예를들어 아래 테스트처럼 oracle2 라는 유저를 생성하고 dba 그룹에 넣어만 주면 sysdba로 접속이 가능함
이후 dba 그룹을 제거하면 sysdba 접속이 불가능한것을 확인할 수 있음
dba 그룹에 속하게끔 oracle2 유저 생성 및 .bash_profile 복제

1 2	# useradd oracle2 -G dba # cp /home/oracle/.bash_profile /home/oracle2/.bash_profile

 

 

sqlplus 접속 시도

1 2 3

# su - oracle2 $ sqlplus / as sysdba (접속 성공)

 

 

oracle2 유저에게서 dba 그룹 삭제 후 sqlplus 접속 시도

1 2 3 4 5

# gpasswd -d oracle2 dba Removing user oracle2 from group dba # su - oracle2 $ sqlplus / as sysdba (접속 불가)

 

 

테스트 후 oracle2 유저 삭제

1	# userdel -r oracle2

 

 

다시 본론으로 돌아와서 SQLNET.AUTHENTICATION_SERVICES 를 NONE 으로 변경

1 2 3

$ cd $ORACLE_HOME/network/admin $ vi sqlnet.ora SQLNET.AUTHENTICATION_SERVICES= (NONE)

 

 

sys 접속 시도

1 2 3

$ sqlplus sys/"positive1!" as sysdba ERROR: ORA-01017: invalid username/password; logon denied

ORA-01017 에러가 발생함

 

 

패스워드 파일 복구 후 다시 sys 접속 시도

1 2 3 4

$ cd $ORACLE_HOME/dbs $ mv orapworacle19bak orapworacle19 $ sqlplus sys/"positive1!" as sysdba SQL>

여전히 접속이 됨
예상한대로라면 접속이 되지 않아야 정상임

 

 

sys 유저 lock 재실행

1 2 3 4 5

SQL> alter user sys account lock; alter user sys account lock * ERROR at line 1: ORA-40365: The SYS user cannot be locked while the password file is in its current format.

처음과 동일하게 패스워드 파일로 인해 lock을 실행할 수 없음

 

 

패스워드 파일 재생성 시 format 구문 제거(또는 format=12.2)

1 2 3

$ cd $ORACLE_HOME/dbs $ rm orapworacle19 $ orapwd file=$ORACLE_HOME/dbs/orapworacle19 password="positive1!"

 

 

sys 접속 시도

1 2	$ sqlplus sys/"positive1!" as sysdba SQL>

여전히 접속이 됨
예상한대로라면 접속이 되지 않아야 정상임

 

 

sys 계정 lock 확인

1 2 3 4 5 6 7 8 9 10

SQL>  set lines 200 pages 1000 col username for a10 select username, account_status from dba_users where username = 'SYS';   USERNAME   ACCOUNT_STATUS ---------- -------------------------------- SYS        LOCKED

lock 상태임

 

 

sys 계정 lock 재실행

1 2 3

SQL> alter user sys account lock;   User altered.

 

 

sys 계정 lock 확인

1 2 3 4 5 6 7 8 9 10

SQL>  set lines 200 pages 1000 col username for a10 select username, account_status from dba_users where username = 'SYS';   USERNAME   ACCOUNT_STATUS ---------- -------------------------------- SYS        LOCKED

동일한 lock 상태임

 

 

sys 접속 시도

1 2 3

$ sqlplus sys/"positive1!" as sysdba ERROR: ORA-28000: The account is locked.

드디어 계정이 잠겨있다고 나옴

 

 

정리하자면 초기에 패스워드 파일을 만든 뒤 sys 계정을 lock 하려고 했을때 에러가 발생한건
orapwd 의 format 옵션이 기본값은 12.2 인데 12로 설정했어서 발생한 에러였음
그래서 이때는 mv로 패스워드 파일을 이름변경(무력화) 시켜 os 인증으로 대신해서 접속 했던것이고,
다시 패스워드 파일을 복구 시킨 이후에는 format 이 12 이기 때문에 lock 자체가 걸릴수 없던 상태였음
그리고 마지막에 다시 orapwd 로 패스워드 파일을 만들때 format 을 따로 지정해주지 않아 기본값인 12.2 로 설정됨
이 fotmat 에서는 sys 계정을 lock 을 걸수 있었음
그렇기 때문에 이때부터는 sqlplus sys/"positive1!" as sysdba 입력시 ORA-28000: The account is locked. 라고 나올수 있었던것임

 

 

결론 :
sqlnet.ora 의 SQLNET.AUTHENTICATION_SERVICES 를 NONE 으로 해둔 상태에서
sys 계정에 lock 을 걸고 접속을 시도하면 계정이 잠겼다고 나옴(패스워드 파일 format 을 따로 설정하지 않거나 12.2 로 설정한 경우)
하지만 sqlnet.ora 의 SQLNET.AUTHENTICATION_SERVICES 를 설정하지 않거나 ALL 로 설정 해놓으면 lock 이 걸려 있어도 os 인증으로 접속 가능함

 

 

참조 : 

https://cafe.naver.com/prodba/55787
https://docs.oracle.com/en/database/oracle/developer-tools-for-vscode/getting-started/connecting-using-os-authentication.html
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/OS_AUTHENT_PREFIX.html
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/REMOTE_LOGIN_PASSWORDFILE.html#GUID-6619299E-95E8-4821-B123-3B5899F046C7
https://docs.oracle.com/en/database/oracle/oracle-database/19/netrf/parameters-for-the-sqlnet.ora.html#GUID-FFDBCCFD-87EF-43B8-84DA-113720FCC095
https://docs.oracle.com/en/database/oracle/oracle-database/19/netrf/parameters-for-the-sqlnet.ora.html#GUID-1FA9D26C-4D97-4D1C-AB47-1EC234D924AA
https://docs.oracle.com/en/database/oracle/oracle-database/19/ntqrf/creating-and-populating-password-files.html
https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/getting-started-with-database-administration.html#GUID-C7B90809-E930-44BF-B836-F760B0989BB0
https://positivemh.tistory.com/795