251209 TIL

2025. 12. 9. 20:02ㆍCourses/아이티윌 오라클 DBA 과정

Role(Cont.)

ANY 시스템 권한

select distinct privilege from dba_sys_privs where privilege like '%ANY%';

모든 사용자 객체에 대해 작업을 수행할 수 있는 권한
ANY 권한은 부여 시 따로 문서 관리하기

Default Role

유저가 오라클 접속 시 자동으로 활성화되는 롤

유저에게 부여한 특정한 롤만 default role로 지정

-- prog 롤만 default role로 지정
alter user insa default role prog;
select * from dba_role_privs where grantee = 'INSA';

INSA@ora19c> conn insa/insa
Connected.
INSA@ora19c> select * from session_roles;

ROLE
------------------------------
PROG

INSA@ora19c> select * from session_privs;

PRIVILEGE
------------------------------
CREATE TRIGGER
CREATE PROCEDURE
CREATE VIEW
CREATE TABLE
CREATE SESSION

INSA@ora19c> select count(*) from hr.locations;
select count(*) from hr.locations
                        *
ERROR at line 1:
ORA-00942: table or view does not exist

INSA@ora19c> select count(*) from hr.employees;
select count(*) from hr.employees
                        *
ERROR at line 1:
ORA-00942: table or view does not exist

INSA@ora19c> select count(*) from hr.departments;

  COUNT(*)
----------
        27

-- mgr 롤만 default role로 지정
alter user insa default role mgr;
select * from dba_role_privs where grantee = 'INSA';

INSA@ora19c> conn insa/insa
Connected.
INSA@ora19c> select * from session_roles;

ROLE
------------------------------
MGR

INSA@ora19c> select * from session_privs;

PRIVILEGE
------------------------------
SELECT ANY TABLE
CREATE TABLE
CREATE SESSION

INSA@ora19c> select count(*) from hr.employees;

  COUNT(*)
----------
       107

INSA@ora19c> select count(*) from hr.departments;

  COUNT(*)
----------
        27

INSA@ora19c> select count(*) from hr.locations;

  COUNT(*)
----------
        23

-- 유저에게 부여된 특정한 롤만 제외하고 나머지 롤들은 default role로 지정
alter user insa default role all except mgr;
select * from dba_role_privs where grantee = 'INSA';

세션 롤 설정

-- mgr 롤에 비밀번호 설정
alter role mgr identified by oracle;

INSA@ora19c> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEL DEF OS_ COM INH
------------------------------ ------------------------------ --- --- --- --- --- ---
INSA                           MGR                            NO  NO  NO  NO  NO  NO
INSA                           PROG                           NO  NO  YES NO  NO  NO

INSA@ora19c> select * from session_roles;

ROLE
------------------------------
PROG

# 모든 세션 롤 해제
INSA@ora19c> set role none;

Role set.

INSA@ora19c> select * from session_roles;

no rows selected

#  유저가 가진 특정 롤을 세션 롤로 설정
INSA@ora19c> set role prog;

Role set.

INSA@ora19c> select * from session_roles;

ROLE
------------------------------
PROG

#  유저가 가진 모든 롤을 세션 롤로 설정
INSA@ora19c> set role all;

Role set.

# 비밀번호가 설정된 롤은 활성화되지 않음
INSA@ora19c> select * from session_roles;

ROLE
------------------------------
PROG

# 비밀번호가 설정된 롤을 세션 롤로 설정
INSA@ora19c> set role mgr identified by oracle;

Role set.

INSA@ora19c> select * from session_roles;

ROLE
------------------------------
MGR

INSA@ora19c> set role prog, mgr identified by oracle;

Role set.

INSA@ora19c> select * from session_roles;

ROLE
------------------------------
MGR
PROG

-- mgr 롤에 설정된 비밀번호 해제
alter role mgr not identified;

INSA@ora19c> conn insa/insa
Connected.
INSA@ora19c> select * from session_roles;

ROLE
------------------------------
PROG
MGR

# 특정 롤을 제외하고 유저가 가진 모든 롤을 세션 롤로 설정
INSA@ora19c> set role all except mgr;

Role set.

INSA@ora19c> select * from session_roles;

ROLE
------------------------------
PROG

PL/SQL

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

Definer’s right

SYS

-- drop any table 시스템 권한이 있기 때문에 다른 유저의 테이블 삭제 가능
drop table hr.emp purge; 

-- create any table 시스템 권한이 있기 때문에 다른 유저의 테이블 생성 가능
create table hr.emp(id number, name varchar2(30), sal number) tablespace users;
create table insa.emp(id number, name varchar2(30), sal number) tablespace users;

HR

create or replace procedure insert_emp1
(p_id in number, 
p_name in varchar2, 
p_sal in number)
is
begin
    insert into emp(id, name, sal) values(p_id, p_name, p_sal);
    commit;
end insert_emp1;
/

select * from user_objects where object_name = 'INSERT_EMP1';
select * from user_procedures where object_name = 'INSERT_EMP1';
select * from user_source where name = 'INSERT_EMP1';

execute insert_emp1(1, 'james', 1000)
select * from emp;

-- insa에게 insert_emp1 프로시저 실행 권한 부여
grant execute on insert_emp1 to insa;
select * from user_tab_privs;

INSA

select * from user_tab_privs;

-- insa 유저가 insert_emp1을 호출했지만 insert_emp1을 만든 hr의 emp 테이블에 insert 수행
exec hr.insert_emp1(2, 'oracle', 3000)

-- 데이터 삽입 X
select * from emp;

HR

-- oracle 데이터 삽입됨
select * from emp;

Invoker’s right

create or replace procedure insert_emp2
(p_id in number, 
p_name in varchar2, 
p_sal in number)
authid current_user
is
begin
    insert into emp(id, name, sal) values(p_id, p_name, p_sal);
    commit;
end insert_emp2;
/

select * from user_objects where object_name = 'INSERT_EMP2';
select * from user_source where name = 'INSERT_EMP2';

grant execute on hr.insert_emp2 to insa;
select * from user_tab_privs;

INSA

select * from user_tab_privs;

-- insa 유저가 가진 emp 테이블에 insert됨
exec hr.insert_emp2(3, 'SQL', 5000)

select * from emp;

application role

PL/SQL 프로그램에 의해서 롤을 활성화/비활성화 가능

dbms_session.set_role()

INSA@ora19c> select * from session_roles;

ROLE
------------------------------
PROG
MGR

INSA@ora19c> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEL DEF OS_ COM INH
------------------------------ ------------------------------ --- --- --- --- --- ---
INSA                           MGR                            NO  NO  YES NO  NO  NO
INSA                           PROG                           NO  NO  YES NO  NO  NO

# 모든 세션 롤 해제
INSA@ora19c> exec dbms_session.set_role('none')

PL/SQL procedure successfully completed.

INSA@ora19c> select * from session_roles;

no rows selected

# 유저가 가진 모든 롤을 세션 롤로 설정
INSA@ora19c> exec dbms_session.set_role('all')

PL/SQL procedure successfully completed.

INSA@ora19c> select * from session_roles;

ROLE
------------------------------
PROG
MGR

# # 유저가 가진 특정 롤을 세션 롤로 설정
INSA@ora19c> exec dbms_session.set_role('prog')

PL/SQL procedure successfully completed.

INSA@ora19c> select * from session_roles;

ROLE
------------------------------
PROG

SYS

-- 프로시저 생성
create or replace procedure priv_mgr
authid current_user
is
begin
        -- 14:00 ~ 14:10 일 경우 프로시저를 실행한 세션에 sec_app_role 설정
    if to_char(sysdate, 'hh24:mi') between '14:00' and '14:10' then
        dbms_session.set_role('sec_app_role');

        -- 아닐 경우 모든 세션 롤 해제
    else
        dbms_session.set_role('none');
    end if;
end priv_mgr;
/

-- application 인증 방식으로 sec_app_role 생성
create role sec_app_role identified using priv_mgr;
select * from dba_roles where role = 'SEC_APP_ROLE';

-- select any dictionary 시스템 권한 부여
grant select any dictionary to sec_app_role;
select * from dba_sys_privs where grantee = 'SEC_APP_ROLE';

-- insa 유저에게 프로시저 실행 권한 부여
grant execute on priv_mgr to insa;

INSA

INSA@ora19c> conn insa/insa
Connected.
INSA@ora19c> select * from session_roles;

ROLE
------------------------------
PROG
MGR

INSA@ora19c> select * from user_tab_privs;

GRANTEE                        OWNER                          TABLE_NAME                     GRANTOR                    PRIVILEGE                       GRA HIE COM TYPE                     INH
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ --- --- --- ------------------------ ---
INSA                           HR                             INSERT_EMP1                    HR                         EXECUTE                         NO  NO  NO  PROCEDURE                NO
INSA                           HR                             INSERT_EMP2                    HR                         EXECUTE                         NO  NO  NO  PROCEDURE                NO
INSA                           SYS                            PRIV_MGR                       SYS                        EXECUTE                         NO  NO  NO  PROCEDURE                NO
PUBLIC                         SYS                            INSA                           INSA                       INHERIT PRIVILEGES              NO  NO  NO  USER                     NO

INSA@ora19c> desc sys.priv_mgr
PROCEDURE sys.priv_mgr

# priv_mgr 프로시저 실행 결과에 따라 모든 세션 롤이 해제됨(14:00 ~ 14:10이 아니기 때문)
INSA@ora19c> exec sys.priv_mgr

PL/SQL procedure successfully completed.

INSA@ora19c> select * from session_roles;

no rows selected

# 14:00 ~ 14:10 이므로 sec_app_role 롤이 세션 롤로 설정됨
INSA@ora19c> ! date
Tue Dec  9 14:00:04 KST 2025

INSA@ora19c> exec sys.priv_mgr

PL/SQL procedure successfully completed.

INSA@ora19c> select * from session_roles;

ROLE
------------------------------
SEC_APP_ROLE

INSA@ora19c> select * from role_sys_privs;

ROLE                           PRIVILEGE                      ADM COM INH
------------------------------ ------------------------------ --- --- ---
PROG                           CREATE VIEW                    NO  NO  NO
PROG                           CREATE TRIGGER                 NO  NO  NO
MGR                            SELECT ANY TABLE               NO  NO  NO
PROG                           CREATE PROCEDURE               NO  NO  NO

# 딕셔너리 테이블 조회 가능
INSA@ora19c> select count(*) from sys.obj$;

  COUNT(*)
----------
     72532

SYS

create or replace procedure priv_mgr
authid current_user
is
begin
        -- 14:00 ~ 14:05 일 경우 프로시저를 실행한 세션에 sec_app_role 설정
    if to_char(sysdate, 'hh24:mi') between '14:00' and '14:05' then
        dbms_session.set_role('sec_app_role');

        -- 아닐 경우 insa가 가진 모든 롤을 세션 롤로 설정
    else
        dbms_session.set_role('all');
    end if;
end priv_mgr;
/

INSA

# priv_mgr 프로시저 실행 결과 14:00 ~ 14:05 가 아니므로 
# sec_app_role은 세션 롤에서 제외되고 insa가 가진 모든 롤들이 세션 롤로 설정됨
INSA@ora19c> exec sys.priv_mgr

PL/SQL procedure successfully completed.

INSA@ora19c> select * from session_roles;

ROLE
------------------------------
PROG
MGR

INSA@ora19c> select count(*) from sys.obj$;
select count(*) from sys.obj$
                         *
ERROR at line 1:
ORA-00942: table or view does not exist

INHERIT PRIVILEGES(12c)

authid current_user 지시어를 이용해서 생성한 프로시저를 수행하기 위한 권한

select * from user_tab_privs;

-- SYS
revoke inherit privileges on user insa from public;

-- INSA
exec hr.insert_emp2(3, 'SQL', 5000)
-- ORA-06598: 불충분한 INHERIT PRIVILEGES 권한

-- SYS
grant inherit privileges on user insa to public;

-- INSA
exec hr.insert_emp2(3, 'SQL', 5000)
-- PL/SQL 프로시저가 성공적으로 완료되었습니다.

# 유저를 생성하면 기본적으로 INHERIT PRIVILEGES 객체 권한을 보유
ORA1@ora19c> select * from user_tab_privs;

GRANTEE                        OWNER                          TABLE_NAME                     GRANTOR                    PRIVILEGE                       GRA HIE COM TYPE                     INH
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ --- --- --- ------------------------ ---
PUBLIC                         SYS                            ORA1                           ORA1                       INHERIT PRIVILEGES              NO  NO  NO  USER                     NO

PROFILE

리소스 소비 제어 및 암호 관리

select username, profile from dba_users;

select * from dba_profiles where profile = 'DEFAULT';

패스워드 관리

select * from v$parameter where name like '%failed%';

sec_max_failed_login_attempts : 클라이언트가 서버 프로세스에 연결할 때 시도할 수 있는 인증 횟수를 지정하는 파라미터
- 3번 실패 시 연결 해제

sql[oracle@ora19c ~]$ sqlplus insa/1

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Dec 9 15:04:13 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

slERROR:
ORA-01017: invalid username/password; logon denied

Enter user-nameinsa
Enter password:
ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name: insa
Enter password:
ERROR:
ORA-01017: invalid username/password; logon denied

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

select * from dba_profiles where profile = 'DEFAULT';

FAILED_LOGIN_ATTEMPTS : 암호 오류 허용 횟수
PASSWORD_LOCK_TIME : 암호 오류 허용 회수를 넘은 경우 자동으로 계정이 잠기는 일 수
PASSWORD_LIFE_TIME : 암호 유효 기간(일 수)
PASSWORD_GRACE_TIME : 암호 만료 이후 암호 변경까지 유예 기간(일 수)
PASSWORD_REUSE_TIME : 주어진 일 수 동안 암호를 재사용할 수 없도록 지정
PASSWORD_REUSE_MAX : 현재 암호를 재사용하기 위해 필요한 암호 변경 횟수를 지정
- 이전 암호를 재사용하려면 30일 이후에 그 사이에 암호는 1번 바꿔야 함
  - PASSWORD_REUSE_TIME : 30
  - PASSWORD_REUSE_MAX : 1
- 이전 암호는 무한으로 재사용 가능
  - PASSWORD_REUSE_TIME : unlimited
  - PASSWORD_REUSE_MAX : unlimited
INACTIVE_ACCOUNT_TIME : 지정된 일 수 동안 데이터베이스에 로그인하지 않은 사용자 계정 잠금
PASSWORD_VERIFY_FUNCTION : 암호의 복합성 검사

로그인 3번 실패 시 1분 잠금

-- 로그인 3번 실패 시 1분 잠금
alter profile default limit
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 1/1440;

select * from dba_profiles where profile = 'DEFAULT';

select * from dba_users where username = 'INSA';

[oracle@ora19c ~]$ sqlplus insa/1

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Dec 9 15:10:58 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name: insa
Enter password:
ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name: insa
Enter password:
ERROR:
ORA-01017: invalid username/password; logon denied

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

select * from dba_users where username = 'INSA';

1분 후 정상적으로 로그인 수행하면 락 해제됨

로그인 3번 실패 시 무한으로 계정 잠금

-- 로그인 3번 실패 시 무한으로 계정 잠금
alter profile default limit
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME unlimited;

select * from dba_profiles where profile = 'DEFAULT';

select * from dba_users where username = 'INSA';

-- 계정 락 해제
alter user insa identified by oracle account unlock password expire;
select * from dba_users where username = 'INSA';

# 재접속 시 비밀번호 재설정
[oracle@ora19c ~]$ sqlplus insa/oracle

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Dec 9 15:34:31 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

ERROR:
ORA-28001: the password has expired

Changing password for insa
New password:
Retype new password:
Password changed

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

select * from dba_users where username = 'INSA';

저작자표시 비영리 변경금지 (새창열림)

'Courses > 아이티윌 오라클 DBA 과정' 카테고리의 다른 글

251211 TIL (0)	2025.12.11
251210 TIL (0)	2025.12.11
251208 TIL (0)	2025.12.08
251205 TIL (1)	2025.12.05
251203 TIL (0)	2025.12.05

Joy's Devlog

Joy's Devlog

태그

최근글

댓글

공지사항

아카이브

Role(Cont.)

ANY 시스템 권한

Default Role

유저에게 부여한 특정한 롤만 default role로 지정

세션 롤 설정

PL/SQL

Definer’s right

SYS

HR

INSA

HR

Invoker’s right

INSA

application role

dbms_session.set_role()

SYS

INSA

SYS

INSA

INHERIT PRIVILEGES(12c)

PROFILE

패스워드 관리

로그인 3번 실패 시 1분 잠금

로그인 3번 실패 시 무한으로 계정 잠금

'Courses > 아이티윌 오라클 DBA 과정' 카테고리의 다른 글

관련글

티스토리툴바