2026. 4. 13. 10:43ㆍDB/서버구축
가.실습 User 조회
▶ 사용 명령어
SQL> SELECT username, default_tablespace, temporary_tablespace, account_status, profile
2 FROM dba_users
3 ORDER BY username;
- User의 이름과 각 user의 여러 설정 사항을 조회한다.
- USERNAME : 사용자명
- DEFAULT_TABLESPACE : 기본으로 사용할 tablespace명
- TEMPORARY_TABLESPACE : 사용할 temporary tablespace명
- ACCOUNT_STATUS : 계정의 상태
- PROFILE : 사용 중인 profile명
ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ
1. DEFAULT TABLESPACE (기본 거주지)
사용자가 데이터를 만들 때(테이블 생성 등), 따로 위치를 지정하지 않으면 자동으로 저장되는 기본 저장 공간입니다.
비유: 입주민이 짐을 들고 왔을 때 "어디다 둘까요?"라고 묻지 않아도 기본적으로 할당된 '안방' 같은 곳입니다.
2. TEMPORARY TABLESPACE (임시 작업실)
데이터를 영구적으로 저장하는 게 아니라, 정렬(Order by)이나 그룹화(Group by) 같은 복잡한 계산을 할 때 잠시 사용하는 메모리 대피소입니다.
비유: 거실에서 큰 잔치를 준비할 때 잠시 물건을 쌓아두는 '임시 작업대'입니다. 작업이 끝나면 알아서 비워집니다.
3. QUOTA (저장 한도)
특정 테이블스페이스에서 이 사용자가 최대 얼마큼의 용량을 사용할 수 있는지 정해주는 한도입니다.
비유: "안방을 쓰되, 짐은 100GB까지만 채울 수 있다"라고 제한을 두는 것입니다. UNLIMITED로 설정하면 무제한으로 쓸 수 있습니다.
4. ACCOUNT (계정 상태)
이 계정을 사용할 수 있게 열어둘 것인지(UNLOCK), 아니면 잠가버릴 것인지(LOCK) 설정하는 것입니다.
비유: 현관문 도어락을 활성화할지 말지 결정하는 것입니다. 비밀번호를 여러 번 틀리면 보안을 위해 자동으로 LOCKED 상태가 되기도 합니다.
5. PROFILE (행동 지침 및 자원 제약)
비밀번호 정책(만료일, 복잡성)이나 시스템 자원 사용량(CPU 사용 시간 등)을 정의해둔 규칙 세트입니다.
비유: 아파트 관리 규약입니다. "비밀번호는 3개월마다 바꿔야 한다", "한 번에 1시간 이상 공용 시설을 점유할 수 없다" 같은 세부 규칙들을 한데 모아놓은 문서라고 보시면 됩니다.
SQL> DESC dba_users;
테이블 조회
SQL> SELECT username, default_tablespace, temporary_tablespace, account_status, profile
2 FROM dba_users
3 ORDER BY 1;
나.실습 User 생성
▶ 사용 명령어
CREATE USER <user 명>
IDENTIFIED BY <암호>
DEFAULT TABLESPACE <tablespace 명>
TEMPORARY TABLESPACE <tablespace 명>
QUOTA <크기> ON <tablespace 명>, ......
[ACCOUNT {LOCK / UNLOCK}]
[PROFILE {<profile명> / DEFAULT}]
- User를 생성한다.
- DEFAULT TABLESPACE : User의 기본 tablespace
- TEMPORARY TABLESPACE : User의 기본 temporary tablespace
- QUOTA : tablespace별 허용된 저장량
- ACCOUNT : 계정 잠금 설정
- PROFILE : 적용될 PROFILE 지정
- CREATE 문장은 ALTER 문장과 형식이 동일하다.
SQL> SELECT username, tablespace_name, max_bytes, max_blocks FROM dba_ts_quotas;
- 각 사용자의 tablespace별 quota량을 조회한다.
- MAX_BYTES : 할당된 quota량 (byte단위)
- MAX_BLOCKS : 할당된 quota량 (block개수)
- quota가 unlimited로 지정되어 제한하지 않는 경우 -1로 표시된다.
실습)
-User를 생성하기 전에 반드시 사용할 tablespace를 먼저 생성한다.
CREATE TABLESPACE te
DATAFILE '/app/ora19c/oradata/disk1/te01.dbf' SIZE 30M,
'/app/ora19c/oradata/disk2/te02.dbf' SIZE 30M;
CREATE TABLESPACE st
DATAFILE '/app/ora19c/oradata/disk2/st01.dbf' SIZE 30M,
'/app/ora19c/oradata/disk1/st02.dbf' SIZE 30M;
CREATE TABLESPACE indx
DATAFILE '/app/ora19c/oradata/disk1/indx01.dbf' SIZE 20M,
'/app/ora19c/oradata/disk2/indx02.dbf' SIZE 20M;
SELECT tablespace_name, bytes, file_name FROM dba_data_files;
-사용자생성
| CREATE USER te IDENTIFIED BY te DEFAULT TABLESPACE te TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON te QUOTA 2M ON st QUOTA 2M ON indx; ← 도표에 정의된 내용에 따라 user를 생성한다. CREATE USER st0 IDENTIFIED BY st0 DEFAULT TABLESPACE st TEMPORARY TABLESPACE temp; ← st0 사용자는 quota 설정을 하지 않는다 CREATE USER st1 IDENTIFIED BY st1 DEFAULT TABLESPACE st TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON st QUOTA 2M ON indx; CREATE USER st2 IDENTIFIED BY st2 DEFAULT TABLESPACE st TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON st QUOTA 2M ON indx ACCOUNT LOCK; ← ACCOUNT 설정을 lock로 지정한다. |
-결과조회
-st2 계정은 lock 걸려있다.
SELECT username, default_tablespace, temporary_tablespace, account_status, profile
FROM dba_users
ORDER BY 1;
- st0는 quota 가 없다.
SELECT username, tablespace_name, max_bytes, max_blocks
FROM dba_ts_quotas
WHERE username IN ('TE', 'ST0', 'ST1', 'ST2')
ORDER BY 1;
다.실습 Quota와 User
▶ 사용 명령어
SQL> SELECT u.username, u.default_tablespace, max_bytes, max_blocks
2 FROM dba_users u, dba_ts_quotas q
3 WHERE u.username = q.username
4 AND u.default_tablespace = q.tablespace_name;
- 사용자의 DEFAULT TABLESPACE별 quota량을 조회한다.
SQL> ALTER USER <user 명>
2 QUOTA <크기> ON <tablespace 명>;
- User의 quota 량을 조정한다.
실습)
-Quota조회
SELECT u.username, u.default_tablespace, q.max_bytes, q.max_blocks
FROM dba_users u, dba_ts_quotas q
WHERE u.username = q.username
AND u.default_tablespace = q.tablespace_name
AND lower(u.username) IN ('te', 'st0', 'st1', 'st2');
← quota가 할당된 사용자는 segment 생성에 문제가 없다.
← st0에게는 table을 생성 할 수 있는 권한이 없다
CONN st0/st0
CREATE TABLE st0 (no NUMBER);
← st tablespace에 대해 quota를 설정한다.
SELECT username, tablespace_name, max_bytes, max_blocks
FROM dba_ts_quotas
WHERE username = 'ST0';
← 결과 조회
GRANT CREATE SESSION TO st0; (세션 권한 부여해야 conn됨)
CONN st0/st0
CREATE TABLE st0 (no NUMBER); (테이블 생성)
INSERT INTO st0 VALUES (1);
라.실습 User 삭제
SQL> DROP USER <user명> [CASCADE]
- User를 삭제한다.
- CASCADE : User를 삭제하기 전 스키마의 모든 segment를 먼저 삭제한다.
실습)
SELECT owner, table_name FROM dba_tables
WHERE owner = 'ST0';
-테이블 삭제
DROP TABLE st0.st0;
DROP USER st0;
마.실습 User에 대한 권한와 Role 조회
*사용 명령어
SQL> SELECT grantee, privilege, admin_option FROM dba_sys_privs;
- User 또는 role에 할당된 시스템 권한을 조회 한다.
- GRANTEE : 권한을 할당 받은 user
- PRIVILEGE : 권한
- ADMIN_OPTION : 할당 받은 권한을 관리자와 동일하게 사용할 수 있는지 여부
SQL> SELECT grantee, owner, table_name, privilege, grantor FROM dba_tab_privs;
- User 또는 role에 할당된 객체 권한을 조회 한다.
- OWNER : 객체 권한이 할당된 개체의 소유 스키마
- TABLE_NAME : 객체 권한이 할당된 table명
- GRANTOR : 객체 권한을 할당해준 user (sys일 경우 owner와 동일하게 표시된다.)
SQL> SELECT grantee, granted_role FROM dba_role_privs;
- User 또는 role에 할당된 role을 조회 한다.
- GRANT_ROLE : 할당된 role명
실습)
--테이블 생성
CREATE TABLESPACE usr
DATAFILE '/app/ora19c/oradata/disk1/usr01.dbf' SIZE 5M;
-- u1 유저 생성
CREATE USER u1 IDENTIFIED BY u1
DEFAULT TABLESPACE usr
QUOTA UNLIMITED ON usr;
-- u2 유저 생성
CREATE USER u2 IDENTIFIED BY u2
DEFAULT TABLESPACE usr
QUOTA UNLIMITED ON usr;
-- u3 유저 생성
CREATE USER u3 IDENTIFIED BY u3
DEFAULT TABLESPACE usr
QUOTA UNLIMITED ON usr;
1)role 생성
-- 1. r1, r2 롤 생성
CREATE ROLE r1;
CREATE ROLE r2;
-- 2. 생성된 롤 확인 (조회 쿼리)
SELECT role
FROM dba_roles
WHERE role IN ('R1', 'R2')
ORDER BY 1;
2)role 권한 할당
1- role과 user에 시스템 권한 할당
- User에게 세션 권한을 할당한다.
GRANT create session TO u1,u2,u3;
- Role에 테이블 생성권한을 할당한다
GRANT create table TO r1;
SELECT grantee, privilege, admin_option
FROM dba_sys_privs
WHERE grantee IN ('U1', 'U2', 'U3', 'R1', 'R2')
ORDER BY 1;
2- 객체 권한 할당을 위한 예제 table u21,u31을 생
CREATE TABLE u2.u21 (no NUMBER);
CREATE TABLE u3.u31 (no NUMBER);
1)
-- 3. u1 유저에게 u21 테이블 조회 권한 부여
GRANT SELECT ON u2.u21 TO u1;
-- 4.롤2 에게 u31 테이블 조회 권한 부여
GRANT SELECT ON u3.u31 TO r2;
SELECT grantee, owner, table_name, privilege, grantor
FROM dba_tab_privs
WHERE grantee IN ('U1', 'U2', 'U3', 'R1', 'R2')
ORDER BY 1;
2)
--u1 에 r1,r2 role을 할당한다.
GRANT r1,r2 TO u1;
--u2 에 r2 role을 할당한다.
GRANT r2 TO u2;
SELECT grantee, granted_role
FROM dba_role_privs
WHERE grantee IN ('U1', 'U2', 'U3', 'R1', 'R2')
ORDER BY 1;
바.실습 시스템 권한 할당(grant)과 해제(revoke)
▶ 사용 명령어
SQL> GRANT <시스템 권한>,<시스템 권한> .......
2 TO [<user명 | role명>,<user명 | role명> ...... | PUBLIC];
3 [WITH ADMIN OPTION];
- User 또는 role에 시스템 권한을 할당한다.
- PUBLIC : DB의 모든 user에게 권한을 할당한다.
- WITH ADMIN OPTION : 할당된 권한을 SYS 관리자와 동일하게 할당하거나 해제할 수 있다.
SQL> REVOKE <시스템 권한>,<시스템 권한> .......
2 FROM [<user명 | role명>,<user명 | role명> ...... | PUBLIC];
- User 또는 role에 시스템 권한을 해제한다.
실습)
1)할당
-- 1. U1에게 아무 스키마에서나 테이블을 만들 수 있는 강력한 권한 부여
GRANT create any table TO u1;
-- 2. U1에게 시퀀스 생성 권한을 주고, 남에게 줄 수 있는 권한까지 부여
GRANT create sequence TO u1 WITH ADMIN OPTION;
-- 3. U2에게 본인 계정에서 테이블을 만들 수 있는 기본 권한 부여
GRANT create table TO u2;
SELECT grantee, privilege, admin_option
FROM dba_sys_privs
WHERE grantee IN ('U1', 'U2', 'U3')
ORDER BY 1;
-- 1. u1 유저로 접속 (권한을 전달할 수 있는 관리 옵션 보유자)
CONN u1/u1
-- 2. u1권한으로 u2에게 시퀀스 생성 권한 부여 (관리 옵션 포함)
GRANT create sequence TO u2 WITH ADMIN OPTION;
2)제거 Revoke
-- 1. u1에게 주었던 강력한 ANY 테이블 생성 권한 회수
REVOKE create session FROM u1;
-- 2. u2에게 주었던 본인 스키마 테이블 생성 권한 회수
REVOKE create session FROM u2;
-- 3. u2와 u3에게 주었던 시퀀스 생성 권한을 한 번에 회수
REVOKE create session FROM u2, u3;
사. 실습 객체 권한 할당(grant)과 해제(revoke)
SQL> GRANT <객체 권한>,<객체 권한> ....... ON <[user명].객체명>, <[user명].객체명> ......
2 TO [<user명 | role명>,<user명 | role명> ...... | PUBLIC];
3 [WITH GRANT OPTION];
- User 또는 role에 객체 권한을 할당한다.
- PUBLIC : DB의 모든 user에게 권한을 할당한다.
- WITH GRANT OPTION : 할당된 권한을 다른 user에게 할당할 수 있다.
SQL> REVOKE <객체 권한>,<객체 권한> ....... ON <[user명].객체명>, <[user명].객체명> ......
2 FROM [<user명 | role명>,<user명 | role명> ...... | PUBLIC];
- User 또는 role에 객체 권한을 해제한다.
- 직접 할당한 권한만 해제 가능하다.
1)
-- 1. u1 계정에서 u2에게 객체 권한 부여 (WITH GRANT OPTION)
CREATE TABLE u11 (no NUMBER);
CONN u1/u1
GRANT select ON u11 TO u2 WITH GRANT OPTION;
-- 2. u2 계정으로 접속하여 u3에게 권한 전파
CONN u2/u2
GRANT select ON u1.u11 TO u3;
-- 3. u1 계정으로 돌아와서 권한 할당 현황 확인
CONN u1/u1
SELECT grantee, owner, table_name, privilege, grantor
FROM user_tab_privs
WHERE table_name = 'U11';
아. 실습 Role 생성과 권한 할당
▶ 사용 명령어
SQL> CREATE ROLE <role 명>
2 [IDENTIFIED BY <암호>];
- Role을 생성한다.
SQL> DROP ROLE <role 명>;
- Role을 삭제한다.
SQL> GRANT <role 명> TO <user명 | role 명>;
- Role을 user나 role에 할당 한다.
- Role이 role에 할당되는 경우 순환해서 할당되지 않는다.
1)
CREATE ROLE rm;
CREATE ROLE rs1;
CREATE ROLE rs2;
CREATE ROLE rs3;
- role에 시스템 권한을 부여한다.
GRANT create session, create table, create sequence TO rs1
-role에 객체 권한을 부여한다
GRANT select, insert ON u1.u11 TO rs3;
- role에 role을 부여한다.
GRANT rs1, rs2, rs3 TO rm;
자. 실습 Default role과 SET 명령을 이용한 role 사용
▶ 사용 명령어
SQL> ALTER USER <user 명>
2 DEFAULT ROLE { <role 명>, ...... | ALL [EXCEPT <role 명>] | none };
- 사용자에 부여된 role중에 default role을 지정한다.
- EXCEPT : 전체 role중에 default에서 제외될 role을 지정
SQL> SET role
2 { <role 명> [IDENTIFIED BY <암호>, ...... ] || ALL [EXCEPT <role 명>] | none }
- 현재 세션에서 사용할 role을 지정한다.
SQL> SELECT * FROM SESSION_ROLES;
- 현재 세션에서 사용 중인 role을 조회한다
->실습
CREATE TABLESPACE insa
DATAFILE
'/app/ora19c/oradata/disk1/insa01.dbf' SIZE 2M,
'/app/ora19c/oradata/disk2/insa02.dbf' SIZE 2M;
CREATE USER insa
IDENTIFIED BY insa
DEFAULT TABLESPACE insa
QUOTA UNLIMITED ON insa;
SELECT username, default_tablespace, temporary_tablespace, account_status, profile
FROM dba_users
ORDER BY 1;
'DB > 서버구축' 카테고리의 다른 글
| 09_패스워드설정 (0) | 2026.04.14 |
|---|---|
| 08_오라클 자동실 (0) | 2026.04.14 |
| 06_tablespace (0) | 2026.04.09 |
| 05_리스너 서버 (1) | 2026.04.09 |
| 04_ Redo log group 추가와 삭제 (0) | 2026.04.08 |