27. sys session
  SELECT * FROM dba_roles;
ROLE                           PASSWORD AUTHENTICAT
------------------------------ -------- -----------
PROG                           NO       NONE
...

SELECT * FROM dba_sys_privs WHERE grantee = 'CONNECT';
SELECT * FROM dba_sys_privs WHERE grantee = 'RESOURCE';
SELECT * FROM dba_sys_privs WHERE grantee = 'DBA';
GRANTEE                        PRIVILEGE                                ADMIN_OPTION
------------------------------ ---------------------------------------- -------------
DBA                            SELECT ANY TABLE                         YES
...

ANY : 남의 영역까지 가서 작업해줄 수 있다 -> 권한부여 주의

SELECT * FROM hr.employees;
ㄴ SELECT ANY TABLE 시스템권한이 있어 다른 유저 소유의 테이블 조회 가능

CREATE ROLE mgr;

GRANT SELECT ANY TABLE TO  mgr;

SELECT * FROM dba_sys_privs WHERE grantee = 'MGR';
GRANTEE                        PRIVILEGE                                ADMIN_OPTION
------------------------------ ---------------------------------------- -------------
MGR                            SELECT ANY TABLE                         NO

GRANT mgr TO insa;

SELECT * FROM dba_role_privs WHERE grantee = 'INSA';
GRANTEE                        GRANTED_ROLE                   ADMIN_OPTION  DEFAULT_ROLE
------------------------------ ------------------------------ ------------- -------------
INSA                           PROG                           NO            YES
INSA                           MGR                            NO            YES

★ ANY 성격의 시스템권한은 별도로 관리하자. 날짜정보 필수
28. insa session
  conn insa/oracle
ㄴ ROLE은 접속시점에 활성화되기 때문에 재접속해야 적용된다.

SELECT * FROM session_roles;
ROLE
------------------------------
PROG
MGR

SELECT * FROM role_sys_privs;
ROLE                           PRIVILEGE                          ADMIN_OPTION
------------------------------ ----------------------------------- -------------
MGR                            SELECT ANY TABLE                    NO
PROG                           CREATE TRIGGER                      NO
PROG                           CREATE VIEW                         NO
PROG                           CREATE PROCEDURE                    NO

SELECT * FROM role_tab_privs;
ROLE            OWNER   TABLE_NAME                     COLUMN_NAME  PRIVILEGE  GRANTABLE
--------------- ------- ------------------------------ ------------ ---------- ----------
PROG            HR      DEPARTMENTS                                 SELECT     NO

SELECT * FROM hr.employees;
SELECT * FROM hr.locations;
ㄴ 객체권한이 없어도 다른 유저가 소유한 테이블 조회 가능

SELECT * FROM sys.user$;
ORA-00942: table or view does not exist
ㄴ 단, sys 소유의 딕셔너리 테이블은 조회불가

SELECT * FROM user_role_privs;
USERNAME   GRANTED_ROLE                   ADMIN_OPTION  DEFAULT_ROLE  OS_GRANTED
---------- ------------------------------ ------------- ------------- -----------
INSA       MGR                            NO            YES           NO
INSA       PROG                           NO            YES           NO
29. sys session
  # 일반적으로 role을 유저한테 부여하면 DEFAULT ROLE로 활성화 된다.
SELECT * FROM dba_role_privs WHERE grantee = 'INSA';
GRANTEE    GRANTED_ROLE    ADMIN_OPTION  DEFAULT_ROLE
---------- --------------- ------------- -------------
INSA       PROG            NO            YES
INSA       MGR             NO            YES

# insa 유저가 받은 롤 중에 mgr롤은 제외한 후 다른 롤들은 활성화
ALTER USER insa DEFAULT ROLE ALL EXCEPT mgr;
ㄴ revoke + grant 작업 대체

SELECT * FROM dba_role_privs WHERE grantee = 'INSA';
GRANTEE    GRANTED_ROLE    ADMIN_OPTION  DEFAULT_ROLE
---------- --------------- ------------- -------------
INSA       PROG            NO            YES
INSA       MGR             NO            NO
30. insa session
  conn insa/oracle

SELECT * FROM session_roles;
ROLE
---------------
PROG
ㄴ 활성화된 롤만 나온다.

SELECT * FROM hr.locations;
ORA-00942: table or view does not exist
ㄴ 권한이 없어 조회 불가

SELECT * FROM user_role_privs;
USERNAME   GRANTED_ROLE    ADMIN_OPTION  DEFAULT_ROLE  OS_GRANTED
---------- --------------- ------------- ------------- -----------
INSA       MGR             NO            NO            NO
INSA       PROG            NO            YES           NO
31. sys session
  ALTER USER insa DEFAULT ROLE NONE;
ㄴ insa 유저의 롤 모두 비활성화

SELECT * FROM dba_role_privs Where grantee = 'INSA';
GRANTEE    GRANTED_ROLE    ADMIN_OPTION  DEFAULT_ROLE
---------- --------------- ------------- -------------
INSA       PROG            NO            NO
INSA       MGR             NO            NO

32. insa session
  conn insa/oracle

SELECT * FROM session_roles;
no rows selected
ㄴ 모두 비활성화되어 나오지 않는다.

SELECT * FROM user_role_privs;
USERNAME   GRANTED_ROLE    ADMIN_OPTION  DEFAULT_ROLE  OS_GRANTED
---------- --------------- ------------- ------------- -----------
INSA       MGR             NO            NO            NO
INSA       PROG            NO            NO            NO

SELECT * FROM role_sys_privs;
ROLE            PRIVILEGE            ADMIN_OPTION
--------------- -------------------- -------------
MGR             SELECT ANY TABLE     NO
PROG            CREATE TRIGGER       NO
PROG            CREATE VIEW          NO
PROG            CREATE PROCEDURE     NO

SELECT * FROM role_tab_privs;
ROLE            OWNER   TABLE_NAME      COLUMN_NAME  PRIVILEGE            GRANTABLE
--------------- ------- --------------- ------------ -------------------- ----------
PROG            HR      DEPARTMENTS                  SELECT               NO

>> ROLE 권한 자체를 삭제한게 아니라서 롤안의 권한은 확인가능하다.
33. sys session
  ALTER USER insa DEFAULT ROLE ALL EXCEPT prog;

SELECT * FROM dba_role_privs WHERE grantee = 'INSA';
GRANTEE    GRANTED_ROLE    ADMIN_OPTION  DEFAULT_ROLE
---------- --------------- ------------- -------------
INSA       PROG            NO            NO
INSA       MGR             NO            YES

34. insa session
  conn insa/oracle

SELECT * FROM session_roles;
ROLE
---------------
MGR

SELECT * FROM user_role_privs;
USERNAME   GRANTED_ROLE    ADMIN_OPTION  DEFAULT_ROLE  OS_GRANTED
---------- --------------- ------------- ------------- -----------
INSA       MGR             NO            YES           NO
INSA       PROG            NO            NO            NO

SELECT * FROM role_sys_privs;
ROLE            PRIVILEGE            ADMIN_OPTION
--------------- -------------------- -------------
MGR             SELECT ANY TABLE     NO
PROG            CREATE TRIGGER       NO
PROG            CREATE VIEW          NO
PROG            CREATE PROCEDURE     NO

SELECT * FROM role_tab_privs;
ROLE            OWNER   TABLE_NAME      COLUMN_NAME  PRIVILEGE            GRANTABLE
--------------- ------- --------------- ------------ -------------------- ----------
PROG            HR      DEPARTMENTS                  SELECT               NO
35. sys session
  ALTER USER insa DEFAULT ROLE ALL;

SELECT * FROM dba_role_privs WHERE grantee = 'INSA';
GRANTEE    GRANTED_ROLE    ADMIN_OPTION  DEFAULT_ROLE
---------- --------------- ------------- -------------
INSA       PROG            NO            YES
INSA       MGR             NO            YES
36. insa session
  conn insa/oracle

SELECT * FROM session_roles;
ROLE
---------------
PROG
MGR

SELECT * FROM user_role_privs;
USERNAME   GRANTED_ROLE    ADMIN_OPTION  DEFAULT_ROLE  OS_GRANTED
---------- --------------- ------------- ------------- -----------
INSA       MGR             NO            YES           NO
INSA       PROG            NO            YES           NO

SELECT * FROM role_sys_privs;
ROLE            PRIVILEGE            ADMIN_OPTION
--------------- -------------------- -------------
MGR             SELECT ANY TABLE     NO
PROG            CREATE TRIGGER       NO
PROG            CREATE VIEW          NO
PROG            CREATE PROCEDURE     NO

SELECT * FROM role_tab_privs;
ROLE            OWNER   TABLE_NAME      COLUMN_NAME  PRIVILEGE            GRANTABLE
--------------- ------- --------------- ------------ -------------------- ----------
PROG            HR      DEPARTMENTS                  SELECT               NO

SELECT * FROM hr.departments;
SELECT * FROM hr.employees;
ㄴ 조회 가능

CREATE VIEW emp_view AS SELECT * FROM hr.employees;
ㄴ mgr 롤 안에 SELECT ANY TABLE 권한이 있어 다른 테이블 조회가 가능하다.

SELECT * FROM emp_view;

SELECT text FROM user_views WHERE view_name = 'EMP_VIEW';   -- veiw 정보
TEXT
--------------------------------------------------------------------------------
select "EMPLOYEE_ID","FIRST_NAME","LAST_NAME","EMAIL","PHONE_NUMBER","HIRE_DATE"
,"JOB_ID","SALARY","COMMISSION_PCT","MANAGER_ID","DEPARTMENT_ID" from hr.employe
es
37. sys session
  REVOKE mgr FROM insa;

SELECT * FROM dba_role_privs WHERE grantee = 'INSA';
GRANTEE    GRANTED_ROLE    ADMIN_OPTION  DEFAULT_ROLE
---------- --------------- ------------- -------------
INSA       PROG            NO            YES

DROP ROLE mgr;

# 비밀번호를 이용해서 role 생성
CREATE ROLE mgr IDENTIFIED BY ORACLE;

GRANT SELECT ANY TABLE TO mgr;

SELECT * FROM dba_sys_privs WHERE grantee = 'MGR';
GRANTEE    PRIVILEGE                                ADMIN_OPTION
---------- ---------------------------------------- -------------
MGR        SELECT ANY TABLE                         NO

GRANT mgr TO insa;

SELECT * FROMdba_role_privs WHERE  grantee = 'INSA';
GRANTEE    GRANTED_ROLE    ADMIN_OPTION  DEFAULT_ROLE
---------- --------------- ------------- -------------
INSA       PROG            NO            YES
INSA       MGR             NO            YES
38. insa session
  conn insa/oracle

SELECT * FROM session_roles;
ROLE
---------------
PROG
ㄴ MGR 롤이 보이지 않는다!!

SELECT * FROM session_privs;
PRIVILEGE
--------------------
CREATE SESSION
CREATE TABLE
CREATE VIEW
CREATE PROCEDURE
CREATE TRIGGER
ㄴ SELECT ANY TABLE 권한이 보이지 않는다!!

SELECT * FROM user_role_privs;
USERNAME   GRANTED_ROLE    ADMIN_OPTION  DEFAULT_ROLE  OS_GRANTED
---------- --------------- ------------- ------------- -----------
INSA       MGR             NO            NO            NO
INSA       PROG            NO            YES           NO
ㄴ mgr 롤의 권한은 받았지만 비활성화 상태
-> 패스워드를 적용하여 생성된 롤은 유저가 직접 활성화 해야한다. (패스워드 아는 사람만 이용해라)

# SET 명령어를 이용하여 role 활성화
SET ROLE mgr;
ORA-01979: missing or invalid password for role 'MGR'

SET ROLE mgr IDENTIFIED BY ORACLE;
ㄴ 비밀번호 설정과 함께 활성화

SELECT * FROM session_roles;
ROLE
---------------
MGR
ㄴ PROG는 어디감?? -> SET을 이용하여 활성화 하면서 같이 설정하지 않은 나머지는 비활성화 되었다.

SELECT * FROM user_role_privs;
USERNAME   GRANTED_ROLE    ADMIN_OPTION  DEFAULT_ROLE  OS_GRANTED
---------- --------------- ------------- ------------- -----------
INSA       MGR             NO            NO            NO
INSA       PROG            NO            YES           NO
ㄴ 활성화해도 패스워드가 있다면 DEFAULT_ROLE : NO

SET ROLE prog , mgr IDENTIFIED By Oracle;
ㄴ prog 활성화
ㄴ mgr 비밀번호 설정하면서 활성화

SELECT * FROM session_roles;
ROLE
---------------
MGR
PROG

SELECT * FROM user_role_privs;
USERNAME   GRANTED_ROLE    ADMIN_OPTION  DEFAULT_ROLE  OS_GRANTED
---------- --------------- ------------- ------------- -----------
INSA       MGR             NO            NO            NO
INSA       PROG            NO            YES           NO
ㄴ 활성화해도 패스워드가 있다면 DEFAULT_ROLE : NO

# 내가 받은 롤을 모두 비활성화
SET ROLE NONE;

SELECT * FROM session_roles;
no rows selected

SELECT * FROM user_role_privs;
USERNAME   GRANTED_ROLE    ADMIN_OPTION  DEFAULT_ROLE  OS_GRANTED
---------- --------------- ------------- ------------- -----------
INSA       MGR             NO            NO            NO
INSA       PROG            NO            YES           NO
ㄴ 권환회수가 아닌 비활성화된거라 ROLE 정보 조회가 된다.

SELECT * FROM role_sys_privs;
ROLE            PRIVILEGE            ADMIN_OPTION
--------------- -------------------- -------------
MGR             SELECT ANY TABLE     NO
PROG            CREATE TRIGGER       NO
PROG            CREATE VIEW          NO
PROG            CREATE PROCEDURE     NO

SET ROLE ALL EXCEPT mgr;
ㄴ ROLE  모두 활성화하지만 mgr은 제외

SELECT * FROM session_roles;
ROLE
---------------
PROG
39. sys session
  # ROLE 비밀번호 삭제
ALTER ROLE mgr NOT IDENTIFIED;
40. insa session
  conn insa/oracle

SELECT * FROM session_roles;
ROLE
---------------
PROG
MGR

SELECT * FROM user_role_privs;
USERNAME   GRANTED_ROLE    ADMIN_OPTION  DEFAULT_ROLE  OS_GRANTED
---------- --------------- ------------- ------------- -----------
INSA       MGR             NO            YES           NO
INSA       PROG            NO            YES           NO

SET ROLE NONE;

SET ROLE ALL;

>> 비밀번호 걸리는게 없어서 일괄 설정에 오류가 없다.





■ PL/SQL

Definer's right 만든사람 입장에서 프로그램을 수행
Invoker's right 호출자 입장에서 프로그램을 수행

 

1. SYS SESSION
  CREATE USER green IDENTIFIED BY oracle;

GRANT CONNECT, RESOURCE TO green;

DROP TABLE hr.emp PURGE;
ㄴ DROP ANY TABLE 권한있어 가능

CREATE TABLE hr.emp
AS SELECT employee_id,last_name,salary FROM hr.employees WHERE 1 = 2;

CREATE TABLE green.emp
AS SELECT employee_id,last_name,salary FROM hr.employees WHERE 1 = 2;
ㄴ CREATE ANY TABLE 시스템 권한이 있어 생성 가능
2. HR SESSION
  # Definer's right
CREATE OR REPLACE PROCEDURE insert_emp1
    (p_id IN emp.employee_id%type,
    p_name IN emp.last_name%type,
    p_sal IN emp.salary%type)
IS
BEGIN
    INSERT INTO emp (employee_id,last_name,salary) VALUES (p_id,p_name,p_sal);
    COMMIT;
END;
/

# Invoker's right
CREATE OR REPLACE PROCEDURE insert_emp2
    (p_id IN emp.employee_id%type,
    p_name IN emp.last_name%type,
    p_sal IN emp.salary%type)
AUTHID CURRENT_USER
IS
BEGIN
    INSERT INTO emp(employee_id,last_name,salary) VALUES (p_id,p_name,p_sal);
    COMMIT;
END;
/

SELECT * FROM user_source WHERE name = 'INSERT_EMP1';
SELECT * FROM user_source WHERE name = 'INSERT_EMP2';

GRANT EXECUTE ON insert_emp1 TO green;
GRANT EXECUTE ON insert_emp2 TO green;

SELECT * FROM user_tab_privs;
GRANTEE    OWNER      TABLE_NAME                    GRANTOR    PRIVILEGE  GRANTABLE  HIERARCHY
---------- ---------- ----------------------------- ---------- ---------- ---------- ----------
GREEN      HR         INSERT_EMP1                   HR         EXECUTE    NO         NO
GREEN      HR         INSERT_EMP2                   HR         EXECUTE    NO         NO
...
3. GREEN SESSION
  SELECT * FROM user_tab_privs;
GRANTEE    OWNER      TABLE_NAME                    GRANTOR    PRIVILEGE  GRANTABLE  HIERARCHY
---------- ---------- ----------------------------- ---------- ---------- ---------- ----------
GREEN      HR         INSERT_EMP1                   HR         EXECUTE    NO         NO
GREEN      HR         INSERT_EMP2                   HR         EXECUTE    NO         NO

DESC hr.insert_emp1
DESC hr.insert_emp2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_ID                           NUMBER(6)               IN
 P_NAME                         VARCHAR2(25)            IN
 P_SAL                          NUMBER(8,2)             IN

EXECUTE hr.insert_emp1(7777,'test1',5000)
ㄴ hr이 만들고 green이 호출 -> hr.emp에 저장
ㄴ definer's right 개념으로 만든 프로그램은 만든 사람 입장에서 수행한다.

EXECUTE hr.insert_emp2(8888,'test2',6000)
ㄴ hr이 만들고 green이 호출 -> green.emp에 저장
ㄴ invoker's right 개념으로 만든 프로그램은 호출하는 사람 입장에서 수행한다.

SELECT * FROM emp;      -- Invoker's right
EMPLOYEE_ID LAST_NAME                     SALARY
----------- ------------------------- ----------
       8888 test2                           6000
4. HR SESSION
  SELECT * FROM emp;      -- Definer's right
EMPLOYEE_ID LAST_NAME                     SALARY
----------- ------------------------- ----------
       7777 test1                           5000

 

# SYS SESION
  SELECT to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') FROM dual;
2023-12-21 00:55:31
ㄴ OS정보 기준

[oracle@oracle ~]$ date
Thu Dec 21 01:00:09 EST 2023

[oracle@oracle ~]$ timedatectl
       Time zone: America/New_York (EST, -0500)

# TIMEZONE 수정
[oracle@oracle ~]$ timedatectl set-timezone 'Asia/Seoul'
Authentication is required to set the system timezone.
ㄴ 루트인증

[oracle@oracle ~]$ timedatectl
       Time zone: Asia/Seoul (KST, +0900)

[oracle@oracle ~]$ sqlplus / as sysdba
ㄴ 재접속해야한다.

SELECT to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') FROM dual;
2023-12-21 15:04:30

# 현재 날짜 시간 정보 수정 : 한국기준
[root@oracle ~]$ rdate -s time.bora.net

 

1. INSA SESSION
  SELECT * FROM session_roles;
ROLE
------------------------------
PROG
MGR

SELECT * FROM user_role_privs;
USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
INSA                           MGR                            NO  YES NO
INSA                           PROG                           NO  YES NO
2. SYS SESSION
  CREATE OR REPLACE PROCEDURE priv_mgr
AUTHID CURRENT_USER
IS
BEGIN
    IF to_char(sysdate,'hh24:mi') BETWEEN '15:10' AND '15:20' THEN
        DBMS_SESSION.SET_ROLE('sec_app_role');
    ELSE
        DBMS_SESSION.SET_ROLE('NONE');
    END IF;
END;
/

# APPLICATION ROLE
CREATE ROLE sec_app_role IDENTIFIED USING priv_mgr;
ㄴ sec_app_role 롤은 priv_mgr 프로그램을 통해서 할꺼다.

GRANT SELECT ANY DICTIONARY TO sec_app_role;
ㄴ

GRANT EXECUTE ON priv_mgr TO insa;
3. INSA SESSION
  SELECT * FROM session_roles;
ROLE
------------------------------
PROG
MGR

EXECUTE sys.priv_mgr

SELECT * FROM session_roles;
ROLE
------------------------------
SEC_APP_ROLE

SELECT * FROM sys.tab$;
ㄴ 조회 가능





■ profile 관리
리소스 소비를 제어하고 계정 상태 및 암호 만료 관리

# SYS SESSION
SELECT username, profile FROM dba_users;

SELECT * FROM dba_profiles WHERE profile = 'DEFAULT';
PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------
DEFAULT                        FAILED_LOGIN_ATTEMPTS            PASSWORD 10
DEFAULT                        PASSWORD_LIFE_TIME               PASSWORD 180
DEFAULT                        PASSWORD_REUSE_TIME              PASSWORD UNLIMITED
DEFAULT                        PASSWORD_REUSE_MAX               PASSWORD UNLIMITED
DEFAULT                        PASSWORD_VERIFY_FUNCTION         PASSWORD NULL
DEFAULT                        PASSWORD_LOCK_TIME               PASSWORD 1
DEFAULT                        PASSWORD_GRACE_TIME              PASSWORD 7
......

FAILED_LOGIN_ATTEMPTS 암호 오류 허용 횟수 (회)
10 : 로그인 10회 시도 가능		  
PASSWORD_LOCK_TIME 암호 오류 허용 횟수 초과 시 자동으로 계정 잠금 기간 (일수)
1 : 1일 계정 잠금
UNLIMITED : 영원히 (DBA 수동해제)
PASSWORD_LIFE_TIME 암호 유효기간 (일수)
180 : 해당 암호를 180일간 사용 가능		  
PASSWORD_GRACE_TIME 암호 만료 이후 암호 변경까지 유예기간 (일수)
7 : 로그인 시 7일간 만료예정 안내, 만료 시 신규 암호 변경 요청
PASSWORD_REUSE_TIME 주어진 일수동안 암호를 재사용할 수 없도록 지정 (일수)
UNLIMITED : 설정안함
30 : 30일간 예정 암호 사용가		 AND조건
PASSWORD_REUSE_MAX 현재 암호를 재사용하기 전에 필요한 암호 변경 횟수를 지정
UNLIMITED : 영구사용불가
PASSWORD_VERIFY_FUNCTION 암호의 복합성 검사, 함수로 구현해야한다.
NULL : 검사안한다.		  


PASSWORD_REUSE_TIME : 30
PASSWORD_REUSE_MAX : 3
ㄴ 암호를 재사용하려면 30일이 지나야 하고, 그 사이 암호는 3번은 바꿔야 한다.




ELECT * FROM user_views;

DESC user_views
Name              Null?      Type
---------------- -------- -------------- 
TEXT                      LONG           -- 2GB
TYPE_TEXT                 VARCHAR2(4000) -- 4000byte
...

set long 1000   -- long 타입의 값을 1000byte 보여줘

SELECT text FROM user_views WHERE view_name = 'EMP_VIEW';
TEXT
--------------------------------------------------------------------------------
select "EMPLOYEE_ID","FIRST_NAME","LAST_NAME","EMAIL","PHONE_NUMBER","HIRE_DATE"
,"JOB_ID","SALARY","COMMISSION_PCT","MANAGER_ID","DEPARTMENT_ID" from hr.employe
es
저작자표시

'Oracle 11g > Oracle DB' 카테고리의 다른 글

54-2 ORACLE Audit  (1) 2023.12.26
53 ORACLE Profile, Resource  (1) 2023.12.22
51 ORACLE Tablespace, Privilege, Role  (1) 2023.12.20
50 ORACLE SGA, Undo, User  (0) 2023.12.19
49 ORACLE Tablespace, VM Backup, Control File, Redo log file  (0) 2023.12.18
'Oracle 11g/Oracle DB'의 다른글
  • 현재글52 ORACLE Role, Execute, Profile

티스토리툴바