Oracle - 사용자 관리 (사용자 권한)

🎈Oracle - 사용자 관리, 계정 생성, 권한 부여 하기

---

관리자 SYS로 접속

 

show user 

현재 접속한 DB의 사용자명을 보기

SYS

모든 권한 관리자

본인은 도커를 이용하여 sql을 사용하기 때문에 conn sys as sysdba; 로 입력한다. 

./sqlplus 타이핑 이후

sys as sysdba

또는

sqlplus / as sysdba

암호는 입력하지 않고 엔터 치거나 기본 암호는 oracle 이거나 임의로 설정하였던 암호일 수 있다.

---

1. 권한 부여

사용자 만들기 --> 권한 있는 사용자가 권한이 없는 사용자에게 권한 부여

---

2. 사용자 만들기

위치 : 관리자(sys,system)에서 만들기 -- 권한이 있는 계정으로 생성 

create user tester1 identified by 1234;

tester1이라는 계정을 비밀번호 1234로 생성한 것이다.

---

3. 사용자로 접속하기

방법 1) CMD -> sqlplus username / password 

sqlplus tester1 / 1234

방법 2) SQL 접속상태에서 -> conn username / password ;

conn tester1/1234;

바로 연결을 시도할 경우 에러가 발생하는데, 사용자 유저 계정에 권한을 부여해주어야 한다.

---

4. 접속할 권한 부여하기

주의 : 권한이 있는 사용자가 권한을 부여하는 명령어를 수행해야 한다.

먼저 sql 상태에서 다시 관리자 계정으로 접속을 해야 하는데 일반적인 sql 설치한 경우 conn / as sysdba 

본인은 도커를 이용하여 sql를 사용하기 때문에 conn sys as sysdba; 로 입력한다. 

접속 이후

grant create session to username;

grant create session to username;

grant succeeded가 떴다면

다시 연결을 한다음 show user을 수행하면 잘 뜨는 것을 확인할 수 있다.

---

정리 - EX: tester2 계정 생성

 

 

---

데이터베이스 보안을 위한 권한

권한의 역할과 종류 

데이터베이스 관리자의 시스템 권한

시스템 권한	기능
CREATE USER	새롭게 사용자를 생성하는 권한
DROP USER	사용자를 삭제하는 권한
DROP ANY TABLE	임의의 테이블을 삭제할 수 있는 권한
QUERY REWRITE	함수 기본 인덱스를 생성하는 권한
BACKUP ANY TABLE	임의의 테이블을 백업할 수 있는 권한

- 사용자를 위한 시스템 권한 

시스템 권한	기능
CREATE SESSION	데이터베이스에 접속할 수 있는 권한
CREATE TABLE	사용자 스키마에서 테이블을 생성할 수 있는 권한
CREATE VIEW	사용자가 스키마에서 뷰를 생성할 수 있는 권한
CREATE SEQUENCE	사용자 스키마에서 시퀀으를 생성할 수 있는 권한
CREATE PROCEDURE	사용자 스키마에서 함수를 생성할 수 있는 권한

권한을 부여할 때는 GRANT라는 명령어로 시작해야 한다. ( grant session ~~ )

---

권한 부여하는 grant 명령어 

사용자에게 시스템 권한을 부여하기 위해서는 GRANT 명령어를 사용한다.

grant privilege_name|role
to user_name
[with admin option];

만일 user_name 대신 PUBLIC을 기술하면 모든 사용자에게 해당 시스템 권한이 부여된다.

PUBLIC이란 DB 내에 있는 모든 계정 즉, 모든 계정을 의미한다.

---

5. 테이블 만들기 권한 주기

위에서 생성한 계정 tester1에게 테이블을 생성할 수 있는 권한을 부여하도록 할 것이다.

먼저, 테이블을 생성할 tester1 계정에 접속을 한 뒤, 테이블을 생성하는 구문을 작성한다.

create table tester( 
	id varchar(12),
    pw varchar(12) 
    );

아마 권한이 없으면 생성하기에 문제가 생긴다.

다시 관리자 계정으로 접속 후 아래와 같은 명령어를 수행한다.

 

grant create table to tester1;

 

> CREATE TABLE EMP (
  2  EMPNO NUMBER(2),
  3  ENAME VARCHAR2(10)
  4  );

아마 이렇게 해도 테이블스페이스에 대한 권한이 없다고 뜨면

ORA-01950: no privileges on tablespace

테이블스페이스 사용에 대한 권한이 없어서 발생하는 오류이다.

ALTER USER 유저명 QUOTA 100M ON USERS;

유저가 테이블 스페이스에서 사용할 수 있는 양을 지정해주려면 QUOTA로 용량을 지정해주어야 한다. 

QUOTA는 기본적으로(Default) 제한량이 없는 값(UNLIMITED)으로 설정되기 때문에 '굳이' 변경하지 않고 아래 테이블스페이스 권한 부여 명령어를 수행한다.

GRANT UNLIMITED TABLESPACE TO 유저명;

으로 권한을 부여해주어야 한다.

---

테이블 생성이 성공적으로 이루어짐

---

6. 명령어 수행하기

DML 명령어 수행 가능한지 확인하기 

INSERT

INSERT INTO EMP VALUES(100, 'A');

- 오류 발생 ORA-01438: value larger than specified precision allowed for this column

이유 : NUMBER(2) 테이블 컬럼을 생성해놓고 지정된 값에서 벗어난 값을 입력했기 때문이다.

 

INSERT INTO EMP VALUES(5, 'A'); 

UPDATE

UPDATE [테이블] SET [열] = '변경할값' WHERE [조건]

 UPDATE EMP SET EMPNO=5 WHERE EMPNO=20;

DELETE

 delete from emp where empno=5;

 

DROP TABLE EMP;

테이블 삭제 명령어도 수행되는 권한이 부여되었다는 것을 알 수 있다.

 

---

6. CREATE VIEW 뷰 생성 하기

 

 CREATE VIEW EMP_ENAME_VIEW AS SELECT EMPNO from emp;

 CREATE VIEW EMP_ENAME_VIEW AS SELECT EMPNO from emp;

권한이 없다고 오류가 발생하면 아래와 같이 수행한다. 

 

sys 계정에서 권한 부여하기

grant create view to 유저명;

grant create view to tester1;

conn tester1; 으로 

 CREATE VIEW 뷰_이름 AS SELECT 보고픈 칼럼 명 FROM 테이블명;

CREATE VIEW EMP_EMPNAME_VIEW AS SELECT ENAME FROM EMP; 

뷰 확인하기

select * from EMP_EMPNAME_VIEW;

 

---

7. TESTER2 계정에 TESTER1의 권한을 그대로 주기 WITH ADMIN OPTION

(TESTER1 계정은 테이블, 뷰 만드는 권한을 가지고 있다. 이것을 다른 계정에게도 부여하는 방식)

방법 1) SYSDBA에 받아오기

방법 2) TESTER1에 받아오기

tester1이 SYSDBA에 권한을 받을 때 WITH ADMIN OPTION 을 붙이면 다른 사용자에게 권한을 줄 수 있다.

 

1. TESTER1이 TESTER2에게 권한을 주기 -> 권한 전달이 되지 않아 오류가 발생한다.

2. SYSDBA가 TESTER1에게 권한을 줄 때 WITH ADMIN OPTION을 붙여 권한을 부여받고, 다시 (1번 수행)권한을 전달한다.

SQL> show user 

USER is "TESTER1"

SQL> grant create table to tester2;

TESTER1 계정은 TESTER2에게 권한을 줄 수가 없기 때문에 sysdba로 접속해야 한다.

---

SQL> conn sys as sysdba

Connected.

SQL> show user; 

USER is "SYS"

SQL> GRANT CREATE TABLE, CREATE VIEW TO TESTER1 WITH ADMIN OPTION;

테이블, 뷰 생성 권한을 동시에 부여하면서 이 권한을 지정할 수 있는 권한을 준다.

SQL> conn tester1; 

Enter password:
Connected.

---

위에서 1. TESTER1이 TESTER2에게 권한을 주기 를 수행한다.

SQL> grant create table, create view to tester2;

권한 전달에 성공하였다.

정확히 따지자면 TESTER1이 SYSDBA로부터 WITH ADMIN OPTION 권한을 받아오고 

자신의 객체를 TESTER2에게 줄 수 있는 방식이 되는 것이다.

SYSDBA -> TESTER1 -> TESTER2

 

---

8. 모든 사용자에게 권한을 부여하기

TESTER1, TESTER2 이 두 계정의 사용자한테 모든 권한을 부여하고 싶을 때 

SYSDBA가 모든 사용자에게 권한을 부여한다.

 

SQL> GRANT CREATE SEQUENCE, CREATE PROCEDURE, DROP ANY TABLE TO PUBLIC WITH ADMIN OPTION;

모든 유저들이 시퀀스, 스키마 함수 생성, 테이블 삭제 권한과 동시에, 관리자 권한을 부여 받음.

---

현재 자신에게 부여받은 권한을 확인해보는 법

 

[롤 관련 데이터 사전]

[Dictionary View]                          [Table]
------------------------               -------------------------------------------------------
ROLE_SYS_PRIVS                         ROLE에 부여된 시스템 권한
ROLE_TAB_PRIVS                         ROLE에 부여된 테이블 권한
USER_ROLE_PRIVS                       현재 사용자가 ACCESS 할 수 있는 ROLE
USER_TAB_PRIVS_MADE               현재 사용자의 객체에 부여한 객체 권한
USER_TAB_PRIVS_RECD               현재 사용자의 객체에 부여된 객체 권한
USER_COL_PRIVS_MADE              현재 사용자 객체의 특정 칼럼에 부여한 객체 권한
USER_COL_PRIVS_RECD               현재 사용자 객체의 특정 컬럼에 부여된 객체 권한
 

현재 사용자의 객체에 부여한 객체 권한을 보기 위해 

USER_TAB_PRIVS_MADE을 사용한다.

TESTER1 계정으로 접속 한 뒤

SQL> select * from USER_TAB_PRIVS_MADE;

GRANTOR는 권한 사용자를 의미하며, PRIVILEGE는 어떤 기능적 권한인지를 의미한다.

콘솔에서 보기 불편하면 SQL Developer에서 확인할 수 있다.

 

---

9. 객체 권한

객체 권한은 특정 객체에 조작을 할 수 있는 권한이다. 객체의 소유자는 객체에 대한 모든 권한을 가진다.

객체와 권한 설정 명령어

권한	TABLE	VIEW	SEQUENCE	PROCEDURE
ALTER	V		V
DELETE	V	V
EXECUTE				V
INDEX	V
INSERT	V	V
REFERENCES	V
SELECT	V	V	V
UPDATE	V	V

---

GRANT privilege_name [(column_name)] | ALL

ON object_name | role_name | PUBLIC

TO user_name;

GRANT privilege_name [(column_name)] | ALL  -- 1
ON object_name | role_name | PUBLIC  -- 2
TO user_name; -- 3

GRANT 명령어의 형식은 어떤 객체(2)에 대한 어떠한 권한(1)을 어느 사용자(3)에게 부여하는가를 설정한다. 시스템 권한과 차이점이 있다면 ON 옵션이 추가된다는 점, ON 다음에 테이블 객체나 뷰 객체 등을 작성한다.

---

사용자 1 - 다양한 테이블

사용자 2 - 사용자 1의 임의의 테이블 검색 

 

TESTER1에서 테이블 생성

 create table DEPT01 (
  2  DEPTNO NUMBER(2),
  3  DNAME VARCHAR2(10)
  4  );

 

   

insert into DEPT01 values (1, 'A');
insert into DEPT01 values (2, 'B');
insert into DEPT01 values (3, 'C');
insert into DEPT01 values (4, 'D');
insert into DEPT01 values (5, 'E');
insert into DEPT01 values (6, 'F');
insert into DEPT01 values (7, 'G');
insert into DEPT01 values (8, 'H');
insert into DEPT01 values (9, 'I');
insert into DEPT01 values (10, 'J');

 

TESTER1이 TESTER2에게 검색할 수 있는 권한 부여

GRANT SELECT ON DEPT01 TO TESTER2;

conn tester2; 접속 후 

select * from tester1.dept01;

 

tester2에서

select * from DEPT01; 을 하게 되면 수행할 수 없다. TESTER1의 DEPT01 테이블을 보기 위해

select * from TESTER1.DEPT01; 

스키마명.테이블명으로 검색해주어야 한다.

select * from schema.table; 

 

TESTER2 계정에서 TESTER1의 DEPT01 테이블 삽입, 수정, 삭제해보기

conn tester1; 을 통해 접속 후 권한을 부여한다

SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON DEPT01 TO TESTER2;

다시 conn tester2; 접속 후

INSERT

SQL> INSERT INTO TESTER1.DEPT01 VALUES(11, 'P');

UPDATE

SQL> UPDATE TESTER1.DEPT01 SET DEPTNO=12 WHERE DEPTNO=11;

DELETE

SQL> delete from TESTER1.DEPT01 WHERE DEPTNO=12;

 

 

 

항상 변경사항이 되었다면 commit; 을 통해 저장을 해주어야 다른 계정에서도 변경 내역을 볼 수 있다.

---

10. 권한 해지 (권한 철회)

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

REVOKE 명령어 형식

TESTER1 계정에 접속 후 REVOKE 명령어 수행 (권한 철회)

SQL> REVOKE SELECT ON DEPT01 FROM TESTER2;

다시 tester2에 접속하여 확인해보기

권한이 없다고 나온다.

 

모든 권한 해지

SQL> revoke all on dept01 from tester2; 

Revoke succeeded.

TESTER1 계정에서 다시 TESTER2에게 권한을 부여하기

SQL> grant select on dept01 to tester2;

SQL> select * from user_tab_privs_made;

 

sys as sysdba로 sys 계정으로 접속하여 

SQL> revoke select on tester1.dept01 from tester2;
스키마명. 테이블명으로 권한을 철회하는 것도 가능하다.

sys 계정에서는 GRANT, REVOKE 등 모든 권한을 가지고 있다. (root 계정이라 생각하면 된다.)

---

---

 11. TESTER3 계정을 생성해서 세션, 테이블 만들기 권한 부여하기 

SQL> create user tester3 identified by 1234;


SQL> GRANT create session, create table to tester3;

insert 권한 부여 

WITH GRANT OPTION; = 가진 권한을 다른 사용자에게도 부여할 수 있는 권한

권한 전달 권한 가능 

SQL> grant insert on dept01 to tester3 with grant option;

 

SQL> insert into tester1.dept01 values(50, 'Q'); 

 
TESTER2에서 INSERT 하려면 TESTER3 로부터 GRANT INSERT 권한을 받아야 한다. 

 

tester3 계정 SQL

TESTER3.SQL> grant insert on tester1.dept01 to tester2;

TESTER2 계정 SQL

TESTER2.SQL> insert into tester1.dept01 values(70, 'Q');

 

 

tester2의 권한을 select, update TESTER1 으로부터 받고 

TESTER2 가 -> TESTER3에게 SELECT 권한만 넘겨준다

 순서. TESTER1 접속하여 select, update 권한+권한 전달 부여 옵션을 부여한다.

TESTER1. SQL> GRANT SELECT, UPDATE ON DEPT01 TO TESTER2 WITH GRANT OPTION;

 TESTER2 접속하여 tester3에게 select 권한과 권한 전달 부여 옵션 부여하기

SQL> UPDATE TESTER1.DEPT01 SET DNAME = 'QQ' WHERE DNAME='Q';

 

TESTER2. SQL> grant select on tester1.dept01 to tester3;

TESTER3에 접속하여 select 확인

TESTER3 SQL > select * from tester1.dept01;

 

---

TESTER3의 권한을 철회하기.

TESTER3의 권한은 TESTER2에서 부여했기 때문에 TESTER1 에서는 사용할 수 없고 TESTER2에서 부여하여야 한다.

TESTER3에서 select * from user_tab_privs; 를 통해 권한 부여자 (GRANTOR)를 확인하면 알 수 있다.

 

---

롤(Role)을 사용한 권한 부여 

CONNECT 롤(role)

사용자가 데이터베이스에 접속 가능하도록 하기 위해서 가장 기본적인 시스템 권한 8가지를 묶어 놓았다.

• ALTER SESSION,
  CREATE CRUSTER,
  CREATE DATABASE LINK,
  CREATE SEQUENCE,
  CREATE SESSION,
  CREATE SYNONYM,
  CREATE TABLE,
  CREATE VIEW

RESOURCE 롤(role)

사용자가 객체(테이블, 뷰, 인덱스)를 생성할 수 있도록 하기 위해서 시스템 권한을 묶어놓았다.

• CREATE CRUSTER,
  CREATE PROCEDURE,
  CREATE SEQUENCE,
  CREATE TABLE,
  CREATE TRIGGER

SYSDBA는 위 두 역할의 권한을 가지고 있다.

DBA 롤

사용자들이 소유한 데이터베이스 객체를 관리하고, 사용자들을 작성하고 변경하고 제거할 수 있도록 하는 모든 권한을 가진다.

즉, 시스템 자원을 무제한적으로 사용하며, 시스템 관리에 필요한 모든 권한을 부여할 수 있는 강력한 권한을 보유한 롤이다.

sys 계정에서 tester5 계정 생성하기

SQL> create user tester5 identified by 1234;
SQL> grant connect, resource, dba to tester5;

dba의 role(역할)이 추가 되어야 grant 명령이 가능하다

 

tester5에 연결한 뒤 권한을 부여해본다.

SQL> grant select, update on tester1.dept01 to tester2;

SQL> grant create table to tester3;

 

---

 

---

사용자 롤(Role) 정의

롤(role) 부여하기. 

SYS 계정에서

 create role role1;

---

롤(role)에 권한 부여

grant create session, create table, create view to role1;

 

---

tester1에게 롤(role) 부여 

SQL> grant role1 to tester1;

---

롤(role) 회수하기

SQL> revoke role1 from tester1;

tester1에게 주었던 role1 을 회수한다. (철회)

---

롤 제거하기 

위치 : 롤을 만든 사용자 (sys)

drop role 롤명;

SQL> drop role role1;

---

롤을 사용하는 이유 (롤의 장점)

---

• 여러 사용자들에게 동일한 역할(role)을 부여하기 편리하다. 
• 역할의 권한을 만들어놓고 여러 사용자에게 역할만 부여하면 그 권한을 가지고 있기 때문이다.

---

 

📝동의어

---

동의어는 다른 데이터베이스 객체에 대한 별명이다. 데이터베이스에서는 여러 사용자들이 테이블을 서로 공유하는데, 다른 사용자의 테이블을 접근할 때 [사용자명.테이블명]으로 표현하는데, 이를 동의어를 적용하면 간단하게 요약해서 기술할 수 있다.

형식

CREATE [PUBLIC] SYNONYM synonym_name
FOR user_name.object_name;

---

실습) 
TESTER1.DEPT을 여러명이서 select을 해야할 경우

스키마명.테이블명을 작성하기 불편하기 때문에 TD이런식으로 약어를 사용한다.

 

SQL> CREATE TABLE SYSTBL (
	  ENAME VARCHAR2(20) );

SQL> INSERT INTO SYSTBL VALUES('A');
SQL> INSERT INTO SYSTBL VALUES('B');
SQL> INSERT INTO SYSTBL VALUES('C');

SYSTBL 테이블 생성 -> A,B,C 값 저장

TESTER1에게 SELECT 권한 부여

SQL> GRANT SELECT ON SYSTBL TO TESTER1;

conn tester1;

SQL> select * from SYS.SYSTBL;
 

 

다시 SYS계정 접속해서 아래와 같이 동의어 구문을 실행한다. SYS.SYSTBL 테이블을 ST로 약어로 지정.

SQL> CREATE SYNONYM ST FOR SYS.SYSTBL;

동의어 삭제하기

SQL> DROP SYNONYM ST;

 

TESTER1에게 시노님(SYNONYM) 권한 부여

SQL> GRANT CREATE SYNONYM TO TESTER1;

TESTER1에서 동의어 생성

SQL> CREATE SYNONYM ST FOR SYS.SYSTBL;

TESTER1에서 동의어를 통해 SELECT 해보기

SQL> select * from st;

 

SYS에서 TESTER2에게 SELECT 권한을 부여하고

TESTER2에서 동의어로 SELECT를 하고 싶을 때

공개 동의어(PUBLIC)를 사용한다. 

공개 동의어

실습) TESTER3에게 grant select on 권한 부여 

SQL> grant select on systbl to tester3;

 

SQL> CREATE PUBLIC SYNONYM PST FOR SYSTBL;

시노님(SYNONYM) PUBLIC으로 명령어 수행 이후 

TESTER2, TESTER3 에서 잘 수행되는지 확인해본다.

SQL> select * from pst;

DROP SYNONYM 은 시노님(SYNONYM)을 생성한 계정으로 가서 드랍해야 한다.

ST 동의어는 TESTER1에서 생성하였으므로 TESTER1에 이동해서 DROP 명령어 수행 해야함.

PST 동의어는 SYS에서 PUBLIC으로 생성하였기 때문에, DROP PUBLIC SYNONYM 이름; 형식으로 public을 넣어주어야 한다.

SQL> drop public synonym pst;

 

 

[DATABASE/Oracle] - 오라클 SQL - 저장 프로시저, 함수, 트리거 (코드버전)

[DATABASE/Oracle] - 오라클(Oracle) SQL - 저장 프로시저, 함수, 트리거

[DATABASE/Oracle] - 스칼라(scala)- 변수(var), 값(val)

[DATABASE/Oracle] - Oracle 오라클 - PL/SQL

[DATABASE/Oracle] - Oracle - 사용자 관리 (사용자 권한)

[DATABASE/Oracle] - Oracle SQL : View(뷰)

[DATABASE/Oracle] - Oracle SQL : JOIN(조인)

[DATABASE/Oracle] - Oracle SQL - Sub Query 서브 쿼리

[DATABASE/Oracle] - Oracle SQL : 제약조건