DB유저관리 : user 생성, 권한부여, 패스워드관리

💡 핵심 요약 (TL;DR)

DB유저관리 : user 생성, 권한부여, 패스워드관리의 동작 원리를 이해하고 실제 프로젝트에 적용하기 위한 실무 가이드다. * user명, 초기패스워드를 설정하여 유저를 생성한 뒤 어떤 권한을 줄지 확인을 적용할 수 있다.

user 생성

• create user 권한 소유자가 DB에 USER 생성 가능
  • user명, 초기패스워드를 설정하여 유저를 생성한 뒤 어떤 권한을 줄지 확인

$> conn sys/oracle as sysdba
$> create user demo identified by 초기패스워드;

$> conn demo/초기패스워드
//출력값
ERROR: DB에 접속할 수 있는 권한 부족
//에러 발생하여 현재 접속된 유저가 없는 상태이다

SQL> conn sys/oracle as sysdba
Connected.
SQL> grant create session, create table    
  2  to demo;
Grant succeeded.

//권한이 생겨서 접속 성공
SQL> conn demo/demo
Connected.
SQL> show user
USER is "DEMO"

명령어로 user 생성

• default로 설정한 테이블스페이스의 공간할당을 user생성시 바로 처리한다.
• default T/S외 다른 T/S(ex user, system, sysaux, 새로_생성한_T/S)
  • undo, temp는 디폴트 T/S로 사용할 수 없다.
• system, sysaux는 default T/S로 설정하는 것은 좋지 않다.

//기본값
create user 새로운_유저명
identified by 새로운_비밀번호
default tablespace 테이블스페이스명
temporary tablespace temp
quota 사이즈 on 위와_동일한_테이블스페이스명
quota 사이즈 on users;

//예시
create user test
identified by test
default tablespace example
temporary tablespace temp
quota 30M on example
quota 20M on users;

EM으로 user 생성

user계정잠금

• 유저한 명이 퇴사한 경우 lock하지않으면 그 계정으로 해킹시도가 일어날 수 있다.
• 후임자가 들어오면 unlock하면 됨

//계정잠금
$> alter user 유저명 account lock;

//계정잠금해제
$> alter user 유저명 account unlock;

권한유형 2가지

권한유형	개념	소유자
System권한	DB를 조작할 수 있는 권한. 대략 100개 정도 된다.	DBA
Object권한	Object를 조작할 수 있는 권한.	Object소유자

System권한

System권한 부여

• with admin option 옵션 : sys사용자가 나눠준 옵션을 다른 유저에게 나눠줄 수 있는 권한을 주는 옵션

SQL> conn sys/oracle as sysdba
Connected.
SQL> grant create session, create table, create sequence, create view
  2  to demo
  3  with admin option;

Grant succeeded.

SQL> conn demo/demo
Connected.
SQL> show user
USER is "DEMO"

System권한 회수

• with admin option 옵션으로 퍼져나간 시스템권한도 연쇄적으로 같이 회수될까?
  • 퍼져나간 시스템권한은 연쇄적으로 회수되지않는다.
  • 직접 명령어를 실행(revoke)해서 시스템권한을 각각 회수해야한다.

SQL> conn sys/oracle as sysdba
Connected.
SQL> revoke create table
  2  from demo;

Revoke succeeded.

Object권한

Object권한 부여

• hr사용자의 employees테이블의 내용을 select해보자
• with grant option 옵션 : 오브젝트소유자가 나눠준 권한을 받은 유저가 다른 유저에게 자신이 받은 오브젝트 권한을 나눠줄 수 있다.

SQL> conn demo/demo
Connected.
SQL> col last_name format a20
SQL> select employee_id, last_name  
  2  from employees
  
ERROR at line 2:
ORA-00942: table or view does not exist

• ERROR가 발생한다 => demo 사용자가 가지고 있는 employees테이블이 없기때문에
  • 우린 다른 유저인 hr소유의 테이블을 검색하고싶다 어떻게 해야할까?
• Object의 소유자인 hr을 테이블명앞에 명시해보자.

SQL> select employee_id, last_name
  2 from hr.employees;

ERROR at line 2:
ORA-00942: table or view does not exist

• 여전히 에러가 발생한다. 왜일까? => 권한이 없기때문에
• 권한은 누가 줄수있을까? 원칙적으로 object의 소유자인 hr이 demo에게 권한을 줘야한다.
  • DBA인 sys도 권한을 줄 수 있다.
  • sys가 demo사용자에게 hr소유의 권한을 줄때의 명령어는 아래와 같다.

grant select, insert
on hr.employees
to demo;

• 권한으로 뭘 줄수있을까?
  • select, insert, update, delete 등등

SQL> conn hr/hr
Connected.
SQL> grant select, insert
  2  on employees 
  3  to demo
  4  with grant option;

Grant succeeded.

SQL> select employee_id, last_name
  2  from hr.employees;

EMPLOYEE_ID LAST_NAME
----------- --------------------
	174 Abel
	(중량)
107 rows selected.

• 성공적으로 실행됨을 확인할 수 있다.

Object권한 회수

• with grant option 옵션으로 퍼져나간 오브젝트권한도 연쇄적으로 회수가능할까?
  • 퍼져나간 오브젝트권한도 연쇄적으로 회수 가능하다.(cascade효과)

SQL> conn hr/hr
Connected.
SQL> revoke select, insert
  2  on employees
  3  from demo;

Revoke succeeded.

SQL> conn demo/demo
Connected.
SQL> 
  1  select employee_id, last_name
  2* from hr.employees;
ERROR at line 2:
ORA-00942: table or view does not exist

패스워드관리

• 초기패스워드는 DBA가 생성한다.
• 패스워드 변경하는 명령어
• user생성 시 profile에 대한 정의를 생략하면 default profile을 할당받는다.

//기본형
alter user 유저명
indentified by 새로운_비밀번호;

//예시
SQL> alter user demo
  2  identified by a1234;

User altered.

profile(프로파일)

• uesr의 패스워드 관리 방법이 정의된 파일
• 모든 user는 반드시 하나의 profile을 할당받는다.
• profile 내 암호 보안 기능 설정 종류

명령어	설명
FAILED_LOGIN_ATTEMPTS	로그인 시도 실패 횟수 지정
PASSWORD_LOCK_TIME	지정된 로그인 연속 실패 횟수 이후 LOCK 상태로 유지되는 기간일 지정
PASSWORD_LIFE_TIME	암호가 만료되기 전 사용할 수 있는 기간 지정. 이 기간 내에 암호를 바꾸지 않으면 암호가 만료되어 이후 연결 거부
PASSWORD_GRACE_TIME	암호 만료 후 비밀번호 변경을 위한 유예기간(경고는 표시하지만 로그인을 허용) 지정. 유예 기간 동안 암호를 바꾸지 않으면 암호가 만료됨
PASSWORD_REUSE_TIME	암호를 재사용할 수 있는 기간 지정. PASSWORD_REUSE_TIME을 정수 값으로 설정한 경우에는 PASSWORD_REUSE_MAX를 UNLIMITED로 설정해야 함
PASSWORD_REUSE_MAX	현재 암호를 재사용할 수 있기 전에 필요한 암호 변경 횟수 지정. PASSWORD_REUSE_MAX를 정수 값으로 설정한 경우에는 PASSWORD_REUSE_TIME을 UNLIMITED로 설정해야 함
PASSWORD_VERIFY_FUNCTION	암호 복잡성 검사를 수행할 PL/SQL 함수를 CREATE PROFILE문에 인수로 전달함. ex) 비번은 영문대소문자와 특수묹 혼합 8글자이상이면서 아이디와 비번 똑같이 설정 금지하는 함수. 오라클이 기본적으로 제공하는 함수 $> @?/rdbms/admin/utlpwdmg.sql
PROFILE	생성할 프로파일 이름

#TILITWILL(JAVA)Oracle