user 관리(시스템,오브젝트), role 관리, profile 관리, profile의 resource 관리

YoonSeo Park ·2023년 12월 20일
profile관리resource관리role관리user관리
0

Oracle Architecture

목록 보기
4/11

2023/12/19

■ user 관리 (tablespace,temporary tablespace)

▶ ora1 유저 생성 (기본테이블 스페이스로)

create user ora1 identified by oracle;

예) create table test(id number);
test segment 는 어느 tablespace 에 저장되나요?
default tablespace (user)

▶ default 테이블스페이스 확인

select * from dba_users where username='ORA1';

유저생성시 DEFAULT TABLESPACE 가 지정되어 있지 않으면 SYSTEM TABLESPACE가
되는 문제점 때문에 10g 버전 부터 db 생성시에 db레벨에서
default 테이블스페이가 생성 되어 있다.

▶ db레벨에 기본값으로 설정된 정보 확인
select * from database_properties;

▶ ora2 유저 생성 (테이블 스페이스 example로 고정, temporary tablespace temp(기본값) 으로)

create user ora2 identified by oracle
default tablespace example -- users가 기본이다.
temporary tablespace temp -- temp 가 기본이다.
quota 1m on example;

select * from dba_users where username in ('ORA1','ORA2');

▶ 테이블스페이스 생성

CREATE TABLESPACE user_tbs
DATAFILE '/u01/app/oracle/oradata/ora11g/user_tbs01.dbf' size 10m
AUTOEXTEND ON 
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

▶ temporary 테이블스페이스 생성 생성
일시적으로 저장하는것은 TEMPFILE

CREATE TEMPORARY TABLESPACE user_temp
TEMPFILE '/u01/app/oracle/oradata/ora11g/user_temp01.dbf' size 10m
AUTOEXTEND ON 
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT MANUAL;

TEMPORARY 테이블스페이스는
유저별로 분리가 되어있다.

▶ default 테이블 스페이스 변경

alter database default tablespace user_tbs;

▶ default temporary 테이블스페이스 변경

alter database default temporary tablespace user_temp;

select * from database_properties;
select * from dba_users where username in ('ORA1','ORA2');

▶ temp10, temporary 테이블스페이스 생성

CREATE TEMPORARY TABLESPACE temp10
TEMPFILE '/u01/app/oracle/oradata/ora11g/user_temp10.dbf' size 10m
AUTOEXTEND ON 
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT MANUAL;

SELECT * FROM dba_tablespaces;
SELECT * FROM dba_data_files;
SELECT * FROM dba_temp_files;

▶ ora3 유저 생성 (테이블 스페이스 example로 고정, temporary tablespace temp10(고정값) 으로)

create user ora3 identified by oracle
default tablespace example
temporary tablespace temp10
quota 1m on example;

▶ 확인
select * from dba_users where username in ('ORA1','ORA2','ORA3');

▶ 변경
alter database default tablespace users;
alter database default temporary tablespace temp;

▶ 확인
select from database_properties;
select from dba_users where username in ('ORA1','ORA2','ORA3');

■ 요약

  • default 테이블스페이스, temporary 테이블스페이스는 변경가능하다.
  • 단, 기본값이었던 유저는 변경되고, 다른 테이블스페이스, temporary테이블스페이스로 지정했던 유저는 변경이 안된다.

■ 따라서 업무별로 default 테이블스페이스와, temporary 테이블스페이스가 나누어져있다.

  • 일반유저는 그냥 디폴트로 쓴다
  • 유저의 기본 테이블스페이스 변경
    : ALTER USER 유저명 DEFAULT TABLESPACE 테이블스페이스명;

■ 유저가 사용할 수있는 테이블 스페이스 (쿼터값 확인)

▶ 다시 변경후

alter database default tablespace user_tbs;
alter database default temporary tablespace user_temp;
select * from database_properties;
select * from dba_users where username in ('ORA1','ORA2','ORA3');

▶ ora1,2,3 유저에 create 권한 주기

grant create session, create table to ora1,ora2,ora3;

▶ 확인, (admin_option: sys계정처럼 권한을줄수있는 옵션)

select * from dba_sys_privs where grantee in ('ORA1','ORA2','ORA3');

▶

conn ora1/oracle

create table test(id number, name varchar2(30), dat date);


select table_name, tablespace_name from user_tables;


select * from user_users;

insert into test(id, name, day) values(1,'james',sysdate);
  • create 할때는 딕셔너리에 설계정보만 담겨있는것이고
  • insert 작업시 쿼터값을 안받았으므로 에러가난다
  • ORA-01950: no privileges on tablespace 'USER_TBS'
    이유 : USER_TBS tablespace에 대한 quota을 받지 않아서 발생

▶ 쿼터값 확인

select * from user_ts_quotas;

▶ 쿼터값 변경 후 insert, 확인

conn / as sysdba

alter user ora1 quota 1m on user_tbs;
alter user ora1 quota 1m on example;

select * from dba_ts_quotas where username = 'ORA1';


conn ora1/oracle

select * from user_ts_quotas;

insert into test(id, name, day) values(1,'james',sysdate);
commit;

select * from test;
create table emp(id number, name varchar2(30), day date)
tablespace example;

insert into emp(id, name, day) values(1,'james',sysdate);
commit;

select * from emp;

2023/12/20

■ user 관리

유저

▶ 1. 유저 생성

CREATE USER 유저이름
IDENTIFIED BY 암호
DEFAULT TABLESPACE 테이블스페이스 이름
TEMPORARY TABLESPACE 임시 테이블스페이스 이름
QUOTA unlimited ON 테이블스페이스 이름
QUOTA 1M ON 테이블스페이스 이름
PASSWORD EXPIRE
ACCOUNT LOCK | UNLOCK(기본 값)
PROFILE profile | DEFAULT(기본 값)

DEFAULT TABLESPACE 테이블스페이스 이름 생성이유:
생성유저가 테이블스페이스를 지정하지 않고 테이블(세그멘트)을 생성하게될 때 시스템 테이블스페이스에 생성되지 않고
디폴트 테이블스페이스에 생성하기 위함.(users 테이블스페이스)

▶ 2. 유저 변경

ALTER USER 유저이름
IDENTIFIED BY 암호
DEFAULT TABLESPACE 테이블스페이스 이름
TEMPORARY TABLESPACE 임시 테이블스페이스 이름
QUOTA unlimited ON 테이블스페이스 이름
QUOTA 1M ON 테이블스페이스 이름
PASSWORD EXPIRE
ACCOUNT LOCK | UNLOCK(기본 값)
PROFILE profile | DEFAULT(기본 값)

▶ 3. 유저 삭제

  • OBJECT 를 생성한게 없을 경우
DROP USER 유저이름;
  • 유저에 속한 OBJECT 가 있을 경우 유저를 삭제하면 오류 발생한다.
    OBJECT 들을 찾아서 삭제한 후 유저 삭제하면 된다.
  • CASCASE 옵션을 사용하면 유저 삭제하기 전에 그 유저가 생성한 OBJECT 찾아서 삭제한 후 유저 삭제한다.
DROP USER 유저이름 CASCADE;

잘목삭제하게 되면 불한전한 복구를 해야한다
export , import

테이블스페이스

▶ 1. 테이블스페이스 변경, 삭제

  • PROPERTY 의 속성과 값을 보여준다.
select property_name, property_value
from DATABASE_PROPERTIES;
  • 테이블스페이스를 보여준다.
select * from dba_tablespaces;
  • default 테이블스페이로 지정되어 있는 테이블 스페잇는 삭제 할수 없다.
drop tablespace user_tbs including contents and datafiles;
  • default temporary 테이블스페이로 지정되어 있는 테이블 스페잇는 삭제 할수 없다.
drop tablespace user_temp including contents and datafiles;
  • 따라서 default로 지정되어있는것은 변경후 삭제가능하다.
alter database default tablespace users;
alter database default temporary tablespace temp;
  • 삭제, 가끔 temporary 테이블스페이스를 삭제할때 딜레이가 걸리므로 멈추고 다른 sys 세션에서 삭제한다.
drop tablespace user_tbs including contents and datafiles;
drop tablespace user_temp including contents and datafiles;

■ 시스템 권한

▶ 1. [sys session] 테이블스페이스 생성, 유저생성

  • 테이블스페이스 생성
CREATE TABLESPACE insa_tbs
DATAFILE '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf' size 10M
AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;
  • temporary 테이블스페이스 생성
    ( 주의, TEMPFILE, MANAUL )
CREATE TEMPORARY TABLESPACE insa_temp
TEMPFILE '/home/oracle/userdata/insa_temp01.dbf' size 5M
AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT MANUAL;
  • 확인
select * from dba_tablespaces;
select * from dba_data_files;
select * from dba_temp_files;
  • 유저생성
CREATE USER insa
IDENTIFIED BY oracle -- 10g 부터는 비밀번호 대소문자구분
DEFAULT TABLESPACE insa_tbs
TEMPORARY TABLESPACE insa_temp
QUOTA 1M ON insa_tbs;

▶ 2. [insa session] 다른세션에서 db접속

  • 권한 때문에 denied가 뜬다.
  • 권한 관리?
    : 권한은 특정 SQL문을 실행하거나 다른 유저가 소유한 object를 액세스할 수 있는 권한.
    • system 권한
      • db에 영향을 줄 수 있는 권한
      • 권한 관리는 sys가 관리한다.
    • object 권한
      • 다른 유저가 소유한 object를 액세스할 수 있는 권한
      • 권한 관리는 sys, 객체 소유자가 한다.

▶ 3. [sys session] 권한관리

insa 한테 접속 권한주기

GRANT create session TO insa;

insa 권한 확인

select * from dba_sys_privs
where grantee = 'INSA';

▶ 4. [insa session] 접속후, 확인

[oracle@oracle ~]$ sqlplus insa/oracle

SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 19 05:20:41 2023

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from user_sys_privs;

USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
INSA                           CREATE SESSION                           NO

자신의 땅 확인
SQL> select * from user_users;

USERNAME                          USER_ID ACCOUNT_STATUS
------------------------------ ---------- --------------------------------
LOCK_DATE EXPIRY_DA DEFAULT_TABLESPACE
--------- --------- ------------------------------
TEMPORARY_TABLESPACE           CREATED   INITIAL_RSRC_CONSUMER_GROUP
------------------------------ --------- ------------------------------
EXTERNAL_NAME
--------------------------------------------------------------------------------
INSA                                   94 OPEN
          16-JUN-24 INSA_TBS
INSA_TEMP                      19-DEC-23 DEFAULT_CONSUMER_GROUP

쿼터값 확인
SQL> select * from user_ts_quotas;

TABLESPACE_NAME                     BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
------------------------------ ---------- ---------- ---------- ---------- ---
INSA_TBS                                0    1048576          0        128 NO

SQL> create table insa_tba(id number, name
varchar2(30));

create table insa_tba(id number, name varchar2(30))
*
ERROR at line 1:
ORA-01031: insufficient privileges
  • create table 권한이 없다.

▶ 5. [sys session] 권한 주기

create 테이블 권한주기

GRANT create table TO insa;

권한 보기

select * from dba_sys_privs where grantee = 'INSA';
  • GRANT WITH ADMIN OPTION
    : WITH ADMIN 옵션으로 받은 시스템 권한에 대해서는 내가 다른 유저들 한테 권한을 부여하고 권한을 취소 할 수 있다. (sys계정처럼)
GRANT create table TO insa WITH ADMIN OPTION;
select * from dba_sys_privs where grantee = 'INSA';
  • WITH ADMIN OPTION으로 준 권한을 dba가 회수할려면 하나씩 다 찾아서 명세서 보고 회수해야한다.

▶ 6. [insa session] ,확인,테이블생성

자신이 받은 권한 보기
SQL> select * from user_sys_privs;

USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
INSA                           CREATE SESSION                           NO
INSA                           CREATE TABLE                             YES

테이블생성

create table insa_tba(id number, name varchar2(30));

SQL> select tablespace_name from user_tables where table_name ='INSA_TBA';

TABLESPACE_NAME
------------------------------
INSA_TBS

▶ 7. [sys session] segment 확인

확인

select * from dba_segments
where owner='INSA' AND segment_name='INSA_TBA';
select * from dba_extents
where owner='INSA' AND segment_name='INSA_TBA';

정보가 안보인다.
이유: 실제 생성은 안되고 딕셔너리에 정보만 저장한다.
설계도만 만들어진다고 생각하면 된다

  • 테이블을 생성하면 그 순간은 딕셔너리에 정보만 저장한다.
  • 설계도만 가지고 있다.
  • 실제 segment는 생성 되지 않는다
  • 실제 생성되는거는 insert 시 생성된다.

▶ 8. [insa session]

INSERT (insert시 segment가 그때 생성된다.)

INSERT INTO insa_tba(id,name) VALUES(1,'JAMES');

▶ 9. [sys session]

확인

select * from dba_segments
where owner='INSA' AND segment_name='INSA_TBA';
select * from dba_extents
where owner='INSA' AND segment_name='INSA_TBA';

▶ 10. [insa session]

롤백

ROLLBACK;

▶ 11. [sys session]

확인

select * from dba_segments
where owner='INSA' AND segment_name='INSA_TBA';
select * from dba_extents
where owner='INSA' AND segment_name='INSA_TBA';

유저생성

CREATE USER insa_buha
IDENTIFIED BY oracle
DEFAULT TABLESPACE insa_tbs
TEMPORARY TABLESPACE insa_temp
QUOTA 1M ON insa_tbs
PASSWORD EXPIRE;

접속 권한주기

GRANT CREATE SESSION TO insa_buha;

확인

select * from dba_users where username='INSA_BUHA';
select * from dba_ts_quotas where username='INSA_BUHA';
select * from dba_sys_privs where grantee='INSA_BUHA';

▶ 12. [insa_buha session]

▶ 13. [insa session] with admin option으로 권한주기

SQL> show user

USER is "INSA"

SQL> GRANT create table TO insa_buha;

Grant succeeded.
  • 그런데 언제 부여한지 알수없으니 따로 메모를 해놔야한다.

▶ 14. [insa_buha session] create table 권한 확인

SQL> show user
USER is "INSA_BUHA"
SQL> select * from user_sys_privs;

USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
INSA_BUHA                      CREATE SESSION                           NO
INSA_BUHA                      CREATE TABLE                             NO

SQL> CREATE TABLE buha(id number, name varchar2(30), day date);

Table created.

SQL> INSERT INTO buha(id,name,day) VALUES(1,'SCOTT',SYSDATE);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from buha;

        ID NAME                           DAY
---------- ------------------------------ ---------
         1 SCOTT                          19-DEC-23

WITH ADMIN OPTION 때문에
insa 유저가 insa_buha 유저한테 create table 권한을 줘서 가능한것이다.

▶ 15. [insa session] with admin option 으로 권한회수

SQL> REVOKE create table FROM insa_buha;

Revoke succeeded.

▶ 16. [insa_buha session] 권한확인

SQL> select * from user_sys_privs;

USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
INSA_BUHA                      CREATE SESSION                           NO

▶ 17. [sys session] create sequence 권한주기

create sequence 권한주기

GRANT create sequence TO insa WITH ADMIN OPTION;

확인

select * from dba_sys_privs where grantee='INSA';

▶ 18. [insa session]

SQL> select * from user_sys_privs;

USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
INSA                           CREATE SESSION                           NO
INSA                           CREATE SEQUENCE                          YES
INSA                           CREATE TABLE                             YES

SQL> CREATE SEQUENCE id_seq
	 	START WITH 1
		MAXVALUE 10
	 	INCREMENT BY 1
	 	NOCYCLE
	 	NOCACHE;
Sequence created.

SQL> select * from user_sequences where sequence_name = 'ID_SEQ';

SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE
------------------------------ ---------- ---------- ------------ - - ----------
LAST_NUMBER
-----------
ID_SEQ                                  1         10            1 N N          0
          1

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
INSA_TBA                       TABLE

SQL> select * from insa_tba;

no rows selected

SQL> INSERT INTO insa_tba(id,name) VALUES(id_seq.nextval,'sophia');

SQL> INSERT INTO insa_tba(id,name) VALUES(id_seq.nextval,'liam');

SQL> INSERT INTO insa_tba(id,name) VALUES(id_seq.nextval,'noah');

SQL> commit;

SQL> select * from insa_tba;

        ID NAME
---------- ------------------------------
         1 sophia
         2 liam
         3 noah

인사부하 한테 create sequence 권한 주기
SQL> GRANT create sequence TO insa_buha;

Grant succeeded.

▶ 19. [insa_buha session] 확인

SQL> select * from user_sys_privs;

USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
INSA_BUHA                      CREATE SESSION                           NO
INSA_BUHA                      CREATE SEQUENCE                          NO

SQL> create sequence buha_seq START WITH 2;

Sequence created.

SQL> select * from user_sequences where sequence_name = 'BUHA_SEQ';

SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE
------------------------------ ---------- ---------- ------------ - - ----------
LAST_NUMBER
-----------
BUHA_SEQ                                1 1.0000E+28            1 N N         20
          2

SQL>INSERT INTO buha(id,name,day) VALUES(buha_seq.nextval,'emma',sysdate);
SQL> INSERT INTO buha(id,name,day) VALUES(buha_seq.nextval,'parks',sysdate);
SQL> INSERT INTO buha(id,name,day) VALUES(buha_seq.nextval,'kkkk',sysdate);
SQL> commit;

SQL> select * from buha;

        ID NAME                           DAY
---------- ------------------------------ ---------
         1 SCOTT                          19-DEC-23
         2 emma                           19-DEC-23
         3 parks                          19-DEC-23
         4 kkkk                           19-DEC-23

▶ 20. [sys session]

select * from dba_sys_privs where grantee='INSA';
select * from dba_sys_privs where grantee='INSA_BUHA';

권한 회수

REVOKE create sequence FROM insa;

insa한테 with admin option으로 준 권한을 회수하여도
insa가 다른 유저한테 준 권한은 회수가 연쇄적으로 안된다.

select * from dba_sys_privs where grantee='INSA';
select * from dba_sys_privs where grantee='INSA_BUHA';

따라서 따로 권한을 회수해줘야한다.

REVOKE create sequence FROM insa_buha;

■ 객체 권한

  • 객체를 ACCESS 할수 있는 권한
  • 객체 권한은 sys, 객체 소유자가 권한을 부여 취소 할 수 있다.
  • TABLE: SELECT, INSERT, UPDATE, ALTER, INDEX,
    REFERENCES : 서로다른 유저의 각각의 테이블끼리 제약조건
  • VIEW : SELECT, INSERT, UPDATE, DELETE
  • SEQUENCE : SELECT, ALTER
  • PROCEDURE, FUNCTION, PACKAGE : EXECUTE

▶ 1. [sys session]

  • WITH GRANT OPTION
    : WITH GRANT OPTION 으로 받은 객체 권한 만큼은 다른 유저들한테 권한을 부여하고 취소할 수 있다.
GRANT select ON hr.employees TO insa WITH GRANT OPTION;
GRANT select ON hr.departments TO insa WITH GRANT OPTION;

insa유저가 부여받은 table 권한

select * from dba_tab_privs where grantee='INSA';

▶ 2. [insa session]

SQL> select * from user_tab_privs where grantee='INSA';

GRANTEE                        OWNER
------------------------------ ------------------------------
TABLE_NAME                     GRANTOR
------------------------------ ------------------------------
PRIVILEGE                                GRA HIE
---------------------------------------- --- ---
INSA                           HR
EMPLOYEES                      HR
SELECT                                   YES NO

INSA                           HR
DEPARTMENTS                    HR
SELECT                                   YES NO

GRANTEE                        OWNER
------------------------------ ------------------------------
TABLE_NAME                     GRANTOR
------------------------------ ------------------------------
PRIVILEGE                                GRA HIE
---------------------------------------- --- ---

SQL> GRANT select ON hr.employees TO insa_buha;

Grant succeeded.

SQL> GRANT select ON hr.departments TO insa_buha;

Grant succeeded.
select * from user_tab_privs;

▶ 3. [insa_buha session]

select * from hr.employees;
select * from hr.departments;

▶ 4. [insa session]

SQL> REVOKE select ON hr.employees FROM insa_buha;

Revoke succeeded.

SQL> select * from user_tab_privs;

▶ 5. [insa_buha session]

SQL> select * from hr.employees;

select * from hr.employees
                 *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select * from user_tab_privs;

GRANTEE                        OWNER
------------------------------ ------------------------------
TABLE_NAME                     GRANTOR
------------------------------ ------------------------------
PRIVILEGE                                GRA HIE
---------------------------------------- --- ---
INSA_BUHA                      HR
DEPARTMENTS                    INSA
SELECT                                   NO  NO

▶ 6. [sys session]

테이블 권한 확인

select * from dba_tab_privs where grantee='INSA';
select * from dba_tab_privs where grantee='INSA_BUHA';
  • WITH GRANT OPTION 인 INSA유저의 테이블 권한회수
    REVOKE select ON hr.departments FROM insa;
  • WITH GRANT OPTION을 사용하여 부여한 객체 권한을 회수하면 연쇄적으로 취소를 수행한다. ( 시스템권한의 with admin option과 다르게 , 이유: 아마 with admin option도 연쇄적으로 권한을 회수하면 접속한 유저한테도 영향을 주기때문에)

테이블 권한 확인

select * from dba_tab_privs where grantee='INSA';
select * from dba_tab_privs where grantee='INSA_BUHA';

■ ROLE

: 관련성이 있는 권한들을 하나로 묶어서 관리하는 객체

  • 관리의 편리성을 위해

예) 프로그래머들에게 부여할 시스템 권한

  • create procedure
  • create trigger
  • create view
    이런것들을 하나로 묶어서 권한을 부여/회수 할려고 ROLE

▶ 1. [sys session] 롤에 시스템권한부여

ROLE 생성

create role prog;

select * from dba_roles;
  • 롤에 시스템 권한부여
GRANT create view, create procedure, create trigger TO prog;
select * from dba_sys_privs where grantee='PROG';
  • 롤에 객체 권한부여
GRANT select ON hr.departments TO prog;
select * from dba_tab_privs where grantee='PROG';
  • 롤을 유저한테 부여
GRANT select ON hr.departments TO prog;
select * from dba_tab_privs where grantee='PROG';
  • 롤을 유저한테 부여
GRANT prog TO insa;
select * from dba_role_privs where grantee='INSA';

▶ 2. [insa session] 확인

다시접속하면 롤이활성화

SQL> select * from session_roles;

no rows selected
  • 롤을 유저한테 부여하였으면 다시접속해야 롤이활성화 된다.
    SQL> conn insa/oracle
Connected.

SQL> select * from session_roles;

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

2023/12/21

sqlplus 디스플레이 조절

set linesize 200 가로사이즈조절
set pagesize 1000 몇행당 컬럼을 디스플레이 할것인지
col role format a15
col privilege format a20

열폭 조절
col owner format a10
col table_name format a15
col column_name format a15

▶ 3. [sys session] 확인, role생성

▶ sqlplus에서 디스플레이 조절

set pagesize 200
col grantee format a10
col privilege format a15

▶ dba_roles, dba_sys_privs

select * from dba_roles;
select * from dba_sys_privs where grantee ='CONNECT';
select * from dba_sys_privs where grantee ='RESOURCE';
select * from dba_sys_privs where grantee ='DBA';
select * from dba_tab_privs where grantee ='DBA';

▶ ROLE 생성

create role mgr;

▶ 롤 mgr에 system 권한을 부여하자

  • any 붙은 시스템권한은 무조건 일지를 기록하자.
grant select any table to mgr;
select * from dba_sys_privs where grantee ='MGR';

▶ insa한테 mgr 부여

grant mgr to insa;
select * from dba_role_privs where grantee='INSA';

▶ 4. [insa session] 확인, role생성

ROLE을 부여하고 다시 접속해야 ROLE이 부여된다.
-> 따라서 DBA는 메시지를 보내야한다.

SQL> select * from session_roles;

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

SQL> conn insa/oracle

Connected.

SQL> select * from session_roles;

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

SQL> select * from role_sys_privs;

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

SQL> select * from role_tab_privs;

ROLE            OWNER      TABLE_NAME      COLUMN_NAME     PRIVILEGE           GRA
--------------- ---------- --------------- --------------- -------------------- ---
PROG            HR         DEPARTMENTS                     SELECT              NO

▶ select any table 권하을 부여하였으므로 가능하다.
SQL> select * from hr.locations;

SQL> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
INSA                           MGR                            NO  YES NO
INSA                           PROG                           NO  YES NO

DEF : default yes이면 insa가 접속하면 role이 자동 활성화된다.

▶ 5. [sys session] ROLE 부분 비활성화

일반적으로 role을 유저한테 부여하면 default role로 활성화된다.

select * from dba_role_privs where grantee='INSA';

insa 유저가 받은 mgr 롤만 비활성화하기 다른것은 활성화

alter user insa default role all except mgr;
select * from dba_role_privs where grantee='INSA';

▶ 6. [insa session] 확인

SQL> conn insa/oracle

Connected.

SQL> select * from session_roles;

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

SQL> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
INSA                           MGR                            NO  NO  NO
INSA                           PROG                           NO  YES NO

SQL> select * from role_sys_privs;

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

▶ 7. [sys session] ROLE 전부 비활성화

alter user insa default role none;
select * from dba_role_privs where grantee='INSA';

▶ 8. [insa session] 확인

select * from session_roles;
select * from user_role_privs;
select * from role_sys_privs;
select * from role_tab_privs;

▶ 9. [sys session] ROLE 활성화

insa의 prog 롤만 제외하고 활성화

alter user insa default role all except prog;
select * from dba_role_privs where grantee='INSA';

insa의 롤 전부 활성화

alter user insa default role all;

▶ 10. [insa session]

뷰만들기
SQL> create view emp_view
as select * from hr.employees; 2

View created.

SQL> set long 200
SQL> select text from user_views where view_name = 'EMP_VIEW';

▶ 11. [sys session] 롤 권한회수, 롤 삭제, 롤생성(패스워드)

롤 권한회수

revoke mgr from insa;
select * from dba_role_privs where grantee='INSA';

롤 삭제

drop role mgr;

롤 생성 (패스워드 알고있는 사람만 사용하게)

create role mgr identified by oracle;

권한부여

grant select any table to mgr;
select * from dba_sys_privs where grantee= 'MGR';
grant mgr to insa;
select * from dba_role_privs where grantee= 'INSA';

▶ 12. [insa session]

  • 패스워드를 가지고 롤을 생성하고 권한을주고
    유저한테 롤의 권한을 주었지만
    유저한테는 보이지 않는다.
    따라서 set명령어를 이용해서 role 활성화 해줘야한다.

SQL> conn insa/oracle

Connected.

SQL> select * from session_roles;

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

부여받은 롤 확인하기 (패스워드 있는것도 보인다.)
SQL> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
INSA                           MGR                            NO  NO  NO
INSA                           PROG                           NO  YES NO
  • set 명령어를 이용해서 role 활성화

SQL> set role mgr;

set role mgr
*
ERROR at line 1:
ORA-01979: missing or invalid password for role 'MGR'

SQL> set role mgr identified by oracle;

Role set.

set으로 mgr롤만 활성화했으므로, prog롤은 비활성화 되었다.
SQL> select * from session_roles;

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

SQL> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
INSA                           MGR                            NO  NO  NO
INSA                           PROG                           NO  YES NO

패스워드 갇고있는 롤도 있으므로 all로 안된다.
SQL> set role all;

set role all
*
ERROR at line 1:
ORA-01979: missing or invalid password for role 'MGR'
  • 따라서 패스워드있는것은 하나씩 set role 해줘야한다.

SQL> set role prog, mgr identified by oracle;

Role set.

SQL> select * from session_roles;

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

SQL> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
INSA                           MGR                            NO  NO  NO
INSA                           PROG                           NO  YES NO

자신이 받은 롤 전부 비활성화

SQL> set role none;

Role set.

SQL> select * from session_roles;

no rows selected

SQL> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
INSA                           MGR                            NO  NO  NO
INSA                           PROG                           NO  YES NO

mgr제외 롤 활성화
SQL> set role all except mgr;

Role set.

▶ 13. [sys session] ROLE 비밀번호삭제

mgr 비밀번호 삭제

alter role mgr not identified;

▶ 12. [insa session] 확인

SQL> conn insa/oracle

Connected.

SQL> select * from session_roles;

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

SQL> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
INSA                           MGR                            NO  YES NO
INSA                           PROG                           NO  YES NO

SQL> set role none;

Role set.

SQL> select * from session_roles;

no rows selected

SQL> set role all;

Role set.

SQL> select * from session_roles;

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

■ PL/SQL

Definer's right (만든 사람입장에서 프로그램 수행할꺼냐?)
Invoker's right (호출자 입장에서 프로그램 수행할꺼냐?)

▶ 1. [sys session]

green 유저생성, 롤 권한 부여

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;

▶ 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;
/
SQL> show error
No errors.

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  -- 이것을쓰면 Invoker's right (호출자 입장에서 실행)
is
begin
	insert into emp(employee_id, last_name, salary)
	values(p_id,p_name,p_sal);
	commit;
end;
/
SQL> select * from user_source where name = 'INSERT_EMP1';
SQL> select * from user_source where name = 'INSERT_EMP2';

green에게 프로시저 실행권한 주기

grant execute on insert_emp1 to green;
grant execute on insert_emp2 to green;
select * from user_tab_privs;

▶ 3. [green session]

SQL> select * from user_tab_privs;

무조건 보고 수행하자
(어떤 object인지, 형식인지)
SQL> desc hr.insert_emp1

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

SQL> desc hr.insert_emp2

PROCEDURE 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
  • definer's right
    : 만든사람 입장에서 프로시저가 실행하니깐
    hr의 emp 테이블에 insert 된다.
execute hr.insert_emp1(7777,'test1',5000)
  • invoker's right
    : 호출자 입장에서 프로시저가 실행되니깐
    green세션에서 실행하므로 green의 emp 테이블에 insert된다.
execute hr.insert_emp2(8888,'test2',6000)

▶ 4. [sys session] 타입존지역 바꾸기

select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;

[oracle@oracle ~]$ timedatectl

      Local time: Wed 2023-12-20 08:45:48 EST
  Universal time: Wed 2023-12-20 13:45:48 UTC
        RTC time: Wed 2023-12-20 13:45:47
       Time zone: America/New_York (EST, -0500)
     NTP enabled: no
NTP synchronized: no
 RTC in local TZ: no
      DST active: no
 Last DST change: DST ended at
                  Sun 2023-11-05 01:59:59 EDT
                  Sun 2023-11-05 01:00:00 EST
 Next DST change: DST begins (the clock jumps one hour forward) at
                  Sun 2024-03-10 01:59:59 EST
                  Sun 2024-03-10 03:00:00 EDT

타임존 지역바꾸기
[oracle@oracle ~]$ timedatectl set-timezone 'Asia/Seoul'

==== AUTHENTICATING FOR org.freedesktop.timedate1.set-timezone ===
Authentication is required to set the system timezone.
Authenticating as: root
Password:
==== AUTHENTICATION COMPLETE ===

[oracle@oracle ~]$ timedatectl

timedatectl
      Local time: Wed 2023-12-20 22:47:26 KST
  Universal time: Wed 2023-12-20 13:47:26 UTC
        RTC time: Wed 2023-12-20 13:47:26
       Time zone: Asia/Seoul (KST, +0900)
     NTP enabled: no
NTP synchronized: no
 RTC in local TZ: no
      DST active: n/a
  • application role
CREATE OR REPLACE PROCEDURE priv_mgr
AUTHID CURRENT_USER
IS
BEGIN
    IF to_char(sysdate,'hh24:mi') between '23:00' AND '23:30' THEN
        DBMS_SESSION.SET_ROLE('sec_app_role');   
    END IF;
END;
/
  • 이러한 역할 설정은 특정 시간대에만 특정 권한을 가진 역할을 부여하고자 할 때 사용될 수 있습니다. 예를 들어, 보안적인 이유로 특정 작업이나 특정 시간대에만 특정 역할을 가진 권한을 사용자에게 부여하는 경우에 유용할 수 있습니다.

프로시저 이용해서 application 롤 만들기

create role sec_app_role identified using priv_mgr;

롤한테 select any dictionary 권한부여

grant select any dictionary  to sec_app_role;

insa유저한테 프로시저 실행할 권한부여

grant execute on priv_mgr to insa;

▶ 5. [insa session]

SQL> select * from session_roles;

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

SQL> execute sys.priv_mgr

PL/SQL procedure successfully completed.

SQL> select * from session_roles;

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

SQL> select * from sys.tab$;

▶ 6. [sys session]

시간만 바꿧다 if절에 적용안되게

CREATE OR REPLACE PROCEDURE priv_mgr
AUTHID CURRENT_USER
IS
BEGIN
    IF to_char(sysdate,'hh24:mi') between '23:00' AND '23:10' THEN
        DBMS_SESSION.SET_ROLE('sec_app_role');
    ELSE
        DBMS_SESSION.SET_ROLE('NONE');
    END IF;
END;
/
  • sysdate는 sys의 시간대니깐 만약 하고 호출된 시간대로 설정하고싶으면 current_time

▶ 7. [insa session]

SQL> conn insa/oracle

Connected.

SQL> execute sys.priv_mgr

PL/SQL procedure successfully completed.

SQL> select * from session_roles;

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

SQL> select from sys.tab$;
select from sys.tab$

ERROR at line 1:
ORA-00942: table or view does not exist

서버에 연결하여 해당 서버의 시간 정보를 가져와서 시스템의 현재 시간을 업데이트합니다 (햔재 날짜 시간 정보 수정)
[root@oracle ~]# rdate -s time.bora.net
[root@oracle ~]# date
Thu Dec 21 15:34:28 KST 2023

■ profile 관리

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

▶ 1. [sys session]

select username, profile from dba_users;

select * from dba_profiles where profile = 'DEFAULT';

  • FAILED_LOGIN_ATTEMPTS : 암호 오류 허용 횟수
  • PASSWORD_LOCK_TIME : 암호 오류 허용 횟수를 넘었다면 자동으로 계정이 잠김(일수) (일반적으로 UNLIMITED)
  • PASSWORD_LIFE_TIME : 암호 유효 기간(일수)
  • PASSWORD_GRACE_TIME : 암호 만료 이후 암호 변경까지 유예기간(일수), 넘어가면 (EXPIRED & LOCKED)
  • PASSWORD_REUSE_TIME : 주어진 일 수 동안 암호를 재사용할 수 없도록 지정(일수)
  • PASSWORD_REUSE_MAX : 현재 암호를 재사용하기 위해 필요한 암호 변경횟수를 지정
    ex) and 개념이다. 무조건 둘다 설정해야한다.
    이전 암호를 재사용할려면 30일 이후에 암호를 1번을 바꾸면 재사용할 수 있다.
PASSWORD_REUSE_TIME	30
PASSWORD_REUSE_MAX	1

ex) 이전 암호는 무한으로 재사용할 수 있다.(보안상 안좋다.)

PASSWORD_REUSE_TIME	UNLIMITED
PASSWORD_REUSE_MAX	UNLIMITED
  • PASSWORD_VERIFY_FUNCTION : 암호의 복합성 검사, 함수로 구현해야한다.

$ORACLE_HOME/rdbms/admin/utlpwdmg.sql
을 이용해서 변경이 가능하다./:

FAILED_LOGIN_ATTEMPTS	PASSWORD	10
PASSWORD_LIFE_TIME	PASSWORD	180
PASSWORD_REUSE_TIME	PASSWORD	UNLIMITED
PASSWORD_REUSE_MAX	PASSWORD	UNLIMITED
PASSWORD_VERIFY_FUNCTION	PASSWORD	NULL
PASSWORD_LOCK_TIME	PASSWORD	1
PASSWORD_GRACE_TIME	PASSWORD	7

2023/12/22

  • sql 파일 실행
    : @ sql파일명

SQL> @ $ORACLE_HOME/rdbms/admin/utlpwdmg.sql

Function created.

Grant succeeded.

Profile altered.

Function created.

Grant succeeded.

▶ PROFILE 생성,적용,삭제

▶ 1. FAILED_LOGIN_ATTEMPTS, PASSWORD_LOCK_TIME

  • FAILED_LOGIN_ATTEMPTS : 암호 오류 허용 횟수
  • PASSWORD_LOCK_TIME : 암호 오류 허용 횟수를 넘었다면 자동으로 계정이 잠김(일수) (일반적으로 UNLIMITED)
  • 프로파일 생성
CREATE PROFILE insa_profile LIMIT
    FAILED_LOGIN_ATTEMPTS 3
    PASSWORD_LOCK_TIME UNLIMITED;
  • 프로파일 확인
SELECT * FROM dba_profiles where profile='INSA_PROFILE';
  • insa유저의 프로파일 변경
ALTER USER insa PROFILE insa_profile;

select username, profile from dba_users;
  • 3번틀리고 로그인했을때 lock 이걸린다.
SQL> conn insa/oracle

ERROR:
ORA-28000: the account is locked
  • account_status 와 락 시간 확인
select username, account_status,
to_char(lock_date,'yyyy/mm/dd hh24:mi:ss') from dba_users;
  • 락 해제
ALTER USER insa ACCOUNT UNLOCK;
  • 락 설정
ALTER USER insa ACCOUNT LOCK;
  • 프로파일 변경
ALTER PROFILE insa_profile LIMIT
    FAILED_LOGIN_ATTEMPTS 2
    PASSWORD_LOCK_TIME 1/1440;
  • 프로파일 삭제
  • CASECASE: 기존 DEFAULT로 바꿔준다.
DROP PROFILE insa_profile CASCADE;

▶ 2. PASSWORD_LIFE_TIME, PASSWORD_GRACE_TIME

  • PASSWORD_LIFE_TIME : 암호 유효 기간(일수)
  • PASSWORD_GRACE_TIME : 암호 만료 이후 암호 변경까지 유예기간(일수), 넘어가면 (EXPIRED & LOCKED)
  • 프로파일 생성
CREATE PROFILE insa_profile LIMIT
    PASSWORD_LIFE_TIME 2/1440
    PASSWORD_GRACE_TIME 1/1440;   
ALTER USER insa PROFILE insa_profile;
  • 확인
SELECT * FROM dba_profiles where profile='INSA_PROFILE';   
select username, profile from dba_users;
  • 만료날짜 확인
select username, account_status,
to_char(expiry_date,'yyyy/mm/dd hh24:mi:ss')
from dba_users
where username= 'INSA';
  • 프로시저때문에 만료가 되었다.
SQL> conn insa/oracle
ERROR:
ORA-28002: the password will expire within 0 days
  • 프로시저 때문에 바꿔야하는데 아까 적용한
    @ $ORACLE_HOME/rdbms/admin/utlpwdmg.sql 안에 FUNCTION verify_function_11G
    때문에 비밀번호 조건이 붙는다
SQL> conn insa/oracle

ERROR:
ORA-28001: the password has expired


Changing password for insa
New password:
Retype new password:
ERROR:
ORA-28003: password verification for the specified password failed
ORA-20001: Password length less than 8


Password unchanged
Warning: You are no longer connected to ORACLE.

▶ DEFUALT 프로파일 변경

  • DEFAULT 프로파일을 insa_profile로 변경
ALTER PROFILE insa_profile LIMIT
    PASSWORD_LIFE_TIME 180
    PASSWORD_GRACE_TIME 7
    PASSWORD_REUSE_TIME UNLIMITED
    PASSWORD_REUSE_MAX UNLIMITED
    FAILED_LOGIN_ATTEMPTS 10
    PASSWORD_LOCK_TIME UNLIMITED
    PASSWORD_VERIFY_FUNCTION NULL;
  • 프로파일확인
SELECT * FROM dba_profiles where profile='INSA_PROFILE';
  • insa 유저 패스워드 변경
ALTER USER insa identified by oracle;

▶ 3. PASSWORD_REUSE_TIME, PASSWORD_REUSE_MAX

  • PASSWORD_REUSE_TIME : 주어진 일 수 동안 암호를 재사용할 수 없도록 지정(일수)
  • PASSWORD_REUSE_MAX : 현재 암호를 재사용하기 위해 필요한 암호 변경횟수를 지정
  • 프로파일 2개 값만 변경
ALTER PROFILE insa_profile LIMIT
    PASSWORD_REUSE_TIME 2/1440
    PASSWORD_REUSE_MAX 1;
  • 확인
SELECT * FROM dba_profiles where profile='INSA_PROFILE';
  • 패스워드 2번 변경 -> 오류
ALTER USER insa identified by oracle;
ALTER USER insa identified by oracle;
ORA-28007: 비밀번호를 재사용될 수 없습니다
  • 전에 썻던 패스워드말고 변경 (바로바꿀려면 살짝 시간이걸린다.)
ALTER USER insa identified by oracle1;

나중에 회사에 패스워드 적용시킬려면
$ORACLE_HOME/rdbms/admin/utlpwdmg.sql 안에 넣어줘야하거나 추가할것, 변경할것 해주고 default 프로파일 변경해주면 된다.

ALTER PROFILE insa_profile LIMIT
PASSWORD_VERIFY_FUNCTION verify_function_itwill;

■ resource 관리

SELECT * FROM dba_profiles where profile = 'DEFAULT';
SESSIONS_PER_USER
IDLE_TIME
CONNECT_TIME
LOGICAL_READS_PER_CALL
LOGICAL_READS_PER_SESSION
CPU_PER_CALL
CPU_PER_SESSION
COMPOSITE_LIMIT
PRIVATE_SGA

거의 이것만 사용한다.

  • SESSIONS_PER_USER
    : 동일한 유저로 허용되는 동시 세션 수
  • IDLE_TIME
    : 분 단위로 측정한 비활성 시간 (5분이면, 5분동안 작업을 안하면 kill)
  • CONNECT_TIME
    : 분 단위로 측정한 연결 경과 시간 (접속해서 작업할수 있는 시간)
  • LOGICAL_READS_PER_CALL
    : 한 문장에서 읽어 들일수 있는 블록의 수(물리적, 논리적) 제한
    (논리적 I/O 안에 물리적 I/O가 들어가있다.)
  • LOGICAL_READS_PER_SESSION
    : 한 세션에서 읽어 들일수 있는 블록의 수(물리적,논리적) 제한
  • CPU_PER_CALL
    : 한 문장에서 사용가능한 CPU 시간, 1/100 초 단위로 측정한 총 시간을 설정 ex) 1000 -> 10초
  • CPU_PER_SESSION
    : 한 세션에서 사용가능한 CPU 시간, 1/100 초 단위로 측정한 총 시간을 설정
  • COMPOSITE_LIMIT
    : (CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION, PRIVATE_SGA) 의 가중합계로 자원 비용을 계산
  • PRIVATE_SGA
    : 바이트 단위로 측정한 SGA의 전용 공간 (shared server 환경(UGA))

▶ 1. IDLE_TIME

  • IDLE_TIME 을 1분으로 변경
alter profile insa_profile LIMIT
    IDLE_TIME 1;

SELECT * FROM dba_profiles where profile='INSA_PROFILE';
select * from v$parameter where name = 'resource_limit';
SQL>show parameter resource_limit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_limit                       boolean     FALSE
  • 리소스 제한을 true로 변경 (true 가 되어야 resource 제한할수 있다.)
alter system set resource_limit = true;

SQL> show parameter resource_limit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_limit                       boolean     TRUE


select * from v$parameter where name = 'resource_limit';

▶ 2. SESSIONS_PER_USER, CONNECT_TIME

  • 리소스 변경
alter profile insa_profile LIMIT
    IDLE_TIME 1
    CONNECT_TIME 1
    SESSIONS_PER_USER 1;
  • 1분이 지나면
SQL> conn sys/oracle
ERROR:
ORA-02002: error while writing to audit trail
ORA-00604: error occurred at recursive SQL level 1
ORA-02399: exceeded maximum connect time, you are being logged off
  • 2 개의 세션이 들어오면
[oracle@oracle ~]$ sqlplus insa/oracle

SQL*Plus: Release 11.2.0.4.0 Production on Fri Dec 22 14:48:45 2023

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

ERROR:
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit

■ ora password file 관리

: sysdba 롤을 받은 사용자에 대한 인증 체크

[sys session]

GRANT SYSDBA TO insa;

[insa session]

겉은 insa 유저이지만 속은 dba롤 이다

select * from dba_users;
select * from user_users;
[oracle@oracle db_1]$ cd $ORACLE_HOME/dbs
[oracle@oracle dbs]$ ls
hc_ora11g.dat  initora11g.ora  orapwora11g                spfileora11g.ora
init.ora       lkORA11G        spfileora11g_20231208.bak  spfileora11g.ora.bak
[oracle@oracle dbs]$ rm orapwora11g

client 에서 sysdba로 접속할려면 orapwora11g 인 패스워드 파일이 무조건 필요하다.

▶ ora password file 생성

  • ora 패스워드 파일 생성
show parameter instance_name

이용하여 SID를 확인한다음
orapwd $ORACLE_HOME/dbs/orapw<SID> password =패스워드

[oracle@oracle ~]$ orapwd file=$ORACLE_HOME/dbs/orapwora11g password=admin
-- password=admin : sys 계정의 비밀번호
-- 11g 부터 패스워드 대소문자 구분한다.
  • password file 을 다시 만들면 sys 계정은 내부적으로 들어가있지만 다른유저의 dba롤은 다시 권한을 부여해줘야한다.
    GRANT SYSDBA TO insa;

    ★ 하지만 여기서 주의할 사항은 sys 계정의 패스워드는 admin 이지만
    sysdba롤 권한을 받은 insa는 insa의 원래 패스워드를 사용해야한다.

▶ sysdba role 을 이용해서 오라클 접속할때 인증체크는?

  1. OS 인증 (LOCAL)
    [oracle@oracle ~]$ sqlplus / as sysdba
  2. client(remote) - server 환경에서는 ora password file을 생성한 인증 체크 방식

  • 일반적으로 sys 계정으로 들어가지않고
    sysdba롤을 받은 계정으로 들어간다.

  • password file로 인증 체크해서 접속한 유저 정보

select * from v$pwfile_users;
SQL> show parameter remote_login_passwordfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE
alter system set remote_login_passwordfile=exclusive scope=spfile;

▶ 패스워드 대소문자 구분

  • 패스워드 대소문자 구분
    : 11g 부터 패스워드는 대소문자 구분을 한다.
SQL> show parameter sec_case_sensitive_logon

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE
select * from v$parameter where name='sec_case_sensitive_logon';
  • 비밀번호 대소문자 구분 false 로 변경
alter system set sec_case_sensitive_logon = false;
  • 언제쓰이냐?
    : 보안상 좋지 않으므로 운영단에서는 쓰이지 않고 개발단에서 비밀번호 대소문자 구분안해서 lock걸릴때가 있으니깐 대소문자 구분 false로 한다.
  • 패스워드 admin이 아닌 대소문자 구분해서 ADMIN은 안된다.
[oracle@oracle ~]$ orapwd file=$ORACLE_HOME/dbs/orapwora11g password=admin ignorecase=n
  • 패스워드 대소문자 구분하지 않겠다.
    ignorecase=y
profile
YoonSeo Park
DB 공부를 하고 있는 사람입니다. 글을 읽어주셔서 감사하고 더 좋은 글을 쓰기 위해 노력하겠습니다 :)
이전 포스트

undo, redo, controlfile

다음 포스트

Audit(감사), 간단한 문제

0개의 댓글