이전 포스트: [Oracle] Oracle 메모리 관리 기법들
다음 포스트: [Oracle] DBMS_JOB & DBMS_SCHEDULER
1. Schema와 User
Oracle Server에 접속하기 위해 사용하는 것이 User(사용자)입니다.
특정 사용자(User)가 만들어 놓은 모든 Object의 집합을 Schema(스키마)라고 합니다.
Object라고 하면 table, index, view, constraint, trigger, dblink, synonym, sequence 등을 의미합니다.
schema와 user는 다른 의미이지만 일반적으로 서로 같이 혼용해서 많이 사용합니다.
2. user 생성하기
Oracle Server를 설치하면 기본적으로 관리자 계정인 SYS와 SYSTEM 계정이 생성됩니다. 이 계정들은 데이터베이스를 관리할 목적으로 제공되는 것으로 일반 작업을 할 때 사용하는 것은 권장하지 않습니다. 일반 작업이 필요할 땐 일반 작업용 계정을 필요할 때 마다 추가로 생성해서 사용하는 것을 권장합니다.
그리고 SYS 계정의 경우 기본적으로 암호가 설정되어 있지 않기 때문에 암호 설정을 해주는 것이 반드시 필요합니다.
새로운 업무가 생성될 때 해당 서비스와 관련해서 많은 TABLE, INDEX, VIEW 등의 Object가 생성될 것입니다. 이러한 Object들을 저장할 Tablespace를 새로 만들고 그 Tablespace를 이용하는 사용자를 새로 생성하는 경우가 많습니다.
새로운 user 생성 순서
1) 생성할 사용자의 default tablespace를 결정하고 해당 tablespace를 생성합니다.
2) 생성할 사용자가 사용하는 temporary tablespace를 결정하고 생성합니다.
3) 사용자를 생성합니다.
4) 적절한 profile과 권한, role 등을 생성한 후 할당해 줍니다.
사용자 생성하기
(1) webuser의 default tablespace 생성하기
$ sqlplus / as sysdba
SQL> set line 200
SQL> col tablespace_name for a10
SQL> col file_name for a50
SQL> select tablespace_name, bytes/1024/1024 MB, file_name
2 from dba_data_files;
SQL> create tablespace ts_webhard
2 datafile '/ORA19/app/oracle/oradata/ORACLE19/ts_web01.dbf' size 100M;
SQL> create tablespace ts_web_idx
2 datafile '/ORA19/app/oracle/oradata/ORACLE19/ts_web_idx01.dbf' size 10M;
SQL> select tablespace_name, bytes/1024/1024 MB, file_name
2 from dba_data_files;
(2) Temporary Tablespace 생성하기
SQL> create temporary tablespace temp_web
2 tempfile '/ORA19/app/oracle/oradata/ORACLE19/temp_web01.dbf' size 10M;
(3) user 생성하기
SQL> create user webuser
2 identified by webpwd
3 default tablespace ts_webhard
4 temporary tablespace temp_web
5 quota unlimited on ts_webhard
6 quota 0m on system;
quota
: 전체 테이블 스페이스 용량 중 사용자가 사용할 수 있는 할당량
(4) 권한 설정
SQL> grant resource, connect to webuser;
-- 접속 가능한지 확인
SQL> conn webuser/webpwd;
3. 사용자 정보 확인하기
(1) default tablespace와 temporary tablespace 정보 확인하기
SQL> set line 200
SQL> col default_tablespace for a10
SQL> col temporary_tablespace for a10
SQL> select username, default_tablespace "Default TS", temporary_tablespace "Temp TS"
2 from dba_users
3 where username="WEBUSER";
4. profile 관리하기
profile은 사용자 계정의 행동에 제약사항을 두기 위해 사용하는 경우가 대부분입니다.
password 관련 profile 생성하기
조건
1) 로그인 시도 5회 실패 시 계정을 5일 동안 사용 못하게 할 것
2) 계정의 암호는 10일에 한 번씩 변경하게 할 것
3) 동일한 암호는 10일 동안 사용 못하게 할 것
SQL> create profile sample_prof limit
2 failed_login_attempts 5
3 password_lock_time 5
4 password_life_time 10
5 password_reuse_time 10;
- failed_login_attempts: login 시도 최대 횟수. 모두 소진하면 계정이 잠김(횟수 단위)
- password_lock_time: 계정이 잠기면 며칠 동안 잠글 것인지 기간(일 단위)
- password_life_time: 동일한 암호를 며칠 간 사용하게 할 것인지(일 단위)
- password_reuse_time: 동일한 암호를 사용할 수 없도록 설정하는 기간(일 단위)
Resource 관련 profile 만들기
조건
1) 1명당 연속적으로 CPU를 사용할 수 있는 시간을 10초로 제한할 것
2) 하루 중 8시간만 DB에 접속 가능하게 할 것
3) 10분 동안 사용하지 않으면 강제로 접속을 끊을 것
SQL> create profile re_sample_prof limit
2 cpu_per_session 1000
3 connect_time 480
4 idle_time 10;
- cpu_per_session: 하나의 session이 CPU를 연속적으로 사용할 수 있는 최대 시간 설정(1/100초 단위)
- connect_time: 하루 동안 DB Server에 접속할 수 있는 총 시간(분 단위)
- idle_time: 연속 휴면 시간이 이 값을 넘으면 접속을 해제(분 단위)
사용자에게 profile 할당하기
현재 모든 사용자가 적용 받고 있는 profile 확인하기
SQL> set pagesize 50;
SQL> select username "사용자명", profile "적용 프로파일"
2 from dba_users
3 where username='WEBUSER';
해당 profile에 어떤 내용이 있는지 확인하기
SQL> set line 200
SQL> col profile for a13
SQL> col resource_name for a30
SQL> col resource for a10
SQL> col limit for a10
SQL> select * from dba_profiles
2 where profile='SAMPLE_PROF';
SQL> col profile for a15
SQL> select * from dba_profiles
2 where profile='RE_SAMPLE_PROF';
사용자에게 profile 적용시키고 확인하기
SQL> alter user webuser profile sample_prof;
SQL> alter user webuser profile re_sample_prof;
SQL> select username, profile
2 from dba_users
3 where username='WEBUSER';
사용 안 하는 profile 삭제하기
-- 현재 사용자에게 할당되어 있는 기본 profile은 삭제되지 않음.
SQL> drop profile re_sample_prof;
-- cascade 옵션을 주면 삭제 가능
SQL> drop profile re_sample_prof cascade;
SQL> select username, profile
2 from dba_users
3 where username='WEBUSER';
5. privilege(권한) 관리하기
profile이 user가 어떤 것들을 하지 못하게 제한하는 것이라면,
privilege는 user가 어떤 것들을 할 수 있게 허락하는 것이 목적입니다.
System Privilege: System 전체에 영향을 주는 권한
Object Privilege: 특정 object에 관련된 privilege
SYSTEM 관련 권한 할당/해제
index
create any index: 소유자에 상관없이(any) 모든 테이블에 인덱스를 생성할 수 있는 권한drop any index: 소유자에 상관없이 모든 인덱스를 삭제할 수 있는 권한alter any index: 소유자에 상관없이 모든 인덱스를 수정할 수 있는 권한
table
create table: 자신의 테이블을 생성할 수 있는 권한create any table: 소유자에 상관없이 다른 user 이름으로 테이블을 생성할 수 있는 권한alter any table: 소유자에 상관없이 모든 테이블의 구조를 수정할 수 있는 권한drop any table: 소유자에 상관없이 모든 사용자의 테이블을 삭제할 수 있는 권한update any table: 소유자에 상관없이 모든 사용자의 테이블을 수정할 수 있는 권한delete any table: 소유자에 상관없이 모든 사용자의 테이블을 삭제할 수 있는 권한insert any table: 소유자에 상관없이 모든 사용자의 테이블을 추가할 수 있는 권한
session
create session: 서버에 접속할 수 있는 권한alter session: 접속 상태에서 환경값을 변경할 수 있는 권한restricted session: Restricted 모드로 open된 DB에 접속할 수 있는 권한
tablespace
create tablespace: Tablespace를 생성할 수 있는 권한alter tablespace: Tablespace를 수정할 수 있는 권한drop tablespace: Tablespace를 삭제할 수 있는 권한unlimited tablespace: Tablespace 사용 용량을 무제한으로 허용하는 권한(quota 옵션 적용 X)
-- khyup 사용자에게 create table, create session 권한 할당
SQL> grant create table, create session to khyup;
-- khyup 사용자에게서 create table 권한 해제
SQL> revoke create table from khuyp;
사용자가 가진 System 권한 조회
SQL> select * from dba_sys_privs
2 where grantee='SCOTT';
위 결과에서 adm 컬럼은 with admin option 옵션 여부를 나타냅니다. 이 옵션은 권한을 위임하는 기능입니다. 예를 들어, A에게 create table 권한을 줄 때 아무 옵션 없이 주면 A는 create table 권한만 받게 되지만 grant create table to a with admin option 이렇게 주게 되면 A는 또 다른 사용자에게 create table 권한을 할당해 줄 수 있습니다. 또한 다른 사용자가 가진 create table 권한을 회수할 수도 있습니다.
Object 관련 Privilege
주로 DML과 연관이 많습니다.
Object를 select, insert, update, delete 등을 할 수 있는 권한을 의미합니다.
Object 권한 할당/해제
SQL> grant select on webuser.webtest to khyup;
SQL> grant update on webuser.webtest to khyup with grant option;
SQL> revoke select on webuser.webtest from khyup;
system 권한 같은 경우 다른 사용자에게 권한을 위임할 수 있는 옵션을 with admin option을 이용했지만, object 권한은 with grant option을 사용합니다.
Object 권한 조회
SQL> select grantee, grantor, table_name, privilege from dba_tab_privs
2 where grantee='KHYUP';
GRANTEE GRANTOR TABLE_NAME PRIVILEGE
--------------- --------------- --------------- ----------------------------------------
KHYUP WEBUSER TEST01 UPDATE
6. Role 관리하기
만약 권한(Privilege)이 너무 많아서 grant 명령어로 하나하나 할당하기 번거롭다면 role로 한 번에 할당할 수 있습니다.
role은 쉽게 말하면 권한의 묶음이라고 생각하시면 됩니다.
Role 생성
SQL> create role trole;
Role에 권한 할당
SQL> grant create session, create table to trole;
User에게 Role 할당
SQL> grant trole to khyup;
User - Role 조회
SQL> select * from dba_role_privs where grantee='SCOTT';
Role - Privilege 조회
SQL> select * from dba_sys_privs where grantee='CONNECT';
SQL> select * from dba_sys_privs where grantee='RESOURCE';
참고
- <오라클 관리 실무> - 서진수
