Oracle - 사용자 관리.
Posted 05 7, 2011 14:06, Filed under: DataBase/Oracle
# 한번의 광고 클릭으로, 당신을 대신해서 불우이웃을 도울 기회가 많아집니다.
# 테이블 스페이스 생성및 사용자 생성
1) smsuser의 default tablespace 생성하기
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 FROM DBA_DATA_FILES; TABLESPACE MB FILE_NAME ---------- ---------- -------------------------------------------------- SYSTEM 480 /app/oracle/oradata/oracle10/system01.dbf UNDOTBS1 200 /app/oracle/oradata/oracle10/undotbs01.dbf SYSAUX 250 /app/oracle/oradata/oracle10/sysaux01.dbf USERS 5 /app/oracle/oradata/oracle10/users01.dbf SCOTT_DATA 1000 /app/oracle/oradata/oracle10/SCOTT_DATA.dbf SQL> CREATE TABLESPACE TS_SMS DATAFILE '/app/oracle/oradata/oracle10/TS_SMS01.dbf' SIZE 10M; 2 Tablespace created. SQL> SELECT TABLESPACE_NAME, BYTES/1024/1024 MB, FILE_NAME 2 FROM DBA_DATA_FILES; TABLESPACE MB FILE_NAME ---------- ---------- -------------------------------------------------- SYSTEM 480 /app/oracle/oradata/oracle10/system01.dbf UNDOTBS1 200 /app/oracle/oradata/oracle10/undotbs01.dbf SYSAUX 250 /app/oracle/oradata/oracle10/sysaux01.dbf TS_SMS 10 /app/oracle/oradata/oracle10/TS_SMS01.dbf USERS 5 /app/oracle/oradata/oracle10/users01.dbf SCOTT_DATA 1000 /app/oracle/oradata/oracle10/SCOTT_DATA.dbf 6 rows selected.
2) Temporary tablespace 생성
SQL> CREATE TEMPORARY TABLESPACE TEMP_SMS TEMPFILE '/app/oracle/oradata/oracle10/TEMP_SMS01.dbf' SIZE 10M; 2 Tablespace created.
3) 사용자 생성
SQL> CREATE USER SMSUSER IDENTIFIED BY SMSPWD DEFAULT TABLESPACE TS_SMS TEMPORARY TABLESPACE TEMP_SMS QUOTA UNLIMITED ON TS_SMS QUOTA 0M ON SYSTEM ; User created.
4) 권한 설정하기
SQL> GRANT RESOURCE, CONNECT TO SMSUSER ; Grant succeeded. SQL> CONN SMSUSER/SMSPWD Connected. SQL> -- # 사용자 정보 확인 SQL> set line 200 SQL> col defulat_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 = 'SMSUSER' 4 ; USERNAME Default TS Temp TS ------------------------------ ------------------------------ ------------------------------ SMSUSER TS_SMS TEMP_SMS
# profile 관리하기
- Profile은 사용자 계정의 행동에 제약사항을 두기 위해서 사용하는 경우가 대부분입니다.
예를 들어 5분 이상 활동이 없으면 접속을 강제로 종료시킨다든지, 1개월 마다 암호를 다른 것으로 바꾸게 강제로 적용한다든지 하는 것들이 대표적인 예입니다.
1)Password profile 관련 파라미터
1. Failed_login_attempts : login 시도를 실패할 경우 계정을 잠그는데 여기 설정된 횟수만큼 시도한 후 계정을 잠그게 됩니다.
2. Password_lock_time : 위 1번에서 계정이 잠기면 여기서 며칠 동안 잠글 것인지 기간을 정하는 파라미터입니다.
단위는 일이고 여기서 일수와 무관하게 DBA가 unlock 해서 사용하게 할 수 있습니다.
3. Password_life_time : 동일한 암호를 며칠간 사용하게 할 것인지 설정하는 파라미터입니다.
단위는 일이며 이 기간이 지나도 안 바꾸면 다음 로그인할 때 강제로 바꾸게 프롬프트를 보여줍니다.
4. Password_grace_time : 위 3번 항목에서 만료되어도 이 파라미터에 지정된 값만큼 더 암호를 변경할 기간을 허용하게 됩니다.
5. Password_reuse_time : 동일한 암호를 다시 사용할 수 없도록 설정하는 기간, 즉 암호를 변경하라고 했는데 사용자가 동일한 암호를 다시 사용하려고 할 수 있기에 같은 암호를 다시 쓸 수 없게 만들어야 할 때 사용하는 파라미터
6. Password_reuse_max : 동일한 암호를 위 5번 설정을 피해 재사용을 할 경우 최대 사용 가능한 횟수를 지정하는 파라미터
7. Password_verify_function : 암호를 보다 복잡하게 만들기 위해서 특정 함수를 적용시켜 사용자의 암호를 점검.
이 함수는 오라클에서 만들어 둔 기본 함수인 verify_function을 사용할 수 있고 사용자가 별도의 함수를 만들어서 적용할 수도 있습니다.
여기서는 verify_function 함수를 이용할 경우 어떤 조건을 점검하는지 보겠습니다.
* 암호는 최소한 4글자 이상 되어야 합니다.
* 암호는 사용자 계정과 달라야 합니다.
* 암호는 하나의 특수문자나, 알파벳, 숫자가 포함되어야 합니다.
* 암호는 이전 암호와 3글자 이상 달라야 합니다.
# Password 관련 profile 생성
- 조건 1: 로그인 시도 3회 실패시 계정을 5일동안 사용 못하게 할 것.
- 조건 2: 계정의 암호는 15일에 한 번씩 변경하게 할 것.
- 조건 3: 동일한 암호는 15일 동안 사용 못하게 할 것.
SQL> CREATE PROFILE SAMPLE_PROF LIMIT 2 FAILED_LOGIN_ATTEMPTS 3 3 PASSWORD_LOCK_TIME 5 4 PASSWORD_LIFE_TIME 15 5 PASSWORD_REUSE_TIME 15 6 ; Profile created.
# Resource profile 관련 파라미터
- 이 profile을 사용하려면 resource_limit = true 라는 설정이 되어 있어야 합니다.
즉 startup 할 때 사용되는 parameter file 에 위 문장을 적어 놓거나 또는 지금 즉시 적용시키고 싶으면 9i 이상 버전일 경우엔 alter system set resource_limit = true; 를 실행하면 됩니다.
1. Cpu_per_session : 하나의 세션이 CPU를 연속적으로 사용할 수 있는 최대 시간을 설정합니다.
무한루프 같은 쿼리가 작동되면 혼자서 CPU를 연속적으로 점유하기 때문에 이 설정을 사용해서 그런 일을 막고자 하는 것입니다.
1/100 초 단위입니다.
2. Sessions_per_user : 하나의 사용자 계정으로 몇 명의 사용자가 동시에 접속할 수 있는지를 설정하는 파라미터입니다.
3. Connect_time : 하루동안 DB Server에 접속할 수 있는 총 시간을 설정합니다.
4. Idle_time : 연속적으로 휴먼 시간이 여기 값을 넘으면 접속을 해제합니다.
예를 들어 idle_time 5 이렇게 하면 5분동안 활동이 없는 세션은 강제로 접속이 끊어지게 됩니다.
5. Logical_reads_per_session : 한 session에서 사용 가능한 최대 block 수를 지정합니다.
6. Private_sga : MTS / shared server 일 경우 해당 session의 SGA 사용량을 bytes 단위로 설정합니다.
7. Cpu_per_call : 하나의 call당 cpu를 점유할 수 있는 시간이며 1/100 초 단위입니다.
8. Logical_reads_per_call : 하나의 call 당 읽을 수 있는 block의 개수를 지정합니다.
# Resource 관련 profile 만들기
SQL> ALTER SYSTEM SET RESOURCE_LIMIT = TRUE; System altered.
- 조건 1: 1명당 연속적으로 CPU를 사용할 수 있는 시간을 10초로 제한할 것.
- 조건 2: 하루 중 8시간만 DB에 접속 가능하게 할 것.
- 조건 3: 10분동안 사용하지 않으면 강제로 접속을 끊을 것.
SQL> CREATE PROFILE RE_SAMPLE_PROF LIMIT CPU_PER_SESSION 1000 CONNECT_TIME 480 IDLE_TIME 10 ; Profile created.
# 사용자에게 profile 할당하기
- 여기에서는 위 2번과 4번에서 만든 profile을 smsuser에게 설정 합니다.
1. 현재 모든 사용자가 적용받고 있는 profile 확인 하기
SQL> SET PAGESIZE 50 SQL> SELECT USERNAME , PROFILE FROM DBA_USERS WHERE USERNAME = 'SMSUSER' ; USERNAME PROFILE ------------------------------ ------------------------------ SMSUSER DEFAULT
2. 해당 profile에 어떤 내용이 있는지 확인하기
- 위에서 만든 sample_prof의 내용을 조회
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' 3 ; PROFILE RESOURCE_NAME RESOURCE LIMIT ------------- ------------------------------ -------- ---------- SAMPLE_PROF COMPOSITE_LIMIT KERNEL DEFAULT SAMPLE_PROF SESSIONS_PER_USER KERNEL DEFAULT SAMPLE_PROF CPU_PER_SESSION KERNEL DEFAULT SAMPLE_PROF CPU_PER_CALL KERNEL DEFAULT SAMPLE_PROF LOGICAL_READS_PER_SESSION KERNEL DEFAULT SAMPLE_PROF LOGICAL_READS_PER_CALL KERNEL DEFAULT SAMPLE_PROF IDLE_TIME KERNEL DEFAULT SAMPLE_PROF CONNECT_TIME KERNEL DEFAULT SAMPLE_PROF PRIVATE_SGA KERNEL DEFAULT SAMPLE_PROF FAILED_LOGIN_ATTEMPTS PASSWORD 3 SAMPLE_PROF PASSWORD_LIFE_TIME PASSWORD 15 SAMPLE_PROF PASSWORD_REUSE_TIME PASSWORD 15 SAMPLE_PROF PASSWORD_REUSE_MAX PASSWORD DEFAULT SAMPLE_PROF PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT SAMPLE_PROF PASSWORD_LOCK_TIME PASSWORD 5 SAMPLE_PROF PASSWORD_GRACE_TIME PASSWORD DEFAULT 16 rows selected. -- 위에서 만든 re_sample_prof의 내용을 조회 SQL> COL PROFILE FOR A15 SQL> SELECT * FROM DBA_PROFILES WHERE PROFILE = 'RE_SAMPLE_PROF' ; PROFILE RESOURCE_NAME RESOURCE LIMIT --------------- ------------------------------ -------- ---------- RE_SAMPLE_PROF COMPOSITE_LIMIT KERNEL DEFAULT RE_SAMPLE_PROF SESSIONS_PER_USER KERNEL DEFAULT RE_SAMPLE_PROF CPU_PER_SESSION KERNEL 1000 RE_SAMPLE_PROF CPU_PER_CALL KERNEL DEFAULT RE_SAMPLE_PROF LOGICAL_READS_PER_SESSION KERNEL DEFAULT RE_SAMPLE_PROF LOGICAL_READS_PER_CALL KERNEL DEFAULT RE_SAMPLE_PROF IDLE_TIME KERNEL 10 RE_SAMPLE_PROF CONNECT_TIME KERNEL 480 RE_SAMPLE_PROF PRIVATE_SGA KERNEL DEFAULT RE_SAMPLE_PROF FAILED_LOGIN_ATTEMPTS PASSWORD DEFAULT RE_SAMPLE_PROF PASSWORD_LIFE_TIME PASSWORD DEFAULT RE_SAMPLE_PROF PASSWORD_REUSE_TIME PASSWORD DEFAULT RE_SAMPLE_PROF PASSWORD_REUSE_MAX PASSWORD DEFAULT RE_SAMPLE_PROF PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT RE_SAMPLE_PROF PASSWORD_LOCK_TIME PASSWORD DEFAULT RE_SAMPLE_PROF PASSWORD_GRACE_TIME PASSWORD DEFAULT 16 rows selected.
3. 사용자에게 profile 적용시키고 확인
SQL> ALTER USER SMSUSER PROFILE SAMPLE_PROF ; User altered. SQL> ALTER USER SMSUSER PROFILE RE_SAMPLE_PROF ; User altered. SQL> SELECT USERNAME , PROFILE FROM DBA_USERS WHERE USERNAME = 'SMSUSER' ; USERNAME PROFILE ------------------------------ --------------- SMSUSER RE_SAMPLE_PROF
위 예에서 알 수 있듯이 여러 개의 프로파일을 적용시킬 수 없습니다.
그래서 처음부터 프로파일을 만들 때 원하는 파라미터를 전부 넣고 한꺼번에 만든 후 그것을 적용시켜야 합니다.
4. 사용 안 하는 profile 삭제하기
- 현재 사용 중인 re_sample_prof 프로파일을 삭제
SQL> DROP PROFILE RE_SAMPLE_PROF ; DROP PROFILE RE_SAMPLE_PROF * ERROR at line 1: ORA-02382: profile RE_SAMPLE_PROF has users assigned, cannot drop without CASCADE
앞에서 보듯이 현재 사용자에게 할당이 되어 있는 profile은 기본적으로 삭제가 안됩니다.
그러나 cascade 옵션으로 삭제하면 삭제할 수 있습니다.
그렇게 삭제하게 되면 해당 프로파일을 사용하던 사용자는 default profile을 사용하게 됩니다.
SQL> DROP PROFILE RE_SAMPLE_PROF CASCADE ; Profile dropped. SQL> SELECT USERNAME , PROFILE 2 FROM DBA_USERS 3 WHERE USERNAME = 'SMSUSER' 4 ; USERNAME PROFILE ------------------------------ --------------- SMSUSER DEFAULT
# privilege(권한) 관리하기
Profile은 사용자가 어떤 것들을 못하게 막는 것이 목적이지만 privilege는 사용자에게 어떤 것들을 하게 허락해주는 것으 목적으로 합니다.
일반적으로 OS는 계정을 생성하고 암호만 주면 서버에 접속이 간으하고 적절한 작업도 가능하지만 Oracle은 계정을 생성하고 암호를 설정하고 적절한 권한까지 줘야만 접속도 할 수 있고 작업도 할 수 있습니다.
privilege는 크게 system 관련 privilege와 object 관련 privilege로 나뉩니다.
1) System 관련 주요 privilege
2) SYSOPER / SYSDBA privilege
3) SYSTEM 관련 권한 할당하기 / 해제하기
- scott 사용자에게 create table, create session 권한을 할당 합니다.
SQL> GRANT CREATE TABLE, CREATE SESSION TO SCOTT ; Grant succeeded.
- scott 사용자에게 create table 권한을 해제합니다.
SQL> REVOKE CREATE TABLE FROM SCOTT; Revoke succeeded.
4) 사용자가 가지고 있는 권한 조회하기
- scott 사용자가 가지고 있는 모든 권한을 조회
SQL> SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'SCOTT' ; GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- SCOTT UNLIMITED TABLESPACE NO SCOTT CREATE SESSION NO
위에서 ADM 컬럼은 With admin option 옵션 여부를 나타냅니다
With admin option 이란 권한을 위임하는 기능인데 예를 들어서 DBA가 A 에게 Create Table이란 권한을 줄 때 그냥 주면 A 사용자는 create table을 수행하는 권한만 받게 되지만
GRANT CREATE TABLE TO A WITH ADMIN OPTION 이렇게 주게 되면 A 사용자는 또 다른 사용자에게 WITH ADMIN OPTION 과 함께 받은 CREATE TABLE이 이라는 권한을 마치 DBA처럼 할당해 줄수가 있게 됩니다.
또한 다른 사용자가 가진 create table이란 권한을 회수할 수도 있게 됩니다.
5) Object 관련 privilege
주로 DML과 연관이 많습니다.
즉 object를 select, insert, update, delete 등을 할 수 있는 권한을 말합니다.
6) Object 권한 할당하기 / 해제하기
- scott 사용자에게 smsuser가 만든 smstest 테이블을 select 할 수 있도록 함
SQL> GRANT SELECT ON SMSUSER.SMSTEST TO SCOTT ; Grant succeeded. -- 여러 테이블의 권한을 주고자 할때 테이블 개수만큼 쿼리 생성 SELECT 'GRANT SELECT ON SMSUSER.'||TABLE_NAME || ' TO SCOTT;' FROM ALL_TABLES WHERE OWNER = 'SMSUSER' ; -- 오라클 전체 테이블 1 SELECT 'GRANT SELECT ON '||TNAME||' TO SCOTT;' FROM TAB ; -- 오라클 전체 테이블 2 GRANT SELECT ANY TABLE TO SCOTT ;
- scott 사용자에게 smsuser가 만든 smstest 테이블을 update 할 수 있도록 하세요.
그리고 scott 사용자가 이 권한을 다른 사람에게 줄수 있는 권한도 주세요.
SQL> GRANT UPDATE ON SMSUSER.SMSTEST TO SCOTT WITH GRANT OPTION ; Grant succeeded.
- scott 사용자가 가진 smsuser의 smstest 테이블을 select 하는 권한을 해제 하세요.
SQL> REVOKE SELECT ON SMSUSER.SMSTEST FROM SCOTT ; Revoke succeeded.
- with grant option 이란?
System privilege에서 권한 위임을 하기 위해서는 권한 할당을 할 때 with admin option을 사용하였습니다.
그러나Object privilege에서는 같은 의미의 작업을 할 때 with grant option을 사용합니다.
차이점은 DBA가 A사용자에게 with admin option을 사용하여 권한을 주고 A 사용자가 다시 B 사용자에게 권한을 주었을때 DBA가 A 사용자에게서 권한을 해제해도 A 사용자로부터 권한을 받은 B 사용자는 권한이 해제가 안되지만 with grant option은 A 사용자의 권한을 해제하면 자동으로 A 사용자로부터 받은 B 사용자의 권한까지 해제되게 됩니다.
# Role 관리
사용자가 어떤 작업을 DB 내에서 수행하려면 반드시 그 권한을 가지고 있어야 한다고 앞에서 살펴보았습니다.
그런데 이 권한이 너무 많아서 grant 명령어로 권한 하나하나를 매번 할당하기에는 아주 힘이 드는 경우가 많이 있습니다.
이럴 경우를 위해서 만들어진 기능이 Role입니다.
Role이란 권한의 그룹이라고 생각하면 됩니다.
-- 1) Role 생성하기
SQL> CREATE ROLE TROLE ;
Role created.
-- 2) Role에 create session, create table 권한 할달 하기
SQL> GRANT CREATE SESSION, CREATE TABLE TO TROLE ;
Grant succeeded.
-- 3) Scott 사용자에게 trole 할당하기
SQL> GRANT TROLE TO SCOTT ;
Grant succeeded.
-- 4) 어떤 사용자가 어떤 Role을 사용하는지 확인하기
SQL> SELECT *
FROM DBA_ROLE_PRIVS
WHERE GRANTEE = 'SCOTT'
;
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
SCOTT RESOURCE NO YES
SCOTT DBA NO YES
SCOTT TROLE NO YES
SCOTT CONNECT NO YES
-- 사용자 권한 상세 정보 출력
SELECT R.GRANTEE, R.GRANTED_ROLE, RS.PRIVILEGE
FROM DBA_ROLE_PRIVS R, DBA_SYS_PRIVS S, ROLE_SYS_PRIVS RS
WHERE R.GRANTEE=S.GRANTEE
AND R.GRANTED_ROLE=RS.ROLE
AND R.GRANTEE = 'SCOTT' --IN ('TEST', 'SCOTT')
ORDER BY 1
;
GRANTEE GRANTED_ROLE PRIVILEGE
------------------------------ ------------------------------ ----------------------------------------
SCOTT DBA CHANGE NOTIFICATION
SCOTT DBA CHANGE NOTIFICATION
SCOTT DBA CHANGE NOTIFICATION
SCOTT DBA ADMINISTER ANY SQL TUNING SET
SCOTT DBA ADMINISTER ANY SQL TUNING SET
SCOTT DBA ADMINISTER ANY SQL TUNING SET
SCOTT DBA ALTER ANY SQL PROFILE
......
SCOTT RESOURCE CREATE TRIGGER
SCOTT RESOURCE CREATE TRIGGER
SCOTT RESOURCE CREATE TRIGGER
SCOTT DBA GRANT ANY ROLE
......
SCOTT CONNECT CREATE SESSION
SCOTT CONNECT CREATE SESSION
SCOTT CONNECT CREATE SESSION
SCOTT DBA ADMINISTER SQL TUNING SET
-- 5) 어떤 Role에 어떤 권한이 있는지 확인
SQL> SELECT *
2 FROM DBA_SYS_PRIVS
3 WHERE GRANTEE = 'CONNECT'
4 ;
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
CONNECT CREATE SESSION NO
SQL> SELECT *
2 FROM DBA_SYS_PRIVS
3 WHERE GRANTEE = 'RESOURCE'
4 ;
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
RESOURCE CREATE TRIGGER NO
RESOURCE CREATE SEQUENCE NO
RESOURCE CREATE TYPE NO
RESOURCE CREATE PROCEDURE NO
RESOURCE CREATE CLUSTER NO
RESOURCE CREATE OPERATOR NO
RESOURCE CREATE INDEXTYPE NO
RESOURCE CREATE TABLE NO
자료 출처 : 서진수 지음 - 원리부터 실무까지 오라클 백업과 복구
"DataBase / Oracle" 분류의 다른 글
| Oracle - 바인드 변수에 대하여(테스트) (0) | 2012/05/06 |
| Oracle - 디폴트 롤, DBA, CONNECT, RESOURCE (0) | 2012/04/27 |
| Oracle - 권한 및 롤 관리 (0) | 2012/04/27 |
| Oracle - SQL*PLUS의 SYSDBA 접근 제어 (0) | 2012/04/27 |
| Oracle - PFILE, SPFILE 에 관하여 (0) | 2012/04/27 |
| Oracle - Listener 포트 변경 (0) | 2012/04/27 |
| Oracle - 사용자 패스워드 정책 변경 (0) | 2012/04/03 |
| Oracle - SYS_CONTEXT 함수를 이용하여 접속 세션 정보 추출 (2) | 2011/12/13 |
| Oracle - ASSM(Automatic Segment Space Management) (0) | 2011/09/18 |
| Oracle - 사용자의 테이블 스페이스 검색및 이동 (0) | 2011/09/18 |
# 한번의 광고 클릭으로, 당신을 대신해서 불우이웃을 도울 기회가 많아집니다.
Response :
0 Trackback
,
0 Comment
Trackback URL : http://develop.sunshiny.co.kr/trackback/616