Oracle Database - DCL – Data Control Language
Database User
데이터베이스 사용자
- 데이터베이스에 접속하여 데이터를 이용하기 위해 접근하는 모든 사람
- 이용 목적에 따라 데이터베이스 관리자(Database Administrator: DBA), 최종 사용자, 응용프로그래머로 구분
- Oracle 데이터베이스 생성시 기본적으로 생성되는 계정
- SYS
- Data Dictionary Table의 소유자
- SYSTEM
- 각종 Dictionary View의 소유자
- 각 USER는 동일한 이름의 SCHEMA 소유자로, 해당 SCHEMA의 모든 객체들에 접근할 수 있다
사용자 관리
Syntax
사용자 생성 : CREATE USER username IDENTIFIED BY password;
비밀번호 변경 : ALTER USER username IDENTIFIED BY password;
사용자 삭제 : DROP USER username [CASCADE];
주의사항
- 일반적으로 DBA의 일
- 사용자를 생성하려면 CREATE USER 권한 필요
- 생성된 사용자가 Login 하려면 CREATE SESSION 권한 필요
- 일반적으로 CONNECT, RESOURCE의 ROLE을 부여하면 일반사용자 역할을 수행할 수 있음
[연습] bituser 사용자를 생성하고 비밀번호를 변경해 봅니다
SQL> conn system/manager
Connected.
SQL> CREATE USER c##bituser IDENTIFIED BY bituser;
User created.
SQL> conn bituser/bituser
ERROR:
ORA-01045: user BITUSER lacks CREATE SESSION privilege;
logon denied - USER를 생성해도 권한(Privilege)를 주지 않으면 아무 일도 할 수 없음
- 위 예에서는 사용자가 CREATE SESSION 권한을 갖고있지 않아 오류 발생
- 사용자는 DBA가 초기화한 암호를 갖게 되며, 로그인 후 ALTER USER 명령을 이용, 각자의 암호를 재설정
사용자 정보 확인
관련 DICTIONARY
- USER_USERS : 현재 사용자 관련 정보
- ALL_USERS : DB의 모든 사용자 정보
- DBA_USERS : DB의 모든 사용자 상세 정보 (DBA만 사용 가능)
SELECT * FROM USER_USERS;
[연습] USER_USERS, ALL_USERS, DBA_USERS Dictionary로부터 user 목록을 각각 출력해 봅니다
SQL> conn scott/tiger
SQL> SELECT username FROM user_users;
SQL> SELECT username FROM all_users;
SQL> SELECT username FROM dba_users;
(ERROR 발생, DBA 권한을 갖고 있지 않음)
SQL> conn system/manager
SQL> /
(SUCCESS, system 계정은 DBA 권한을 갖고 있음)권한(Privilege)과 롤(Role)
Privileges
권한(Privilege)
- 사용자가 특정 SQL 문을 실행하거나 특정 정보에 접근할 수 있는 권리
- 사용자는 작업에 요구되는 관련 권한에 대한 허가(GRANT)를 받아야 한다
- 종류
- 시스템 권한(80여개) :
- 스키마 객체 권한
롤(Role)
- 권한을 쉽게 관리하기 위하여 특정한 종류별로 묶어놓은 그룹
GRANT / REVOKE
권한/롤을 부여(GRANT)하거나 회수(REVOKE)
Syntax (System Privileges) - 시스템 권한
GRANT [system_priv|role [,system_priv|role ...]
TO {user [,user ...]|role|PUBLIC}
[WITH ADMIN OPTION];REVOKE [system_priv|role [,system_priv|role ...]
FROM {user [,user ...]|role|PUBLIC};Syntax (Object Privileges) - 개별 객체 권한
GRANT {[object_priv [,object_priv ...]|ALL}
ON object TO {user [,user ...]|role|PUBLIC}
[WITH ADMIN OPTION];REVOKE {[object_priv [,object_priv ...]|ALL}
FROM {user [,user ...]|role|PUBLIC};GRANT, REVOKE 문장은 실행 즉시 효력을 발휘한다 (재접속 등 필요 없음)
시스템 권한: 관리자로 수행 (ADMIN OPTION/GRANT ANY PRIVILEGES 권한)
GRANT create session TO user1;REVOKE create session FROM user1;스키마 객체 권한
GRANT select ON emp TO user1;REVOKE select ON emp FROM user1;WITH GRANT OPTION
- 해당 권한을 받은 사용자가 다시 제3자에게 권한을 부여할 수 있도록 하는 옵션
- 권한을 REVOKE 하면 해당 사용자가 3자에게 부여한 권한들도 함께 회수됨
GRANT select ON emp TO user2
WITH GRANT OPTION;------
-- dcl
-----
-- create : 데이터 베이스 객체 생성
-- alter : 데이터 베이스 객체 수정
-- drop : 데이터 베이스 객체 삭제
-- SYSTEM 계정으로 수행
-- 사용자 생성 : CREATE USER
CREATE USER c##bituser IDENTIFIED BY bituser;
-- SQLPLUSE에서 사용자로 로그인
-- 사용자 삭제 DROP USER
DROP USER c##bituser CASCADE; -- cascade : 연결된 모든 것을 함께 삭제
-- 다시 생성
CREATE USER c##bituser IDENTIFIED BY bituser;
-- 사용자 정보 확인
-- USER_ : 현재 사용자 관련
-- ALL_ : 시스템 전체 객체
-- DBA : DBA전용, 객체 모든 정보
SELECT * FROM USER_USERS;
SELECT * FROM ALL_USERS;
SELECT * FROM DBA_USERS;
-- 새로 만든 사용자 확인
SELECT * FROM DBA_USERS WHERE username = 'C##BITUSER';
-- 권한 {privilege)와 역할 {ROLE)
-- 특정 작업 수행을 위해 적절한 권한을 가져야 한다.
-- CREATE SESSION
-- 시스템 권한의 부여 ; GRANT 권한 TO 사용자
-- c##bituser에게 crete sesstion 권한 부여
GRANT create session to C##BITUSER;
-- 일반적으로 CONNECT, RESOURCE롤을 부여하면 일반 사용자의 역할을 수행할 수 있다.
GRANT connect, resource to C##BITUSER;
-- 오라클 12이후로는 임의로 Table Space를 할당해줘야한다.
ALTER USER C##BITUSER -- 사용자 정보 수정
DEFAULT TABLESPACE USERS -- 기본 테이블 스페이스를 USERS에 지정
QUOTA UNLIMITED ON USERS; -- 사용 용량 지정
-- 객체 권한 부여
-- C##BITUSER 사용자에게 HR.employees를 SELECT할수있는 권한 부여
GRANT SELECT on hr.employees to C##BITUSER;
-- 객체 권한 회수
REVOKE select on HR.employees FROM c##bituser;
-- 전체 권한 부여
-- GRANT ALL privileges ..