ORACLE - DAY 10

BUMSOO·2024년 8월 9일

schema(스키마)

유저이름
특정 유저와 관련된 오브젝트를 의미

유저생성

CREATE USER 유저이름
INDENTIFIED BY 패스워드
DEFAULT TABLESPACE 테이블스페이스 이름
TEMPORARY TABLESPACE 임시테이블스페이스 이름
QUOTA UNLIMITED ON 테이블스페이스
QUOTA 10M ON 테이블스페이스2
PASSWORD EXPIRE -- 비밀번호를 만기시켜 새롭게 설정하도록 함
ACCOUNT [UNLOCK(기본값) | LOCK]
PROFILE [PROFILE이름 | DEFAULT(기본값)]

유저삭제

DROP USER 유저이름 CASCADE;

CASCADE : 유저가 생성한 객체들 먼저 삭제
현재 접속한 유저는 삭제할 수 없다.

ROLE

롤은 관련성이 있는 권한들을 하나로 묶어서 관리하는 객체이다.
권한관리의 편리성

예) 개발자들에게 부여할 시스템 권한
-CREATE PROCEDURE
-CREATE TRIGGER
-CREATE VIEW

DBA SESSION

--role 생성
CREATE ROLE prog;

--데이터베이스에 생성된 롤정보
SELECT * FROM dba_roles;

--role에 시스템 권한 부여
GRANT create procedure, create trigger, create view TO prog;

--role에 부여한 시스템 권한 확인
SELECT * FROM dba_sys_privs WHERE grantee = 'PROG';

--role에 객체권한을 부여
GRANT select ON hr.employees TO prog;

--role에 부여한 객체권한 확인
SELECT * FROM dba_tab_privs WHERE grantee = 'PROG';

--role을 유저에게 권한부여
GRANT prog TO insa;

--유저한테 부여한 role을 확인
SELECT * FROM dba_role_privs WHERE grantee = 'INSA';

INSA SESSION

role은 접속후에 받았으면 꼭 다시 접속해야한다.
role은 처음 접속시에 받은 롤이 활성화 된다.

--role에 부여한 시스템권한 확인
SELECT * FROM role_sys_privs;

--role에 부여한 객체권한 확인
SELECT * FROM role_tab_privs;

--직접 받은 시스템권한 확인
SELECT * FROM user_sys_privs;

--직접 받은 객체권한, 내가 부여한 객체권한 확인
SELECT * FROM user_tab_privs;

DBA SESSION

--role 생성
CREATE ROLE mgr;

--mgr 롤에 select any table 시스템 권한 부여
GRANT select any table TO mgr;
SELECT * FROM dba_sys_privs WHERE grantee = 'MGR';

--mgr 롤을 insa에 부여해주세요
GRANT mgr TO insa;

SELECT * FROM dba_role_privs WHERE grantee = 'INSA';

INSA SESSION

--부여받은 role 확인
SELECT * fROM session_roles;

--내가 받은 롤에 시스템권한 확인
SELECT * FROM role_sys_privs;

--내가 받은 롤에 객체권한 확인
SELECT * FROM role_tab_privs;

--sys가 보유한 딕셔너리 테이블 조회해보기
SELECT * FROM sys.user$;

select any table 이라도 sys가 보유한 딕셔너리 테이블은 조회할 수 없다.

-- 일반적으로 role을 유저한테 부여하면 default role로 활성화 된다.
SELECT * FROM user_role_privs;

DBA가 role 비활성화

DBA SESSION

-- insa 유저가 받은 롤중에 mgr롤은 제외한 후 다른 롤들은 활성화 한다.
ALTER USER insa DEFAULT ROLE ALL EXCEPT mgr;

SELECT * FROM dba_role_privs WHERE grantee = 'INSA';

ISNA SESSION

SELECT * FROM session_roles;

특정 role을 비활성화 했어도 해당 유저가 현재 접속해 있는 상태라면 role은 활성화 되어있다. 유저가 재접속 해야지만 해당 role이 비활성화 된다.

DBA SESSION

-- insa 유저가 받은 롤 전부를 비활성화 한다.
ALTER USER insa DEFAULT ROLE NONE;

SELECT * FROM dba_role_privs WHERE grantee = 'INSA';

-- insa 유저가 받은 롤 전부를 활성화 한다.
ALTER USER insa DEFAULT ROLE ALL;

SELECT * FROM dba_role_privs WHERE grantee = 'INSA';

INSA SESSION

select * FROM role_sys_privs;

CREATE OR REPLACE VIEW dept_view
AS 
SELECT * FROM hr.departments;

- 부여받은 role안에 select any table 권한이 있어도 뷰 생성 안에서 서브쿼리 테이블을 조회는 할수 없다.
- 뷰 생성시 수행하는 SELECT 문장에 해당하는 테이블의 객체권한은 별도로 받아서 수행해야 한다.

DBA SESSION

--직접적인 객체 권한 부여
GRANT select ON hr.departments TO insa;

INSA SESSION

--직접 부여받은 객체권한으로 뷰 생성 가능하다
CREATE OR REPLACE VIEW dept_view
AS 
SELECT * FROM hr.departments;

SELECT text FROM user_views;

DBA SESSION

--객체권한 회수
REVOKE select ON hr.departments FROM insa;

INSA SESSION

SELECT * FROM dept_view;

- view의 owner 일지라도 해당 view가 access하는 객체에 대한 직접적인 권한이 없으면 view를 조회 할수가 없다.

user가 직접 role 비활성화

-- 전체 롤 비활성화
SET ROLE NONE;

-- 전체 롤 활성화
SET ROLE ALL;

-- 특정 롤만 활성화
SET ROLE prog;

-- 특정 롤 제외하고 활성화
SET ROLE ALL EXCEPT MGR;

role 생성시에 패스워드 설정

DBA SESSION

CREATE ROLE mgr
IDENTIFIED BY oralce;

GRANT select any table TO mgr;

GRANT mgr TO insa;

INSA SESSION

SELECT * FROM SESSION_ROLES;

- 처음 조회 했을때는 부여받은 role이 조회되지 않는다

SET ROLE prog, mgr IDENTIFIED BY oralce;

SELECT * FROM SESSION_ROLES;

- 그냥 SET ROLE ALL을 하게 되면 패스워드가 설정되어있는 ROLE은 설정되지 않는다. 패스워드를 같이 입력해줘야 한다.

role 패스워드 지우기

DBA SESSION

ALTER ROLE mgr NOT IDENTIFIED;

INSA SESSION

SELECT * FROM user_role_privs;

PL/SQL

Definer's right

만든 사람 입장에서 프로그램을 수행
기본으로 definer's right 모드로 생성된다.

hr session

CREATE OR REPLACE PROCEDURE insert_emp1(
    p_id IN number,
    p_name IN varchar2,
    p_sal IN number)
IS 
BEGIN
    INSERT INTO emp(employee_id, last_name, salary)
    VALUES (p_id, p_name, p_sal);
END insert_emp1;
/

green 유저에게 권한부여

GRANT EXECUTE hr.insert_emp1 TO green;

green session

EXECUTE hr.insert_emp1(1, '김범수', 100000);

- definer's right 프로그램이었기 때문에 green 세션에서 수행했더라도 hr 유저의 emp 테이블로 insert된다.

- hr.emp 테이블에 insert 하기 위해서는 원래같으면 emp 테이블에 대한 select 객체 권한이 필요하겠지만, 프로그램을 실행하게 되면 간접 access를 하기 때문에 따로 권한이 필요없다. 프로그램을 실행할 권한만 필요하다.

Invoker's right

호출자 입장에서 프로그램을 수행
AUTHID CURRENT_USER를 명시해줘야 한다.

hr session

CREATE OR REPLACE PROCEDURE insert_emp2(
    p_id IN number,
    p_name IN varchar2,
    p_sal IN number)
AUTHID CURRENT_USER -- Invoker's right
IS 
BEGIN
    INSERT INTO emp(employee_id, last_name, salary)
    VALUES (p_id, p_name, p_sal);
END insert_emp2;
/

green 유저에게 권한부여

GRANT EXECUTE hr.insert_emp1 TO green;

green session

EXECUTE hr.insert_emp2(2,'james',50000);

- invoker's right 프로그램은 호출자 입장에서 사용되기 때문에 green이 가지고 있는 emp 테이블에 데이터가 insert 되게 된다.

- green 유저가 속해있는 테이블스페이스에 대한 quota 값이 없으면 오류가 발생한다. sys에서 green 유저에 대한 quota값을 수정해줘야한다.

ALTER USER green 
QUOTA 10M ON users;

- green 유저가 emp테이블이 없는 상태에서 프로그램을 실행하면 오류가 발생된다.

APPLICATION ROLE 생성

DBA SESSION

응용프로그램 및 롤 생성

CREATE OR REPLACE PROCEDURE priv_mgr
AUTHID CURRENT_USER
IS
BEGIN
    IF to_char(sysdate,'hh24:mi') BETWEEN '15:05' AND '15:10' THEN
        dbms_session.SET_ROLE('sec_app_role');
    ELSE
        dbms_session.SET_ROLE('NONE');
    END IF;
END priv_mgr;
/

-- 응용프로그램 롤 생성
CREATE ROLE sec_app_role
IDENTIFIED USING priv_mgr;

- dbms_session 패키지 안에 SET_ROLE 프로그램을 통해 특정 role을 활성화 시킬 수 있다.
- AUTHID CURRENT_USER 를 통해 Invoker's right 모드로 만들어 호출자 입장에서 실행하도록 한다.

권한 부여

GRANT select any dictionary TO sec_app_role;

GRANT execute ON priv_mgr TO insa;

- role을 직접적으로 유저에게 grant 하는게 아니라
프로그램을 실행시킬 execute 권한을 grant하여 해당 조건에만 role을 활성화 시키게 구성함

INSA SESSION

-- 프로그램 실행
EXECUTE sys.priv_mgr;

-- 받은 role 확인
SELECT * FROM session_roles;

-- select any dictionary  권한으로 조회 가능
SELECT * FROM sys.obj$;

PROFILE 관리

리소스 소비를 제어하고 계정 암호 상태 및 암호 만료 관리
RESOURCE_TYPE이 KERNEL은 리소스 관리
RESOURCE_TYPE이 PASSWORD는 패스워드 관리

SELECT * FROM dba_profiles WHERE profile = 'DEFAULT' AND resource_type = 'PASSWORD';

SELECT * FROM dba_profiles WHERE profile = 'DEFAULT' AND resource_type = 'KERNEL';

FAILED_LOGIN_ATTEMPTS : 암호 오류 허용 횟수
PASSWORD_LOCK_TIME (일 단위) : 암호 오류 허용 횟수를 넘었다면 자동으로 계정이 잠김 일수

PASSWORD_LIFE_TIME (일 단위) : 암호 유효기간
PASSWORD_GRACE_TIME (일 단위) : 암호 만료 이후 암호 변경까지 유예기간

PASSWORD_REUSE_TIME (일 단위) : 주어진 일 수 동안에는 암호를 재사용 할수 없도록 지정
PASSWORD_REUSE_MAX : 현재 암호를 재사용 하기 위해 필요한 암호 변경 횟수를 지정한다.

PASSWORD_REUSE_TIME PASSWORD UNLIMITED
PASSWORD_REUSE_MAX PASSWORD UNLIMITED
- 이전 암호는 무한으로 재사용 할 수 있다.

INACTIVE_ACCOUNT_TIME : 지정된 일수 동안 데이터베이스에 로그인 하지 않은 사용자에 대해서 잠그겠다.
휴면 계정으로 만든다고 보면 될거같다.
최소값 15일 ~ 최대값 24855일

PASSWORD_VERIFY_FUNCTION : 암호의 복합성 검사, 함수로 구현

오라클이 제공하는 샘플 스크립트 위치 $ORACLE_HOME/rdbms/admin/utlpwdmg.sql

sys 계정에서 해당 스크립트 실행

- 해당 스크립트를 실행하면 오라클에서 제공하는 password_verify_function 샘플 함수를 사용한다.

PROFILE 변경

PASSWORD_VERIFY_FUNCTION 변경

ALTER PROFILE DEFAULT LIMIT 
PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION_ITWILL;

FAILED_LOGIN_ATTEMPTS, PASSWORD_LOCK_TIME 변경

ALTER PROFILE DEFAULT LIMIT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME UNLIMITED;

로그인시 유효횟수 초과 할 경우 계정 LOCK 걸리는데 DBA가 해제 시켜줘야 한다.

SELECT * FROM dba_users WHERE username = 'INSA';

ALTER USER insa ACCOUNT UNLOCK;

PASSWORD_VERIFY_FUNCTION 프로파일일 적용하고 싶지 않으면 null로 변경하면 된다.

ALTER PROFILE DEFAULT LIMIT 
PASSWORD_VERIFY_FUNCTION NULL;

BUMSOO

이전 포스트

ORACLE - DAY 9

다음 포스트

ORACLE - DAY 10

schema(스키마)

유저생성

유저삭제

ROLE

DBA SESSION

INSA SESSION

DBA SESSION

INSA SESSION

DBA가 role 비활성화

DBA SESSION

ISNA SESSION

DBA SESSION

INSA SESSION

DBA SESSION

INSA SESSION

DBA SESSION

INSA SESSION

user가 직접 role 비활성화

role 생성시에 패스워드 설정

DBA SESSION

INSA SESSION

role 패스워드 지우기

DBA SESSION

INSA SESSION

PL/SQL

Definer's right

hr session

green 유저에게 권한부여

green session

Invoker's right

hr session

green 유저에게 권한부여

green session

APPLICATION ROLE 생성

DBA SESSION

INSA SESSION

PROFILE 관리

PROFILE 변경

ORACLE - DAY 9

ORACLE - DAY 11

0개의 댓글