| SQL> ! | sqlplus를 나가지 않고 OS명령어 사용 |
| SQL> @ | 명령어 파일 실행 |
--- P.52 연결 : PROFILE 관리
# Default Password Resource Limits
SQL> @ $ORACLE_HOME/rdbms/admin/utlpwdmg.sql
| utlpwdmg.sql | ||
| CREATE OR REPLACE FUNCTION verify_function_11G ㄴ 프로시저 생성 : 비밀번호 복잡도 검사 |
||
| GRANT EXECUTE ON verify_function_11G TO PUBLIC; ㄴ 모두에게 프로시저 권한 부여 |
||
| ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 180 PASSWORD_GRACE_TIME 7 PASSWORD_REUSE_TIME UNLIMITED PASSWORD_REUSE_MAX UNLIMITED FAILED_LOGIN_ATTEMPTS 10 PASSWORD_LOCK_TIME 1 PASSWORD_VERIFY_FUNCTION verify_function_11G; |
PROFILE 생성 : DEFAULT 암호갱신주기 180일 암호갱신 유효기간 7일 암호 재사용 가능 로그인 시도 10회 가능 실패 초과 시 1일 계정 잠금 암호 복합성 검사 참조 : verify_function_11G |
|
| SELECT username, profile FROM dba_users; USERNAME PROFILE ------------------------------ ------------------------------ SYS DEFAULT INSA DEFAULT HR DEFAULT ... SELECT * FROM dba_profiles WHERE profile = 'DEFAULT'; PROFILE RESOURCE_NAME RESOURCE LIMIT --------------- -------------------------------- -------- -------------------- DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10 DEFAULT PASSWORD_LIFE_TIME PASSWORD 180 DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD VERIFY_FUNCTION_11G DEFAULT PASSWORD_LOCK_TIME PASSWORD 1 DEFAULT PASSWORD_GRACE_TIME PASSWORD 7 ... |
||
| # PROFILE 생성 | ||
| CREATE PROFILE insa_profile LIMIT FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME UNLIMITED; |
PROFILE 생성 : insa_profile 로그인 시도 3회 가능 접속실패 3회 초과 시 계정 잠금 지속 |
|
| SELECT * FROM dba_profiles WHERE profile = 'INSA_PROFILE'; PROFILE RESOURCE_NAME RESOURCE LIMIT --------------- -------------------------------- -------- -------------------- INSA_PROFILE FAILED_LOGIN_ATTEMPTS PASSWORD 3 INSA_PROFILE PASSWORD_LIFE_TIME PASSWORD DEFAULT INSA_PROFILE PASSWORD_REUSE_TIME PASSWORD DEFAULT INSA_PROFILE PASSWORD_REUSE_MAX PASSWORD DEFAULT INSA_PROFILE PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT INSA_PROFILE PASSWORD_LOCK_TIME PASSWORD UNLIMITED INSA_PROFILE PASSWORD_GRACE_TIME PASSWORD DEFAULT ... ALTER USER insa PROFILE insa_profile; SELECT username, profile FROM dba_users; USERNAME PROFILE ------------------------------ ------------------------------ SYSTEM DEFAULT INSA INSA_PROFILE INSA_BUHA DEFAULT ... sqlplus insa/비번오타 또는 conn insa/비번오타 x 3회 ORA-01017: invalid username/password; logon denied 4회차부터 정상입력해도 ORA-28000: the account is locked SELECT USERNAME,ACCOUNT_STATUS,LOCK_DATE,PROFILE FROM dba_users WHERE username = 'INSA'; USERNAME ACCOUNT_STATUS LOCK_DATE PROFILE ------------------------------ -------------------------------- --------- --------------- INSA LOCKED 22-DEC-23 INSA_PROFILE ALTER USER insa ACCOUNT UNLOCK; SELECT USERNAME,ACCOUNT_STATUS,LOCK_DATE,PROFILE FROM dba_users WHERE username = 'INSA'; USERNAME ACCOUNT_STATUS LOCK_DATE PROFILE ------------------------------ -------------------------------- --------- --------------- INSA OPEN INSA_PROFILE |
||
| # PROFILE 수정 | ||
| ALTER PROFILE insa_profile LIMIT FAILED_LOGIN_ATTEMPTS 2 PASSWORD_LOCK_TIME 1/1440; |
PROFILE 수정 : insa_profile 로그인 접속 시도 2회 가능 접속 실패 2회 초과 시 1분 계정 잠금 - 1(일)/24 1시간 |
|
| SELECT * FROM dba_profiles WHERE profile = 'INSA_PROFILE'; PROFILE RESOURCE_NAME RESOURCE LIMIT --------------- -------------------------------- -------- -------------------- INSA_PROFILE FAILED_LOGIN_ATTEMPTS PASSWORD 2 INSA_PROFILE PASSWORD_LIFE_TIME PASSWORD DEFAULT INSA_PROFILE PASSWORD_REUSE_TIME PASSWORD DEFAULT INSA_PROFILE PASSWORD_REUSE_MAX PASSWORD DEFAULT INSA_PROFILE PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT INSA_PROFILE PASSWORD_LOCK_TIME PASSWORD .0006 INSA_PROFILE PASSWORD_GRACE_TIME PASSWORD DEFAULT ... sqlplus insa/비번오타 또는 conn insa/비번오타 x 2회 SELECT USERNAME,ACCOUNT_STATUS,LOCK_DATE,PROFILE FROM dba_users WHERE username = 'INSA'; USERNAME ACCOUNT_STATUS LOCK_DATE PROFILE ------------------------------ -------------------------------- --------- --------------- INSA LOCKED(TIMED) 22-DEC-23 INSA_PROFILE 1분 후 정상접속 후 SELECT USERNAME,ACCOUNT_STATUS,LOCK_DATE,PROFILE FROM dba_users WHERE username = 'INSA'; USERNAME ACCOUNT_STATUS LOCK_DATE PROFILE ------------------------------ -------------------------------- --------- --------------- INSA OPEN INSA_PROFILE |
||
| # PROFILE 삭제 | ||
| DROP PROFILE insa_profile CASCADE; ㄴ 권한 회수 포함 SELECT USERNAME,ACCOUNT_STATUS,LOCK_DATE,PROFILE FROM dba_users WHERE username = 'INSA'; USERNAME ACCOUNT_STATUS LOCK_DATE PROFILE ------------------------------ -------------------------------- --------- --------------- INSA OPEN DEFAULT |
||
| # PROFILE 생성 | ||
| CREATE PROFILE insa_profile LIMIT PASSWORD_LIFE_TIME 2/1440 PASSWORD_GRACE_TIME 1/1440; |
PROFILE 생성 : insa_profile 2분 후 암호 갱신 만료 후 1분 유예 |
|
| SELECT * FROM dba_profiles WHERE profile = 'INSA_PROFILE'; PROFILE RESOURCE_NAME RESOURCE LIMIT --------------- -------------------------------- -------- -------------------- INSA_PROFILE FAILED_LOGIN_ATTEMPTS PASSWORD DEFAULT INSA_PROFILE PASSWORD_LIFE_TIME PASSWORD .0013 INSA_PROFILE PASSWORD_REUSE_TIME PASSWORD DEFAULT INSA_PROFILE PASSWORD_REUSE_MAX PASSWORD DEFAULT INSA_PROFILE PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT INSA_PROFILE PASSWORD_LOCK_TIME PASSWORD DEFAULT INSA_PROFILE PASSWORD_GRACE_TIME PASSWORD .0006 ... ALTER USER insa PROFILE insa_profile; SELECT USERNAME,ACCOUNT_STATUS,LOCK_DATE,PROFILE FROM dba_users WHERE username = 'INSA'; USERNAME ACCOUNT_STATUS LOCK_DATE PROFILE ------------------------------ -------------------------------- --------- --------------- INSA OPEN INSA_PROFILE 2분 후 sqlplus insa/oracle 또는 conn insa/oracle 접속 가능 ORA-28002: the password will expire within 0 days Connected. SQL> show user USER is "INSA" SELECT USERNAME,ACCOUNT_STATUS,LOCK_DATE,PROFILE FROM dba_users WHERE username = 'INSA'; USERNAME ACCOUNT_STATUS LOCK_DATE PROFILE ------------------------------ -------------------------------- --------- --------------- INSA EXPIRED(GRACE) INSA_PROFILE 1분 후 sqlplus insa/oracle 또는 conn insa/oracle 접속 시 ORA-28001: the password has expired Changing password for insa SELECT USERNAME,ACCOUNT_STATUS,LOCK_DATE,PROFILE FROM dba_users WHERE username = 'INSA'; USERNAME ACCOUNT_STATUS LOCK_DATE PROFILE ------------------------------ -------------------------------- --------- --------------- INSA EXPIRED INSA_PROFILE 비번 수정 후 USERNAME ACCOUNT_STATUS LOCK_DATE PROFILE ------------------------------ -------------------------------- --------- --------------- INSA OPEN INSA_PROFILE |
||
| # PROFILE 수정 | ||
| ALTER PROFILE insa_profile LIMIT PASSWORD_LIFE_TIME 180 PASSWORD_GRACE_TIME 7 PASSWORD_REUSE_TIME UNLIMITED PASSWORD_REUSE_MAX UNLIMITED FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME UNLIMITED PASSWORD_VERIFY_FUNCTION NULL; |
PROFILE 수정 : insa_profile 암호갱신주기 180일 암호갱신 유효기간 7일 암호 재사용 가능 로그인 시도 3회 가능 실패 초과 시 계정 잠금 지속 암호 복합성 검사 참조안함 -> 자리수 검증X |
|
| SELECT * FROM dba_profiles WHERE profile = 'INSA_PROFILE'; PROFILE RESOURCE_NAME RESOURCE LIMIT --------------- -------------------------------- -------- -------------------- INSA_PROFILE FAILED_LOGIN_ATTEMPTS PASSWORD 3 INSA_PROFILE PASSWORD_LIFE_TIME PASSWORD 180 INSA_PROFILE PASSWORD_REUSE_TIME PASSWORD UNLIMITED INSA_PROFILE PASSWORD_REUSE_MAX PASSWORD UNLIMITED INSA_PROFILE PASSWORD_VERIFY_FUNCTION PASSWORD NULL INSA_PROFILE PASSWORD_LOCK_TIME PASSWORD UNLIMITED INSA_PROFILE PASSWORD_GRACE_TIME PASSWORD 7 ... ALTER USER insa IDENTIFIED BY oracle; |
||
| # PROFILE 수정 | ||
| ALTER PROFILE insa_profile LIMIT PASSWORD_REUSE_TIME 2/1440 PASSWORD_REUSE_MAX 1; |
PROFILE 수정 : insa_profile 2분동안 암호 재사용 불가 1회 암호 변경해야 암호 재사용 가능 |
|
| SELECT * FROM dba_profiles WHERE profile = 'INSA_PROFILE'; PROFILE RESOURCE_NAME RESOURCE LIMIT --------------- -------------------------------- -------- -------------------- INSA_PROFILE FAILED_LOGIN_ATTEMPTS PASSWORD 3 INSA_PROFILE PASSWORD_LIFE_TIME PASSWORD 180 INSA_PROFILE PASSWORD_REUSE_TIME PASSWORD .0013 INSA_PROFILE PASSWORD_REUSE_MAX PASSWORD 1 INSA_PROFILE PASSWORD_VERIFY_FUNCTION PASSWORD NULL INSA_PROFILE PASSWORD_LOCK_TIME PASSWORD UNLIMITED INSA_PROFILE PASSWORD_GRACE_TIME PASSWORD 7 ... ALTER USER insa IDENTIFIED BY oracle1; User altered. ALTER USER insa IDENTIFIED BY oracle1; ORA-28007: the password cannot be reused ㄴ 2분 이내 암호 재사용 시도 ㄴ 2분 후 다른 비번으로 수정없이 재사용 시도 ALTER USER insa IDENTIFIED BY oracle; User altered. |
||
# resource 관리
SELECT * FROM dba_profiles WHERE profile = 'INSA_PROFILE';
PROFILE RESOURCE_NAME RESOURCE LIMIT
--------------- -------------------------------- -------- --------------------
INSA_PROFILE COMPOSITE_LIMIT KERNEL DEFAULT
INSA_PROFILE SESSIONS_PER_USER KERNEL DEFAULT
INSA_PROFILE CPU_PER_SESSION KERNEL DEFAULT
INSA_PROFILE CPU_PER_CALL KERNEL DEFAULT
INSA_PROFILE LOGICAL_READS_PER_SESSION KERNEL DEFAULT
INSA_PROFILE LOGICAL_READS_PER_CALL KERNEL DEFAULT
INSA_PROFILE IDLE_TIME KERNEL DEFAULT
INSA_PROFILE CONNECT_TIME KERNEL DEFAULT
INSA_PROFILE PRIVATE_SGA KERNEL DEFAULT
| SESSIONS_PER_USER | 동일한 유저로 접속가능한 동시 세션 수 |
| IDLE_TIME | 분 단위로 측정한 비활성 시간. 경과 시 자동 KILL |
| CONNECT_TIME | 분 단위로 측정한 연결 경과 시간. 경과 시 자동 KILL |
| LOGICAL_READS_PER_CALL | 한 문장에서 읽어들일 수 있는 블록의 수 (물리접,논리적) 제한 |
| LOGICAL_READS_PER_SESSION | 한 SESSION에서 읽어들일 수 있는 블록의 수 (물리접,논리적) 제한 |
| CPU_PER_CALL | 한 문장에서 사용가능한 CPU 시간, 1/100초 단위로 측정한 총 시간을 설정 |
| CPU_PER_SESSION | 한 세션에서 사용가능한 CPU 시간, 1/100초 단위로 측정한 총 시간을 설정 |
| COMPOSITE_LIMIT | (CPU_PER_SESSION,CONNECT_TIME,LOGICAL_READS_PER_SESSION,PRIVATE_SGA)의 가중합계로 자원 비용을 계산 |
| PRIVATE_SGA | 바이트 단위로 측정한 SGA의 전용 공간(shared server 환경(UGA)) |
CPU_PER_CALL 1000 -- 10초
show parameter resource_limit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_limit boolean FALSE
SELECT * FROM v$parameter WHERE name = 'resource_limit';
ISSES_MODIFIABLE : FALSE
ISSYS_MODIFIABLE : IMMEDIATE
...
# 리소스 관리 프로파일 사용
ALTER SYSTEM SET resource_limit = TRUE;
show parameter resource_limit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_limit boolean TRUE
SELECT * FROM v$parameter WHERE name = 'resource_limit';
VALUE : TRUE
...
ALTER PROFILE insa_profile LIMIT
IDLE_TIME 1
CONNECT_TIME 1
SESSIONS_PER_USER UNLIMITED;
SELECT * FROM dba_profiles WHERE profile = 'INSA_PROFILE';
conn insa session 2개 동시 접속 시도
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit
** oracle 계정에 연결된 상태에서는 누구나 SYS로 접속이 가능하다.
[oracle@oracle ~]$ sqlplus insa/oracle as sysdba
[oracle@oracle ~]$ sqlplus insa/35634 as sysdba
SQL> show user
USER is "SYS"
# SYS SESSION
GRANT SYSDBA TO insa;
SELECT * FROM user_users;
USERNAME : SYS
INITIAL_RSRC_CONSUMER_GROUP : SYS_GROUP
...
# INSA SESSION
| USER_ID | ROLE | USERNAME | INITIAL_RSRC_CONSUMER_GROUP | sqldeveloper 접속 시 설정 |
| INSA | DEFAULT | INSA | DEFAULT_CONSUMER_GROUP |  |
| INSA | SYSDBA | SYS | SYS_GROUP |  |
SELECT * FROM user_users;
ㄴ 로그인별 참고
# password file 삭제 TEST
[oracle@oracle ~]$ cd $ORACLE_HOME/dbs
[oracle@oracle dbs]$ rm orapwora11g
putty : 서버접속 > new session
[oracle@oracle ~]$ sqlplus / as sysdba
sqldeveloper > 192.168.56.102_insa
> 접속 가능
sqldeveloper > 192.168.56.102_insa_sysdba
ORA-01017: invalid username/password; logon denied
■ password file 생성
sysdba 롤을 받은 사용자에 대한 인증 체크
show parameter instance_name
# orapw<SID>
orapwd file=$ORACLE_HOME/dbs/orapwora11g password=admin
- password=admin : sys 계정의 비밀번호
- 11g부터 패스워드 대소문자를 구분한다.
[oracle@oracle dbs]$ ll
-rw-r-----. 1 oracle oinstall 1536 Dec 22 15:13 orapwora11g
...
sqldeveloper > 192.168.56.102
암호 admin 로 수정 > 접속 가능
192.168.56.102_insa
192.168.56.102_insa_sysdba
ㄴ 암호 수정해도 접속 불가
GRANT SYSDBA TO insa;
ㄴ orapwora11g 에 등록 된다.
192.168.56.102_insa
ㄴ 암호 oracle
192.168.56.102_insa_sysdba
ㄴ 암호 oracle
conn insa/oracle as sysdba
SQL> show user
USER is "SYS"
orapwd file=$ORACLE_HOME/dbs/orapwora11g password=admin ignorecase=n
# ignorecase=n 대소문자 구분한다.
# 패스워드 admind일 경우 admin은 허용되지 않음
orapwd file=$ORACLE_HOME/dbs/orapwora11g password=admin ignorecase=y
# ignorecase=y : 패스워드 대소문자를 구분하지 않겠다.
■ sysdba role 을 이용해서 오라클에 접속할 때 인증 체크는?
1. OS 인증(LOCAL)
- putty로 서버에 직접 접속
- login as: oracle -> DB를 설치한 계정이니 인증 끝 ★ 루트계정 보호 주의
- sqlplus / as sysdba -> 계정없이 접속가능
2. Client(remote) - server 환경에서는 password file을 생성한 인증 체크 방식
- sqldeveloper
SHOW PARAMETER remote_login_passwordfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
EXCLUSIVE : 리스너를 통하여 원격 접속 가능
NONE : 원격접속불가
SELECT * FROM v$parameter WHERE name='remote_login_passwordfile';
VALUE : EXCLUSIVE
ISSES_MODIFIABLE : FALSE
ISSYS_MODIFIABLE : FALSE
ㄴ STATIC PARAMETER
ALTER SYSTEM SET remote_login_passwordfile = exclusive SCOPE=SPFILE;
# password file 로 인증 체크해서 접속한 유저 정보
SELECT * FROM v$pwfile_users;
USERNAME SYSDBA SYSOPER SYSASM
------------------------------ ------ ------- ------
SYS TRUE TRUE FALSE
INSA TRUE FALSE FALSE
# 패스워드 대소문자 구분(11g)
11g부터 패스워드의 대소문자를 구분한다.
SQL> show parameter sec_case_sensitive_logon
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon boolean TRUE
ㄴ TRUE : 대소문자 구분한다.
SELECT * FROM v$parameter WHERE name='sec_case_sensitive_logon';
ISDEFAULT : TRUE
ISSES_MODIFIABLE : FALSE
ㄴ dynamic parameter
ALTER SYSTEM SET sec_case_sensitive_logon = FALSE;
show parameter sec_case_sensitive_logon
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon boolean FALSE
SQL> conn INSA/ORACLE -> 대소문자 구분 안함
Connected.
SQL> SHOW USER
USER is "INSA"
'Oracle 11g > Oracle DB' 카테고리의 다른 글
| 55 ORACLE Audit, Trigger (1) | 2023.12.27 |
|---|---|
| 54-2 ORACLE Audit (1) | 2023.12.26 |
| 52 ORACLE Role, Execute, Profile (1) | 2023.12.21 |
| 51 ORACLE Tablespace, Privilege, Role (1) | 2023.12.20 |
| 50 ORACLE SGA, Undo, User (0) | 2023.12.19 |