[Oracle] DB사용자 생성 및 권한 부여 ( DCL, GRANT, ROLE, REVOKE, CONNECT, RESOURCE, DBA, 사용자 생성, 권한 부여 및 삭제

 

 

 

1. SYSTEM 계정으로 권한 부여 

 

드디어 권한을 부여 받아 쓰기만 했었던 DB를 이제는 내가 직접 만들어서 사용해볼 시간이 다가왔다. 짝짝짝!!  나도 드디어 DCL (데이터 제어어)를 써서 권한을 부여해 사용자에게 새로운 DB를 만들어 줄 수 있다.

 

 

3-1. DCL (데이터 제어어)

 

사용자를 생성하고 권한을 부여, 삭제하는 명령어이다. 권한을 부여하는 GRANT와 삭제하는 REVOKE가 있다.

 

- GRANT : 사용자에게 권한 부여

- REVOKE : 사용자의 권한을 박탈/ 삭제 

 

 

3-2. sql plus 

 

 sqlplus 에서 로그인 ①

 

 

- SQL Plus : SQL문을 실행시키고 그 결과를 볼 수 있도록 Oracle에서 제공하는 가장 기본적인 Oracle Database 유틸리티(프롬프트)

 

 

저렇게 sql을 검색창에 쳐서 sql plus를 찾아 클릭해보면 프롬프트창이 뜬다.

 

 

사용자명과 비밀번호를 입력하여 관라자 계정으로 접속을 할 수가 있다.

 

 

저렇게 없는 아이디와 비밀번호를 입력하면 (비밀번호는 원래 안보이게 설정되어있다) 당연히 로그인 할 수 없다고 뜬다.

 

 

이번엔 system이라는 관리자계정으로 로그인하면 정상적으로 로그인이 되었다고 뜬다.

 

 

- show user : 현재 DMBS에 로그인되어 있는 사용자를 보여주는 명령어 

 

접속이 잘되었는지 확인차 입력했는데, 정확히 system이라는 계정으로 로그인 되어있다.

 

 

sqlplus 에서 로그인 ②

 

 

 

- cnn / as sysdba : 오라클에서 관리자 권한으로 접속하는 명령어

 

 

따로 아이디와 비밀번호를 입력하지 않았다. 아이디/비밀번호가 없는 어떻게 로그인이 되는걸까? 

 

이유는 오라클 DB가 설치된 컴퓨터에서의 접속은 관리자가 접속했겠지 라고 생각해서 그냥 권한을 부여한다. 

 

Orace DB가 설치된 로컬컴퓨터가 아니라 다른 컴퓨터에서 하면 안되는 걸까? 이거는 다음에 한번 실험을 해봐야겠다.

 

 

현재 접속 계정을 조회해 보면 SYS라고 뜬다. 아니 그러면 관리자 계정이 2개인것인가? 아까 우리가 만들었던SYSTEM은 무슨 역할이고,SYS계정은 또 무슨 역할인가? 둘 차이가 머지?

 

두개의 계정은 오라클을 설치하면 등록되어 있는 기본 계정들이다.

- SYS : ORACLE DB관리자로 Super user이다. Oracle 시스템의 기반이 되는 Data dictionary 소유자이며, DB 생성과 삭제도 가능하다. Oracle 시스템의 총 관리자라고 보면 되고, SYSDBA 권한을 가진다.

- SYSTEM : SYS와 유사한 권한을 가지고 있지만 DB생성과 삭제는 불가능하다. 운영을 위한 권한을 가진다. SYSOPER의 권한을 가진다.

 

사용자를 만들고 권한을 부여하는건 두 관리자 계정 모두 가능하기에, 둘 중 하나를 골라 접속을 하면된다. 

 

---

 

conn

 

conn 명령어는 사용자를 변경하는 명령어. " conn 사용자이름/비밀번호" 를 입력하여 계정을 바꿀 수 가있다. 

 

 

USER가 SYS에서 SYSTEM으로 바뀐것을 볼 수 있다.

 

sqlpius에서도 사용자를 생성 및 권한 부여, 쿼리문등 대부분의 작업이 가능하지만, SQL Developer 를 쓰는게 좀 더 편하기 때문에 sqlpius는 이정도로 마무리 짓고 이제는 SQL Developer에서 작성하려고 한다. 

---

 

 3-2. sql developer  

 

 

sql developer에서 로그인 ①

 

 

- 먼저 sql developer에서 관리자 계정으로 들어가려 하면 저렇게 ORA-01017 오류가뜬다. 검색을 해보니 대/소문자 구분 에러라고 한다.

 

- 오라클 10g 버전까지는 대/소문자 구분을 하지 않았는데, 11g 부터는 대/소문자를 구분 한다고 한다.

 

 

show parameter sec_case; : sec_case라는 파라미터의 타입 옵션등을 조회해주는 명령어

 

alter system set sec_case_sensitive_logon=false; : 로그인시 대/소 문자의 구분을 없애주는 명령어 (기존은 true로 되어있다)

 

 

하지만 버전의 문제인지 저 sec_case라는 파라미터는 더이상 사용하지 않는 파라미터 타입이라고 나온다. 아무리 검색을 해봐도 관련된 정보는 없어 다음에 오라클 잘 아시는 분이 있으면 한번 여쭤바야겠다.

 

 

[ 오라클 버전 확인 ]

 

 

위에 쿼리문을 입력하면 현재 사용하고 있는 오라클의 버전을 확인 할 수 있다. 나는 확실히 21버전을 사용하고 있음이 확인이 되었고, 아마 저기 위에 대/소 문자 구분이 안되는 이유는 지금 내가 쓰는 XE21 버전이 11g버전과는 많이 차이가 있는듯 하다.

 

 

sql developer에서 로그인 ②

 

 

아이디를 SYSDBA로 하면 접속이 가능한건가?

 

이번에는 또 부적합하다고 나온다. 무엇이 문제일까?

 

 

지금은 찾아도 나오지도 않고 잘 모르겠다. 이미 SYSTEM계정으로 접속이 되어있는 상태이기 때문에 굳이 로그인을 하지 않아도 되지만, 비밀번호가 다르다거나, 대/소문자를 구분해 주는 명령어가 따로 있을것으로 생각된다.

 

이건 지금 접속이 되었다는 말인가? 아니면 해제 되었다는 말인지... 말이 어려워..

---

 

 3-3. 사용자 생성/권한

 

모르겠는 것들은 일단 뒤로하고 , 로그인은 정상적으로 되는것 같으니 새로운 사용자를 만들어보자.

<현재 새성된 계정 확인>

Select username from dba_users; : 

select username from all_users;

 

먼저 현재 생성되어져 있는계정이 무엇이 있는지 조회해봤다. 기본적으로 생성되어 있는 계정이 37개쯤 있었고, 저기 1번과 2번에 SYS, SYSTEM 계정이 있는것이 보인다.

 

 

 

사용자 생성(CREAT USER)

 

 

CREATE USER [사용자 아이디(이름)] IDENTIFIED BY [사용자 비밀번호] ; 

 

 

오류가 뜬다. 이뉴는 공통 사용자 이름을 적지 않아서인데, 12c 버전 부터는 공통계정앞에 c##을 붙여야 한다.  이유는 12c 부터 등장한 CDB, PDB 개념 때문이라고 한다.

 

 - CDB : 하나의 DB안에 여러개의 DB를 담을 수 있는 DB 즉, 컨테이너 역할의 DB  -> 방주인
 - PDB : CDB에 끼웠다 뺐다 할 수 있는(Pluggable) DB 즉, CDB안에 포함되는 DB -> 세입자

 

 

정확히 전문지식처럼 정리하기는 힘들거 같고, 약간 자바에서 처럼 pivate와 public개념 같기도 하고 아리쏭 하다. 아래에 잘 설명해주신 글이 있으니 참고 하면 좋을거 같다.

 

오라클 멀티테넌트 DB 기본개념 정리 (Oracle Multi-Tenant DB Basics)

 

 

 

앞에 c##을 붙여주니 공통된 이름으로 인식하여 정상적으로 생성이 되었다. 한번 확인해보자.

 

 

저기 38번으로 방금 등록한 USER1 사용자가 보인다. 근데 매번 이름 앞에 C##을 붙여주면 손이 많이간다. 그래서 이것도 바꿔줄 수 있는 옵션이 있다.

 

 

ALTER  SESSION SET  "_ORACLE_SCRIPT" = true;

 

 

위에 명령어를 입력해주면 앞으로 사용자 이름 앞에 C##을 붙일 필요가 없다. 수고링!

 

 

사용자 삭제(DROP USER)

 

DROP USER [사용자 아이디(이름)] CASCADE; 

 

 

원래는 삭제시에도 C## 을 붙여야 하는데, 이제는 C## 을 안붙이고도 삭제가 잘된다. 

 

- 뒤에 붙어있는 CASCADE는 계정 삭제와 함께, 사용자가 보유한 테이블 하위요소까지 같이 삭제한다는 명령어이다.

---

 

권한 부여 (GRANT)

 

사용자에게 권한을 부여할 때 GRANT 명령어를 사용하는데, 두가지 종류의 권한 방법이 있다. 하나는 권한을 개별로 주는 방법, 다른 하나는 권한이 모여있는 패키지를 주는 방법이다.

 

 

1. 개별 권한

 

GRANT [시스템 권한 이름 ] TO [사용자 계정]

 

기본 적인 권한을 부여하는 명령문이다. 테이블 생성, 테이블 조회, 데이터베이스 접속 권한등 각각의 권한을 개별로 부여할 수 있다. 아래 목록은 시스템 권한의 목록이다.

 

 

[시스템 권한 목록]

 

CREATE USER	데이터 베이스 유저 생성 권한
SELECT ANY TABLE	모든 유저의 테이블 조회 권한
CREATE SESSION	데이터베이스 접속 권한
CREATE TABLE	테이블 생성 권한
CREATE VIEW	view 생성 권한
CREATE PROCEDURE USER	rocedure, function, package 생성 권한
CREATE SEQUENCE	sequence 생성 권한
SYSDBA	데이터베이스 관리 최고 권한
SYSOPER	데이터베이스 관리 권한

 

 

이렇게 사용자에게 세션, 테이블 생성, 테이블 삭제등 하나씩 주는 방법도 있는데, 이렇게 하나씩 주면 언제 다 줄지 모른다. 그래서 우리는 "권한들이 모여있는 패키지(ROLES)" 를 이용해 한번에 여러개의 권한을 부여할 수 있다. 

 

 

2. ROLES (권한 묶음)

 

 

GRANT CONNECT,  RESOURCE,  DBA  TO  [사용자 계정] ;

 

저기 명령어를 보면 CONNECT와 RESOURCE가 보인다. ROLE이라고 하는 여러개의 권한을 묶어놓은 패키지 같은 상품이다. ROLE을 만들어서 거기에 직접 권한들을 부여할 수도 있지만, CONNECT와 RESOURCE 는 사용자에게 필수적인 권한만 모아둔 기본 ROLE이기 때문에 굳이 새로 만들필요없이 그대로 가져다 쓰면 된다.  각각 어떤 권한을 가지고 있는지 보자.

 

 

[생성되어 있는 ROLE의 권한]

 

역할	설명
CONNECT	- 데이터베이스에 접속할 수 있는 권한
RESOURCE	- 스키마 오브젝트를 생성,변경, 삭제 할 수 있는 권 - SYSTEM의 일부 권한  - CREATE (CLUSTER, TABLE, SEQUENCE, PROCEDURE,TRIGGER, TYPE, INDEXTYPE, OPERATOR)
DBA	- 관리자 권한 -  유저생성 및 권한 부여, 롤 생성 부여, 스키마 오브젝트 수정 및 삭제 - SYSTEMM, SYS 계정이 기본적으로 가진 권한

 

그러면 이제 권한을 하나씩 부여해보자. 한번에 3개 다 부여할 수 도 있고 하나씩 부여할 수 도 있다.

 

 

 

사용자 계정이 정상적으로 있는지 확인하고, CONN 명렬어를 이용해 계정을 변경해보려 하니 저렇게 오류가 났다. 왜 그러냐, 현재는 아무런 권한도 가지고 있지 않기 때문에 데이터베이스에 접속할 수 있는 권한도 없다.

 

 

무언가 바꼈다. 바꼈다는 것은 USER1에 정상적으로 권한이 부여되고 접속을 할 수 있게 되었다는게 아닌가? 한번 USER1의 아이디와 비번을 입력하여 접속을 해보자.

 

 

성공 !! 열심히 따라 만들었다. 완전 따라는 아니고 다 직접 입력해보고 실험해보면서 만들었는데.... 나도 드디어 권한을 부여할 수 있는 능력자다 ㅋㅋㅋ

 

---

 

권한 삭제 (REVOKE)

 

 

REVOKE [시스템 권한 이름 ] TO [사용자 계정]

 

REVOKE CONNECT,  RESOURCE,  DBA  TO  [사용자 계정] ;

 

 

권한 삭제는 앞에서 썼던 GRANT를 그냥 REVOKE로 바꿔주면 된다. 부여한 권한을 삭제하는 명령어다.

 

---

 

3-4. 기타 명령어 

 

 

계정 Lock 풀기 

 

 

ALTER  USER  [사용자 계정]  ACCOUNT  UNLOCK; 

 

 

혹시나 비밀번호를 잘못 입력해 3회 이상 오류가 나게되면 계정이 잠기게 된다. 이럴경우 관리자 계정에서 ACCOUNT를 LOCK -> UNLOCK으로 바꿔주면 다시 접속이 가능하다. 그 외에도 특정 사용자의 계정을 잠그고 싶으면 반대로 UNLOCK -> LOCK으로 바꿔주면 된다. 

 

 

 

사용자 권한 확인

 

SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = [사용자 계정] ;

SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = [사용자 계정]';

 

SELECT * FROM USER_ROLE_PRIVS;

SELECT * FROM USER_SYS_PRIVS;

 

 

위에 2개는 사용자 계정을 찾아서 보여주는 쿼리문, 밑에 2개는 현재 접속되어있는 자신의 ROLE을 확인하는 쿼리문이다.

 

 

 

ROLE에 포함된 권한 확인

 

 

SELECT * FROM  role_sys_privs  WHERE  ROLE = ' CONNECT ';

SELECT * FROM role_sys_privs  WHERE  ROLE = ' RESOURCE ';

 

 

이렇게 우리가 부여했던 ROLE인 CONNECT와 RESOURCE의 권한이 무엇이 있는지 확인이 가능하다.

 

---

 

오케이 오늘의 목표는 여기까지 입니다. 어제는 설치를 성공했고, 오늘은 사용자를 생성해서 권한도 부여해보았다. 이제는 진짜 여러가지 쿼리문을 써보면서 마치 내것처럼 익숙해지는 일이 남았다. 그러기 위해선 좀 더 효율적인 쿼리문을 생각해보려는 습관이 있어야한다. 오라클 내장함수, PL/SQL 도 활용하면서 좀 더 느낌있는 쿼리문을 작성해 보자.

 

[오늘의 TMI]

 

이번 주 금요일날 면접을 보고왔다. 회사는 부산 센텀에 위치해 있는데, 이름을 말하는건 안될 것 같고 암튼 다녀왔다. 면접관님들도 친절히 대해 주셨고, 분위기도 밝아서 좋았다. 회사는 너무 좋아서 사실 다음주 월요일 발표인데 합격만 되면 가고 싶다. (너무!!) 근데 처음에 손코딩으로 코딩 테스트, CS 기본용어들을 적는 시간이 있었는데 많이 못적었다. CS지식은 쿠키랑 세션은 잘 몰라서 못적었고 나머지는 빼곡히 적었는데, 뒤에 JAVA로 코딩하는 문제는 시간문제도 있었지만 거의 못풀었다. 흠... 그래서 아쉽다는거다. 말도 좀 더 잘 할 수 있었는데 프로젝트 설명도 좀 더 디테일 하게 할 걸 하는 후회가 남는다. 머 항상 인생이란게 지난것에 대해 후회를 많이 하는 편이니... 지금 내가 해야하 것은 매일 매일 공부해서 좀 더 성장하는것이다. 꾸준히 하다보면 더 좋은 기회가 있을거고 가장 중요한건 나의 성장이니깐 !!! (근데 떨리긴 한다 ㅋㅋㅋㅋ 월요일날 좋은 결과 있기를 )

 

 

면접비도 받았다 ! 감사힙니다. 맛있는거 먹자 :)

 

 

[도움 받은 사이트]

오라클 유저에게 기본적으로 주는 롤 connect와 resource 에 포함된 권한 확인

[Oracle] 권한 생성(GRANT), 권한 삭제(REVOKE)

오라클 계정생성, 권한부여 명령어 1초만에 습득