17장 사용자 관리_데이터베이스 보안을 위한 권한 생성, GRANT,비밀번호 변경(SQL 오라클)

안녕하세요 jju_developer입니다.

 

오늘은 데이터베이스 보안을 위한 사용자 관리에 대한 소개하는 시간을 갖겠습니다.

 

 DBA :DataBase Administrator) 데이터베이스 관리자

 

✔ DBA :DataBase Administrator

 데이터베이스 관리자는 

사용자가 데이터베이스의 객체(테이블, 뷰 등)에 대한 특정 권한을 가질 수 있도록 함으로써 

다수의 사용자가 데이터베이스에 저장된 정보를 공유하면서도 정보에 대한 보안이 이루어지도록 합니다.

 사용자마다 서로 다른 권한과 롤을 부여함으로써 보안을 설정할 수 있다.

 

✔ DB 시스템 권한과 기능

SQL 오라클 시스템 권한 목록

권한 (Privilage)

1.  시스템 권한 (System Privileges)

2.  객체 권한  (Object Privileges)

 

 시스템 권한은 사용자의 생성과 제거, DB 접근 및 각종 객체를 생성할 수 있는 권한 등 주로 DBA에 의해 부여되며 그 권한의 수가 80 가지가 넘기에 대표적인 시스템 권한의 예시는 위 테이블 참고 부탁드립니다.

 

✔ 사용자 생성하기

 사용자 계정을 발급받기 위해서 시스템 권한을 가진 SYSTEM으로 접속해야 합니다.

오라클을 처음 다운로드하신 후에 그냥 접속을 눌러서 계정을 만들면 오류가 나게 됩니다..

우선 SYSTEM으로 가야 시스템 권한이 있기 때문에 

이 둘은 다 SYSTEM 계정에서 생성, 변경을 하셔야 합니다.

 

IDENTIFIED BY 뒤에는 계정 비밀번호가 와야 합니다. 

 

C:\Users\user2>SQLPLUS USER10/PWD

SQL*Plus: Release 11.2.0.2.0 Production on 화 12월 20 17:54:22 2022

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

ERROR:
ORA-01045: user USER10 lacks CREATE SESSION privilege; logon denied

이 뜻은 권한이 없다는 뜻입니다. - 권한에 제한이 있는 SESSION을 USER10에 부여하도록 하겠습니다.

권한을 부여하게 된다면 아래와 같이 다시 콘솔 창에 들어가서 확인하면 제대로 CONNECT가 됩니다.

CREATE SESSION => 데이터베이스에 접속할 수 있는 권한

 

즉, 사용자 생성은 CREATE USER 및 GRANT로 권한을 꼭 부여해주어야 합니다~!!

 

사용자를 생성 후 비밀번호를 잊어버리셨다면

ALTER USER를 사용하여 바꿔주시면 됩니다!

 

※ 비밀번호 변경 시, 꼭 SYSTEM 계정이나 DBA 권한이 있는 계정으로 가서 ALTER를 해주셔야 합니다~!

 

✔ 권한을 부여하는 GRANT 명령어

 

1. CREATE SESSION 권한 부여하기

 

2. CREATE TABLE 권한 부여하기

이때 발생하는 오류는 insufficient privileges

이럴 때는 없는 권한을 GRANT를 해줘야 합니다.

무제한으로 테이블 스페이스를 부여한다는 뜻입니다.

이렇게 테이블이 정상적으로 생성되는 것을 볼 수 있습니다.

 

 

✔ 테이블스페이스(Tablespace)

 테이블스페이스(Tablespace)는 디스크 공간을 소비하는 테이블과 뷰 그리고 그 밖의 다른 데이터베이스 객체들이 저장되는 장소입니다.

 

 

이 디스크의 크기가 5GB이기 때문에 그 이상의 무리한 데이터를 넣게 된다면

실행이 되지 않습니다.

 

오라클에서 USER 테이블을 확인하는 방법입니다.

--테이블 스페이스 확인하기
SELECT USERNAME, DEFAULT_TABLESPACE
FROM DBA_USERS
WHERE USERNAME IN('USER10', 'SCOTT');

 

 

✔ 테이블스페이스(Tablespace) Quota 할당하기

QUOTA란?

테이블 스페이스의 할당량을 지정하는 것입니다.

 

여러 개의 테이블은 특정한 공간을 차지하게 되는데 주어진 스페이스를 할당받는 것입니다.

즉, 사용하고자 하는 테이블 스페이스 전체 용량 중 유저가 사용할 수 있는 양을 지정해주는 옵션입니다.

 

※테이블 인덱스 생성 시 꼭 테이블 스페이스를 명시해야 합니다.

GRANT CREATE TABLE TO USER10;

--테이블 스페이스 확인하기
SELECT USERNAME, DEFAULT_TABLESPACE
FROM DBA_USERS
WHERE USERNAME IN('USER10', 'SCOTT');

--테이블 스페이스 변경하기
ALTER USER USER10 DEFAULT TABLESPACE USERS;
ALTER USER USER10 TEMPORARY TABLESPACE TEMP;

--테이블 스페이스 쿼터 할당하기
ALTER USER USER10
QUOTA 1000M ON USERS;

USER10에 1기가만 할당하였습니다.

 

---

no	설명	상세 설명
1	기본 테이블 스페이스 변경	* ALTER USER scott DEFAULT TABLESPACE tblspace1; ALTER USER [사용자명] DEFAULT TABLESPACE [변경할 테이블스페이스명] ALTER USER 문을 사용하여 사용의 테이블스페이스를 변경할 수 있다.
2	기본 테이블 스페이스 할당 용량 변경	* ALTER USER scott QUOTA 10M ON tblspace1; ALTER USER [사용자명] QUOTA [할당용량] ON [기본 테이블스페이스명] QUOTA 속성을 사용하여 사용자의 테이블스페이스 사용량을 제한할 수 있다. (위의 예제는 10M로 제한)
3	기본 테이블에 무제한 용량 할당	* ALTER USER scott QUOTA UNLIMITED ON tblspace1; UNLIMITED 키워드를 사용하여 기본 테이블스페이스 용량을 무제한으로 할당할 수 있다.
4	임시 테이블 스페이스 변경	* ALTER USER scott TEMPORARY TABLESPACE temp1; TEMPORARY TABLESPACE를 사용하여 사용자의 임시 테이블스페이스를 변경할 수 있다.

 

---

 

✔ 객체와 권한 설정

객체 = 테이블, 뷰 등등

 

 객체 권한은 특정 객체에 조작을 할 수 있는 권한입니다. 객체의 소유자는 객체에 대한 모든 권한을 가집니다.
 객체 권한은 테이블이나 뷰나 시퀀스나 함수 등과 같은 객체별로 DML문(SELECT, INSERT, DELETE)을 사용할 수 있는 권한을 설정하는 것입니다.

-- 형식
GRANT PRIVILEGE_NAME [(COLUMN_NAME)] | ALL
ON OBJECT_NAME | ROLE_NAME | PUBLIC
TO USER_NAME;

 

✔ 다른 유저의 객체 접근하기

권한이 없는 상태에서는 접근 불가

CONN USER01/TIGER;
SHOW USER;
SELECT * FROM EMP; -- 조회할 수 없다.

이럴 때에는 권한이 있는 SCOTT 계정에서 USER01에게 GRANT를 해줘야 합니다.

-- 테이블 객체에 대한 SELECT 권한 부여하기
CONN SCOTT/TIGER;
SHOW USER;
GRANT SELECT ON EMP TO USER01;
CONN USER01/TIGER;
SHOW USER;
SELECT * FROM SCOTT.EMP;

 

 

✔ 사용자에게 부여된 권한 조회

CONN SCOTT/TIGER

1. 자신이 부여한 객체 권한 제거하기
SELECT * FROM USER_TAB_PRIVS_MADE;

2. 자신이 받은 객체 권한 제거하기
SELECT * FROM USER_TAB_PRIVS_RECD;

 

 

✔ 사용자에게서 권한을 뺏기 위한 REVOKE 명령어

 사용자에게 부여한 객체 권한을 데이터베이스 관리자나 객체 소유자로부터 철회하기 위해서는 REVOKE 명령어를 사용합니다.

REVOKE SELECT ON EMP FROM USER01;

 

 

✔ WITH GRANT OPTION

 사용자에게 객체 권한을 WITH GRANT OPTION과 함께 부여하면 그 사용자는 그 객체를 접근할 권한을 부여받으면서 그 권한을 다른 사용자에게 부여할 수 있는 권한도 함께 부여받게 된다.

GRANT SELECT ON SCOTT.EMP TO USER02
WITH GRANT OPTION;

이렇게 하게 된다면 이제 USER02에서도 SCOTT.EMP 테이블을 볼 수 있습니다.

 

 

 

 

 

 

 

 

이상으로 사용자 관리, 데이터베이스 보안을 위한 권한 생성, GRANT, 사용자 비밀번호 변경에 대한 설명을 모두 마치겠습니다.

 

감사합니다.

 

 

 

 

 

 

17장 스키마 개념 상세 설명_SQL(오라클)