다양한 예제로 쉽게 배우는 오라클 SQL 과 PL/SQL 서진수 저

13장 사용자 관리를 배웁니다 1

13. 사용자 관리를 배웁니다 1. User와 Schema(스키마)에 대해서 알아봅니다 Schema : 특정 User 가 만든 Object 들의 모음을 의미 일반적으로 혼용해서 많이 사용함. 2

13. 사용자 관리를 배웁니다 2. PROFILE 관리하기 1) PASSWORD PROFILE 관련 파라미터 (1) FAILED_LOGIN_ATTEMPTS : 로그인 실패 할 경우 계정이 잠기는데 허용될 횟수를 지정 (2) PASSWORD_LOCK_TIME : 위 1번의 상황에서 계정이 잠길 기간 설정 (3) PASSWORD_LIFE_TIME : 암호를 변경 없이 사용할 수 있는 기간 설정 (4) PASSWORD_GRACE_TIME : 암호 변경 추가 시간 설정 (5) PASSWORD_REUSE_TIME : 동일한 암호를 쓸 수 없는 기간 설정 (6) PASSWORD_REUSE_MAX : 동일한 암호를 쓸 수 없는 횟수 설정 3

13. 사용자 관리를 배웁니다 (7) PASSWORD_VERIFY_FUNCTION : 암호를 복잡하게 만드는 함수 사용 * 암호는 최소한 4글자 이상 되어야 합니다. * 암호는 사용자 계정과 달라야 합니다. * 암호는 하나의 특수문자나, 알파벳 , 숫자가 포함되어야 합니다. * 암호는 이전 암호와 3글자 이상 달라야 합니다. 4

13. 사용자 관리를 배웁니다 예제 1. Password 관련 PROFILE 생성하기 - 조건 1: 로그인 시도 3회 실패 시 계정을 5일 동안 사용 못하게 할 것 - 조건 2: 계정의 암호는 15일에 한번씩 변경하게 할 것 - 조건 3: 동일한 암호는 15일 동안 사용 못하게 할 것 SYS>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 ; 5

13. 사용자 관리를 배웁니다 2) RESOURCE PROFILE 관련 파라미터 RESOURCE_LIMIT = true ALTER SYSTEM SET RESOURCE_LIMIT = true; - CPU_PER_SESSION : 1 세션당 CPU 를 쓸 수 있는 시간 지정(1/100 초) - SESSIONS_PER_USER : 1 유저당 동시 접속 가능한 세션 수 지정 - CONNECT_TIME : 접속 가능한 시간 지정 ( 분 단위 ) - IDLE_TIME : 휴면 시간 지정 ( 분 단위 ) - LOGICAL_READS_PER_SESSION : 1 세션에서 사용 가능한 Block 수 지정 - PRIVATE_SGA : MTS / Shared Server 의 경우 세션당 SGA 사용 가능량 지정 - CPU_PER_CALL : 1 세션당 사용 가능한 CPU 시간 지정 - LOGICAL_READS_PER_CALL : 1 call 당 사용 가능한 Block 수 지정 6

13. 사용자 관리를 배웁니다 * 예제 2: RESOURCE 관련 PROFILE 만들기 SYS>ALTER SYSTEM SET RESOURCE_LIMIT=true ; - 조건 1: 1명당 연속적으로 CPU를 사용할 수 있는 시간을 10초로 제한할 것. - 조건 2: 하루 중 8시간만 DB에 접속 가능하게 할 것. - 조건 3: 10분 동안 사용하지 않으면 강제로 접속을 끊을 것 SYS>CREATE PROFILE RE_SAMPLE_PROF LIMIT 2 CPU_PER_SESSIN 1000 3 CONNECT_TIME 480 4 IDLE_TIME 10 ; 7

13. 사용자 관리를 배웁니다 3) 사용자에게 PROFILE 할당하기 SYS>SELECT username "사용자명" , profile "적용 프로파일" 2 FROM dba_users 3 WHERE username=’SCOTT’ ; (2) 해당 PROFILE 에 어떤 내용이 있는지 확인하기 SYS>SELECT * FROM dba_profiles 2 WHERE PROFILE='SAMPLE_PROF' ; SYS> SELECT * FROM dba_profiles 2 WHERE profile='RE_SAMPLE_PROF' ; 8

13. 사용자 관리를 배웁니다 4) 사용자에게 PROFILE 적용시키고 확인하기 9 SYS>ALTER USER scott PROFILE sample_prof; SYS>ALTER USER scott PROFILE re_sample_prof; SYS>SELECT username, profile 2 FROM dba_users 3 WHERE username='SCOTT'; USERNAME PROFILE ------------------ -------------------------- SCOTT RE_SAMPLE_PROF 9

13. 사용자 관리를 배웁니다 (4) 사용 안 하는 PROFILE 삭제하기 SYS>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 SYS>DROP PROFILE re_sample_prof CASCADE; SYS>SELECT username, PROFILE 2 FROM dba_users 3 WHERE username='SCOTT'; 10

13. 사용자 관리를 배웁니다 3. PRIVILEGE (권한) 관리하기 1) System 관련 주요 PRIVILEGE 11 대 분 류 PRIVILEGE 설 명 INDEX CREATE ANY INDEX 소유자에 상관없이 모든 테이블에 인덱스를 생성할 수 있는 권한 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 소유자에 상관없이 모든 사용자의 테이블에 데이터를 삽입 할 수 있는 권한 11

13. 사용자 관리를 배웁니다 12 SESSION CREATE SESSION 서버에 접속할 수 있는 권한 ALTER SESSION 접속 상태에서 환경값을 변경할 수 있는 권한 RESTRICTED SESSION Restricted 모드로 open 된 DB에 접속할 수 있는 권한 TABLESPACE CREATE TABLESPACE Tablespace를 만들 수 있는 권한 ALTER TABLESPACE Tablespace를 수정 할 수 있는 권한 DROP TABLESPACE Tablespace를 삭제 할 수 있는 권한 UNLIMITED TABLESPACE Tablespace 사용용량을 무제한으로 허용 하는 권한. 즉 quota 옵션 적용을 받지 않게 됨 12

13. 사용자 관리를 배웁니다 2) SYSOPER / SYSDBA PRIVILEGE 13 PRIVILEGE 할 수 있는 일 Startup / shutdown Alter database mount / open Alter database backup control file to ….. Recover database Alter database archivelog Restricted session SYSDBA SYSOPER PRIVILEGE with admin option Create database Alter tablespace … begin backup / end backup Recover database until 13

13. 사용자 관리를 배웁니다 3) SYSTEM 관련 권한 할당하기 / 해제하기 4) 사용자가 가지고 있는 권한 조회하기 14 SYS> GRANT CREATE TABLE, CREATE SESSION TO SCOTT; SYS> GRANT CREATE TABLE, CREATE SESSION TO SCOTT WITH ADMIN OPTION; SYS> REVOKE CREATE TABLE FROM SCOTT ; 4) 사용자가 가지고 있는 권한 조회하기 SYS>SELECT * FROM DBA_SYS_PRIVS 2 WHERE grantee='SCOTT'; 14

13. 사용자 관리를 배웁니다 - Object 관련 PRIVILEGE – 주로 DML 과 관련된 권한들임 -사용 예제 1: SCOTT 사용자에게 HR 사용자가 만든 EMPLOYEES 테이블을 SELECT 할 수 있도록 권한을 할당하세요. SYS> GRANT SELECT ON HR.EMPLOYEES TO SCOTT ; -사용 예제 2: SCOTT 사용자에게 HR 가 만든 EMPLOYEES 테이블을 UPDATE 할 수 있도록 하세요.그리고 SCOTT 사용자가 이 권한을 다른 사람에게 줄 수 있는 권한도 주세요. SYS> GRANT UPDATE ON HR.EMPLOYEES TO SCOTT WITH GRANT OPTION ; 15

13. 사용자 관리를 배웁니다 4. Role (롤) 관리하기 1) ROLE 생성하기 SYS>CREATE ROLE trole; 2) ROLE 에 CREATE SESSION , CREATE TABLE 권한 할당하기 SYS>GRANT CREATE SESSION , CREATE TABLE TO trole ; 3) SCOTT 사용자에게 trole 할당하기 SYS>GRANT trole TO SCOTT ; 4) 어떤 사용자가 어떤 ROLE 을 사용하는지 확인하기 SYS>SELECT * FROM dba_role_privs WHERE grantee='SCOTT'; 16

13. 사용자 관리를 배웁니다 5) 어떤 ROLE 에 어떤 권한이 있는지 확인하기 17 SYS>SELECT * FROM dba_sys_privs WHERE grantee ='CONNECT‘; SYS>SELECT * FROM dba_sys_privs WHERE grantee ='RESOURCE‘; 17