유저 보안 관리
DATABASE User 생성
- 미리 정의된 DB 관리 계정
- sys : sysdba, sysoper로만 접속 가능
- DBA
- DD의 소유자
- OWR의 소유자
- system : DBA
- tool 등에 만드는 테이블의 소유자
- sys보다는 권한이 적다.
- dbsnmp : OMA와 통신을 하기 위한 유저(EM 관리작업)
- sysman : EM 관리 작업에 필요한 사용자
- db user 생성시 DBA가 해야하는 일련의 작업들
- 패스워드로 인증하는 유저 생성
- OS 인증 가능한 User 생성 하고 테스트하기
- 패스워드 관리를 위해 필요한 FUNCTION 생성
- PROFILE 설정
- 필요한 권한 부여
- 패스워드로 인증하는 유저 생성
SYS@orcl2> CREATE USER user1 IDENTIFIED BY newuser
PASSWORD EXPIRE
DEFAULT TABLESPACE users
QUOTA 1M ON users
TEMPORARY TABLESPACE temp
PROFILE DEFAULT
ACCOUNT UNLOCK ;
select * from database_properties;
-- 속성값을 확인
select * from dba_users
where username = 'USER1';
-- 등록된 유저를 확인
- PASSWORD
- DEFAULT TABLESPACE
- QUOTA
- 테이블스페이스의 허가된 할당량
- 기본적으로 모든 테이블스페이스의 quota는 0
- default tablespace에 대한 quota도 0
- ROLE(권한)
- DBA
- connect : create session
- resource : create table... unlimited tablespace(모든 생성되는 테이블 스페이스에 무제한 quota)
- 일반적으로 테이블 등 저장공간이 필요한 세그먼트를 생성하는 dbuser(OBJECT OWNER)에게 quota를 명시적으로 할당하지 않고 모든 테이블스페이스에 대한 무제한 quota를 사용할 수 있는 권한을 부여한다.
- UNLIMITED TABLESPACE => RESOURCE ROLE
- TEMPORARY TABLESPACE
- PROFILE
- 암호관리와 자원관리
- ACCOUNT
- 만들자마자 유저를 사용할지 안할지를 결정
- DB의 user
- Schema(유저가 소유한 object의 집합)와 유사하지만, 같은 것은 절대 아니다.
- object의 owner( 소유자 )
- 단위시스템
- HR
- ACCT
- PROD
- SH
- default tablspace
- quota
- object의 사용자
- ex) ymcho, allen ...
- OS 인증 가능한 User 생성하고 테스트
SYS@orcl2> show parameter os_authent_prefix
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix string ops$
-- os가 인증할때 사용하는 접두어.
SYS@orcl2> CREATE USER ops$oracle
IDENTIFIED EXTERNALLY ;
User created.
SYS@orcl2> grant create session to ops$oracle;
Grant succeeded.
-- 세션을 열 수 있는 권한을 부여
SYS@orcl2> conn /
Connected.
OPS$ORACLE@orcl2> show user
USER is "OPS$ORACLE"
- EXTERNALLY
- 외부 인증을 하겠다. 즉 OS인증을 가능하게 한다.
- 패스워드 없다.
- OS인증으로 접속하지 못하게 하는 법
- sqlnet.ora
- SQLNET.AUTHENTICATION_SERVICES=(NONE)
[orcl2:dbs]$ cd $ORACLE_HOME/network/admin
[orcl2:admin]$ vi sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES=(NONE)
:wq
[orcl2:admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 1 15:13:12 2019
Copyright (c) 1982, 2009, Oracle. All rights reserved.
ERROR:
ORA-01031: insufficient privileges
[orcl2:admin]$ vi sqlnet.ora
#SQLNET.AUTHENTICATION_SERVICES=(NONE)
:wq
[orcl2:admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 1 15:13:33 2019
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
관리자(sysdba, sysoper, sysasm) 인증 방법 2가지
- OS 인증
SYS@orcl2> !id
uid=500(oracle) gid=504(oinstall) groups=502(vboxsf),504(oinstall),505(dba)
[orcl2:test]$ sqlplus sdfhkas/asdjkhkasdh as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 1 14:15:41 2019
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
-- 이처럼 유저명과 패스워드를 엉망으로 적어도 OS인증이기때문에 sysdba유저로 접속이 가능하다.
- password file 인증
- 웹환경과 다른 위치에서 접속이 가능하다.
[orcl2:~]$ cd $ORACLE_HOME/dbs
[orcl2:dbs]$ ls orapw*
orapwPROD orapworcl orapworcl2
-- 요놈들이 password file이다.
-- Binary file이기 때문에 일반적인 텍스트 편집기로는 확인이 불가하다.
SYS@orcl2> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE
[orcl2:dbs]$ mv orapworcl2 orapworcl2.bak
-- 패스워드 파일을 삭제해보자.
-- 그 후에 다시 접속할시
[orcl2:dbs]$ sqlplus sys/oracle_4U@orcl2 as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 1 14:25:08 2019
Copyright (c) 1982, 2009, Oracle. All rights reserved.
ERROR:
ORA-01031: insufficient privileges
-- clinet tool인 SQL Developer로 sys유저로 접속하면 오류 발생
요청한 작업을 수행하는 중 오류 발생:
ORA-01031: 권한이 불충분합니다
01031. 00000 - "insufficient privileges"
*Cause: An attempt was made to perform a database operation without
the necessary privileges.
*Action: Ask your database administrator or designated security
administrator to grant you the necessary privileges
업체 코드 1031
-- 해결방법
[orcl2:dbs]$ orapwd file=orapworcl2 password=oracle_4U
[orcl2:dbs]$ ls orapw*
orapwPROD orapworcl orapworcl2 orapworcl2.bak
-- 생성됨을 확인할 수 있다.
[orcl2:dbs]$ sqlplus sys/oracle@orcl2 as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 1 14:29:22 2019
Copyright (c) 1982, 2009, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
-- 에러가 발생하기는 하나, 패스워드가 다르다는 에러로 변경된 것이 확인 가능하다.
[orcl2:dbs]$ sqlplus sys/oracle_4U@orcl2 as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 1 14:30:02 2019
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
-- 정상 접속됨.
SYS@orcl2> show parameter remote_login_passwordfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
- remote_login_passwordfile
- exclusive : 패스워드 파일 인증방법(default)
- none : os 인증방법만(관리자 계정으로 접속할때 패스워드로 접속을 못하게 하겠다)
- shared : RAC
- sysoper 사용자로 scott을 등록
SYS@orcl2> grant sysoper to scott;
Grant succeeded.
SYS@orcl2> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE
SCOTT FALSE TRUE FALSE
-- scott이 추가 된 것을 볼 수 있다.
[orcl2:dbs]$ sqlplus scott/tiger@orcl2 as sysoper
SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 1 14:34:43 2019
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
PUBLIC@orcl2> show user
USER is "PUBLIC"
PUBLIC@orcl2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
원래대로 복구
SYS@orcl2> alter system set remote_login_passwordfile=exclusive scope=spfile;
권한 부여 및 취소
- sh user의 lock을 풀고 password를 sh 로 변경
SYS@orcl2> select username, account_status from dba_users
where username='SH';
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
SH EXPIRED & LOCKED
SYS@orcl2> alter user sh identified by sh account unlock;
User altered.
SYS@orcl2> select username, account_status from dba_users
where username='SH';
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
SH OPEN
-- 변경됨을 확인.
- 권한의 종류
- 시스템 권한 : 데이터베이스에서 수행할수 있는 시스템 권한
- create table, create view, create trigger...
- select any table ( DB내의 모든 테이블 select 권한)
- cf) select_catalog_role ( DD 를 select 권한을 가진 role)
- 객체 권한 : 특정 data 를 엑세스하거나 조작할수있는 권한
- 예: select on emp , insert(column명) on emp, update(column명) on emp, delete on emp ......
- select의 컬럼을 제한하기 위해서는 view를 사용한다.
- all on emp
- 이러한 object 권한을 모두 준다.
- execute on compute_sal
- WAO
- WGO
SYS@orcl2> create user allen identified by tiger;
User created.
SYS@orcl2> create user jones identified by tiger;
User created.
SYS@orcl2> grant connect to allen, jones;
Grant succeeded.
-- connect 허용
SYS@orcl2> conn allen / tiger
Connected.
ALLEN@orcl2> conn jones/tiger
Connected.
문제. 내가 가지고 있는 시스템 권한이 무엇인지 확인하시오
ALLEN@orcl2> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
-- 세션을 열면서 가질수 있는 system_privileges
-- 즉 세션이 열어진 이후에 가지는 권한은 볼 수 없다.
-- 권한이 더 있을 수 있다는 뜻이다.
문제. scott 유져에서 allen 유져에게 scott 의 emp 테이블을 select 할수있는 권한을 부여하시오
SCOTT@orcl2> grant select on emp to allen;
Grant succeeded.
ALLEN@orcl2> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
...
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
문제. scott 에게 public synonym을 생성할 수 있는 권한을 부여하시오
SYS@orcl2> grant create public synonym to scott;
Grant succeeded.
SCOTT@orcl2> create public synonym emp
for scott.emp;
Synonym created.
ALLEN@orcl2> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
...
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
문제. SCOTT 의 객체권한이 누구에게 어떻게 부여되었는지 확인하는 방법은 ?
SCOTT@orcl2> SELECT owner || '.' || table_name object, privilege , grantable, grantee, grantor
FROM user_tab_privs;
OBJECT PRIVILEGE GRANTABLE GRANTEE GRANTOR
---------- ---------- ---------- ---------- --------
SCOTT.EMP SELECT YES ALLEN SCOTT
문제. allen 유져가 jones 유져에게 scott 의 emp 테이블을 select 할수있는 권한을 부여한다.
-- scott ------------> allen -----------> jones
SCOTT@orcl2> grant select on emp to allen with grant option;
Grant succeeded.
ALLEN@orcl2> grant select on scott.emp to jones;
Grant succeeded.
JONES@orcl2> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
...
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SCOTT@orcl2> SELECT owner || '.' || table_name object, privilege , grantable, grantee, grantor
FROM user_tab_privs;
OBJECT PRIVILEGE GRANTABLE GRANTEE GRANTOR
---------- ---------- ---------- ---------- --------
SCOTT.EMP SELECT YES ALLEN SCOTT
SCOTT.EMP SELECT NO JONES ALEEN
SCOTT@orcl2> revoke select on emp from allen;
Revoke succeeded.
-- allen의 권한이 철회되면서 jones에게 까지 연쇄적으로 철회
SCOTT@orcl2> SELECT owner || '.' || table_name object, privilege , grantable, grantee, grantor
FROM user_tab_privs;
no rows selected
- system 권한
SCOTT@orcl2> conn system/oracle_4U
Connected.
SYSTEM@orcl2> grant create table to allen with admin option;
Grant succeeded.
ALLEN@orcl2> grant create table to jones;
Grant succeeded.
SYSTEM@orcl2> revoke create table from allen;
Revoke succeeded.
ALLEN@orcl2> create table a(x number);
create table a(x number)
*
ERROR at line 1:
ORA-01031: insufficient privileges
JONES@orcl2> create table a (x number);
Table created.
JONES@orcl2> select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
JONES CREATE TABLE NO
JONES@orcl2> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE
--
JONES@orcl2> select * from user_tab_privs;
no rows selected
SYS@orcl2> create user demo identified by demo;
User created.
SYS@orcl2> conn demo/demo
ERROR:
ORA-01045: user DEMO lacks CREATE SESSION privilege; logon denied
SYS@orcl2> grant create session to demo;
Grant succeeded.
SYS@orcl2> conn demo/demo
Connected.
SYS@orcl2> grant connect, resource to demo;
Grant succeeded.
- object 권한
SCOTT@orcl2> grant select on emp to demo;
Grant succeeded.
SCOTT@orcl2> grant update(sal, deptno) on emp to demo;
Grant succeeded.
DEMO@orcl2> select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DEMO UNLIMITED TABLESPACE NO
DEMO CREATE SESSION NO
-- UNLIMITED TABLESPACE는 resource권한에 들어있다.
DEMO@orcl2> select * from user_role_privs;
USERNAME GRANTED_ROLE ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
DEMO CONNECT NO YES NO
DEMO RESOURCE NO YES NO
-- ROLE 권한을 볼 수 있다.
DEMO@orcl2> select * from role_sys_privs;
ROLE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
RESOURCE CREATE SEQUENCE NO
RESOURCE CREATE TRIGGER NO
RESOURCE CREATE CLUSTER NO
RESOURCE CREATE PROCEDURE NO
RESOURCE CREATE TYPE NO
CONNECT CREATE SESSION NO
RESOURCE CREATE OPERATOR NO
RESOURCE CREATE TABLE NO
RESOURCE CREATE INDEXTYPE NO
-- 어떠한 권한이 들어있는지 볼 수 있다.
DEMO@orcl2> select * from user_tab_privs;
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE
---------- -------- ----------- ------- --------- --------- ---------
DEMO SCOTT EMP SCOTT SELECT NO NO
-- 객체 권한을 확인
- user에게 부여된 권한
- user_sys_privs
- user_role_privs - role_sys_privs
- user_tab_privs
- user_col_privs
-- user에게 부여된 권한을 전부 확인하는 법
DEMO@orcl2>
select null role, privilege, null owner, null table_name, null column_name
from user_sys_privs
union all
select role, privilege, null, null, null
from role_sys_privs
union all
select null, privilege, owner, table_name, null
from user_tab_privs
union all
select null, privilege, owner, table_name, column_name
from user_col_privs
order by 1,2;
ROLE PRIVILEGE OWNER TABLE_NAME COLUMN_NAME
-------------------- -------------------- -------------------- -------------------- ---------------
CONNECT CREATE SESSION
RESOURCE CREATE CLUSTER
RESOURCE CREATE INDEXTYPE
RESOURCE CREATE OPERATOR
RESOURCE CREATE PROCEDURE
RESOURCE CREATE SEQUENCE
RESOURCE CREATE TABLE
RESOURCE CREATE TRIGGER
RESOURCE CREATE TYPE
CREATE SESSION
SELECT SCOTT EMP
UNLIMITED TABLESPACE
UPDATE SCOTT EMP DEPTNO
UPDATE SCOTT EMP SAL
14 rows selected.
롤 생성 및 관리
- 관리가 편해진다.
- 성능이 빨라진다.
- 보안을 더 강화할 수 있다.
SYS@orcl2> select * from dba_roles;
-- 미리 정의된 ROLE을 볼 수 있다.
ROLE PASSWORD AUTHENTICAT
------------------------------ -------- -----------
CONNECT NO NONE
RESOURCE NO NONE
DBA NO NONE
......
OWB$CLIENT YES PASSWORD
OWB_DESIGNCENTER_VIEW NO NONE
55 rows selected.
- ROLE 생성
SYS@orcl2> create role role_select;
Role created.
SYS@orcl2> create role role_update;
Role created.
- ROLE 권한 부여 및 확인
SYS@orcl2> grant select on scott.emp to role_select;
Grant succeeded.
SYS@orcl2> grant create session to role_select;
Grant succeeded.
SYS@orcl2> grant update on scott.emp to role_update;
Grant succeeded.
SYS@orcl2> grant create session to role_update;
Grant succeeded.
SYS@orcl2> select * from role_sys_privs
where role in ('ROLE_SELECT', 'ROLE_UPDATE');
ROLE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
ROLE_UPDATE CREATE SESSION NO
ROLE_SELECT CREATE SESSION NO
SYS@orcl2> select * from role_tab_privs
where role in ('ROLE_SELECT', 'ROLE_UPDATE');
ROLE OWNER TABLE_NAME COLUMN_NAME PRIVILEGE GRA
--------------- --------------- --------------- --------------- --------------- ---
ROLE_SELECT SCOTT EMP SELECT NO
ROLE_UPDATE SCOTT EMP UPDATE NO
- ROLE을 사용자에게 할당
SYS@orcl2> create user test identified by test;
User created.
SYS@orcl2> select * from dba_users where username='TEST';
-- user의 정보를 확인한다.
SYS@orcl2> grant role_select, role_update to test;
Grant succeeded.
TEST@orcl2> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
.......
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
TEST@orcl2> update scott.emp set sal = 1000;
14 rows updated.
-- 정상적으로 select와 update가 되는 것을 확인 할 수 있다.
TEST@orcl2> rollback;
Rollback complete.
TEST@orcl2> select * from user_role_privs;
USERNAME GRANTED_ROLE ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
TEST ROLE_SELECT NO YES NO
TEST ROLE_UPDATE NO YES NO
TEST@orcl2> select * from role_sys_privs;
ROLE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
ROLE_UPDATE CREATE SESSION NO
ROLE_SELECT CREATE SESSION NO
TEST@orcl2> select * from role_tab_privs;
ROLE OWNER TABLE_NAME COLUMN_NAME PRIVILEGE GRA
--------------- --------------- --------------- --------------- --------------- ---
ROLE_SELECT SCOTT EMP SELECT NO
ROLE_UPDATE SCOTT EMP UPDATE NO
TEST@orcl2> select null role, privilege, null owner, null table_name, null column_name
from user_sys_privs
union all
select role, privilege, null, null, null
from role_sys_privs
union all
select role, privilege, owner, table_name, null
from role_tab_privs
union all
select null, privilege, owner, table_name, null
from user_tab_privs
union all
select null, privilege, owner, table_name, column_name
from user_col_privs
order by 1,2;
ROLE PRIVILEGE OWNER TABLE_NAME COLUMN_NAME
--------------- --------------- --------------- --------------- ---------------
ROLE_SELECT CREATE SESSION
ROLE_SELECT SELECT SCOTT EMP
ROLE_UPDATE CREATE SESSION
ROLE_UPDATE UPDATE SCOTT EMP
- Default Role
- Default Role이란
- 세션을 열때 활성화되는 role
- default role을 제외한 role은 모두 비활성화
- Default Role Test
- test 사용자로 로그인해서 권한을 사용
SYS@orcl2> alter user test default role role_select;
User altered.
TEST@orcl2> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
......
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
TEST@orcl2> update scott.emp set sal=1000;
update scott.emp set sal=1000
*
ERROR at line 1:
ORA-01031: insufficient privileges
-- select는 되지만, update는 안되는 것을 볼 수 있다.
TEST@orcl2> set role role_update;
Role set.
-- PL/SQL 의 DBMS_SESSION.SET_ROLE('role_update');로 활성화 시킬수도 있다.
TEST@orcl2> update scott.emp set sal=1000;
14 rows updated.
TEST@orcl2> select * from scott.emp;
select * from scott.emp
*
ERROR at line 1:
ORA-01031: insufficient privileges
-- update는 되지만, select는 되지 않는 것을 볼 수 있다.
-- default role 1개를 활성화 시키면 나머지가 전부 비활성화 된다.
TEST@orcl2> rollback;
Rollback complete.
TEST@orcl2> set role role_update;
Role set.
TEST@orcl2> conn / as sysdba
Connected.
SYS@orcl2> conn test/test
Connected.
TEST@orcl2> update scott.emp set sal=1000;
update scott.emp set sal=1000
*
ERROR at line 1:
ORA-01031: insufficient privileges
-- 접속을 끊었다 다시 연결하면 끊기전에 role_update로 설정되어있어도 default role로 변경된다.
- Secure application role test
- ROLE : secure_role
- PROCEDURE : secure_application_role
SYS@orcl2> create user user2 identified by oracle;
User created.
--아래의 프로시져 생성
SYS@orcl2>
CREATE OR REPLACE PROCEDURE system.secure_role_proc
AUTHID CURRENT_USER
IS
BEGIN
IF to_char(sysdate, 'HH24:MI') BETWEEN '08:00' AND '18:00' THEN
RAISE_APPLICATION_ERROR(-20001,'DML only business hours');
ELSE
DBMS_SESSION.SET_ROLE('secure_role');
END IF;
END;
/
Procedure created.
======Secure Applocation Role 생성=================================================================
SYS@orcl2> create role secure_role
identified USING system.secure_role_proc;
Role created.
SYS@orcl2> GRANT all ON hr.employees TO secure_role;
Grant succeeded.
===================================================================================================
======필요한 Role, Privilege 부여===================================================================
SYS@orcl2> GRANT secure_role TO user2;
Grant succeeded.
SYS@orcl2> GRANT create session TO user2;
Grant succeeded.
SYS@orcl2> GRANT execute on system.secure_role_proc TO user2;
Grant succeeded.
===================================================================================================
======DEFAULT ROLE 정의============================================================================
SYS@orcl2> ALTER USER user2
DEFAULT ROLE NONE;
User altered.
===================================================================================================
USER2@orcl2> select * from hr.employees;
select * from hr.employees
*
ERROR at line 1:
ORA-00942: table or view does not exist
-- 권한이 없어 검색되지 않는다.
USER2@orcl2> exec system.secure_role_proc;
BEGIN system.secure_role_proc; END;
*
ERROR at line 1:
ORA-20001: DML only business hours
ORA-06512: at "SYSTEM.SECURE_ROLE_PROC", line 6
ORA-06512: at line 1
-- 예외처리에 걸린다.
SYS@orcl2>
CREATE OR REPLACE PROCEDURE system.secure_role_proc
AUTHID CURRENT_USER
IS
BEGIN
IF to_char(sysdate, 'HH24:MI') BETWEEN '08:00' AND '18:00' THEN
DBMS_SESSION.SET_ROLE('secure_role') ;
ELSE
RAISE_APPLICATION_ERROR(-20001,'DML only business hours') ;
END IF;
END;
/
-- 프로시저를 수정한다
USER2@orcl2> exec system.secure_role_proc;
PL/SQL procedure successfully completed.
-- 프로시저를 실행한다.
USER2@orcl2> select * from hr.employees;
USER2@orcl2> update hr.employees set salary = 1000;
USER2@orcl2> rollback;
USER2@orcl2> conn / as sysdba
Connected.
SYS@orcl2> conn user2/oracle
Connected.
USER2@orcl2> select * from hr.employees;
select * from hr.employees
*
ERROR at line 1:
ORA-00942: table or view does not exist
-- 재접속을 하면 role이 default값으로 설정된다.
프로파일 생성 및 관리
- profile 을 사용해야하는 이유 ?
- 오라클의 자원 사용에 대한 제한을 둘수 있다 .
- 특정유져가 오라클의 자원을 무한히 사용하지 못하도록 제한을 두는 기능
- password 관리 기능
- 악성 SQL 수행 못하게 -- 자원관리
- 로그인시 패스워드를 여러번 틀리면 잠겨버리게 -- 암호관리
SYS@orcl2> select username, profile from dba_users where username = 'SCOTT';
USERNAME PROFILE
------------------------------ ------------------------------
SCOTT DEFAULT
SYS@orcl2> select * from dba_profiles;
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ---------------
MONITORING_PROFILE COMPOSITE_LIMIT KERNEL DEFAULT
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
MONITORING_PROFILE SESSIONS_PER_USER KERNEL DEFAULT
DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
......
MONITORING_PROFILE PASSWORD_LOCK_TIME PASSWORD DEFAULT
DEFAULT PASSWORD_LOCK_TIME PASSWORD 1
MONITORING_PROFILE PASSWORD_GRACE_TIME PASSWORD DEFAULT
DEFAULT PASSWORD_GRACE_TIME PASSWORD 7
32 rows selected.
[orcl2:~]$ cd $ORACLE_HOME/rbms/admin/
[orcl2:admin]$ cp utlpwdmg.sql /home/oracle/test/.
[orcl2:admin]$ cd /home/oracle/test/
[orcl2:test]$ vi utlpwdmg.sql
.....
-- This script alters the default parameters for Password Management
-- This means that all the users on the system have Password Management
-- enabled and set to the following values unless another profile is
-- created with parameter values set to different value or UNLIMITED
-- is created and assigned to the user.
/*
ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 180
PASSWORD_GRACE_TIME 7
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LOCK_TIME 1
PASSWORD_VERIFY_FUNCTION verify_function_11G;
*/ 이부분 주석처리
......
-- 현재 verify_function 과 verify_function_11g가 존재
SYS@orcl2> @utlpwdmg
Function created.
Function created.
- scott 사용자에게 할당된 profile 확인
SYS@orcl2>
select username, profile
from dba_users
where username = 'SCOTT';
- profile의 변경
SYS@orcl2> alter profile default limit
failed_login_attempts 3
password_lock_time 1/1440 ; -- 기간 (일)
-- 패스워드 3번 틀리면 계정을 1분 동안 잠궈버린다는 의미
SYS@orcl2> conn scott/asdlj
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
@> conn scott/asldjkasd
ERROR:
ORA-01017: invalid username/password; logon denied
@> conn scott/afhlsad
ERROR:
ORA-01017: invalid username/password; logon denied
-- 3번 틀리기
@> conn scott/tiger
ERROR:
ORA-28000: the account is locked
-- 이후 정상접속하면 lock이 걸려있음
@> conn scott/tiger
Connected.
-- 1분 뒤 접속하면 접속된다.
SYS@orcl2> alter user scott
account unlock;
-- 또는 다음과 같이 강제로 해제할수 있다.
SYS@orcl2> show parameter resource_limit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_limit boolean FALSE
-- TRUE 값이여야 자원관리가 가능하다.
SYS@orcl2> alter system set resource_limit = true;
System altered.
문제. 프로파일을 새로 만들어서 idle_time 을 3분으로 설정해서 allen에게 그 프로파일을 할당하고 테스트 하시오.
SYS@orcl2> create profile idle_profile limit
idle_time 3;
Profile created.
SYS@orcl2> select * from dba_profiles
where profile='IDLE_PROFILE';
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- -------------------------
IDLE_PROFILE COMPOSITE_LIMIT KERNEL DEFAULT
IDLE_PROFILE SESSIONS_PER_USER KERNEL DEFAULT
IDLE_PROFILE CPU_PER_SESSION KERNEL DEFAULT
IDLE_PROFILE CPU_PER_CALL KERNEL DEFAULT
IDLE_PROFILE LOGICAL_READS_PER_SESSION KERNEL DEFAULT
IDLE_PROFILE LOGICAL_READS_PER_CALL KERNEL DEFAULT
IDLE_PROFILE IDLE_TIME KERNEL 3
IDLE_PROFILE CONNECT_TIME KERNEL DEFAULT
IDLE_PROFILE PRIVATE_SGA KERNEL DEFAULT
IDLE_PROFILE FAILED_LOGIN_ATTEMPTS PASSWORD DEFAULT
IDLE_PROFILE PASSWORD_LIFE_TIME PASSWORD DEFAULT
IDLE_PROFILE PASSWORD_REUSE_TIME PASSWORD DEFAULT
IDLE_PROFILE PASSWORD_REUSE_MAX PASSWORD DEFAULT
IDLE_PROFILE PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
IDLE_PROFILE PASSWORD_LOCK_TIME PASSWORD DEFAULT
IDLE_PROFILE PASSWORD_GRACE_TIME PASSWORD DEFAULT
SYS@orcl2> select username, profile from dba_users
where username='ALLEN';
USERNAME PROFILE
------------------------------ ------------------------------
ALLEN DEFAULT
SYS@orcl2> alter user allen profile idle_profile;
User altered.
SYS@orcl2> select username, profile from dba_users
where username='ALLEN';
USERNAME PROFILE
------------------------------ ------------------------------
ALLEN IDLE_PROFILE
-- ALLEN 유저가 profile이 적용되는지를 확인
SYS@orcl2> conn allen/tiger
Connected.
ALLEN@orcl2> select sysdate from dual;
SYSDATE
---------
02-APR-19
ALLEN@orcl2> set time on
14:35:50
-- 3분있다 select를 다시 해보자.
14:39:18 ALLEN@orcl2> select sysdate from dual;
select sysdate from dual
*
ERROR at line 1:
ORA-02396: exceeded maximum idle time, please connect again
-- 끊긴 것을 확인할 수 있다.
SYS@orcl2> select profile from dba_users where username='ALLEN';
PROFILE
------------------------------
IDLE_PROFILE
- passowrd verify의 설정
SYS@orcl2> alter profile default limit
password_verify_function verify_function;
Profile altered.
SYS@orcl2> alter user scott password expire;
User altered.
SYS@orcl2> select username, account_status
from dba_users
where username = 'SCOTT';
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
SCOTT EXPIRED
SYS@orcl2> conn scott/tiger
ERROR:
ORA-28001: the password has expired
Changing password for scott
New password: abc
Retype new password: abc
ERROR:
ORA-28003: password verification for the specified password failed
ORA-20002: Password length less than 4
-- 4글자가 안 넘으므로 에러발생, 패스워드가 만들어지지 않는다.
@> conn scott/tiger
ERROR:
ORA-28001: the password has expired
Changing password for scott
New password: oracle_4U
Retype new password: oracle_4U
Password changed
Connected.
-- 정상적으로 만들어진다.
SYS@orcl2> select table_name from dict
where table_name like'%QUOTA%';
TABLE_NAME
------------------------------
USER_TS_QUOTAS
DBA_TS_QUOTAS
select * from dba_profiles
where profile='DEFAULT;
SYS@orcl2> select * from dba_profiles
where profile='DEFAULT';
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- -------------------------
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
DEFAULT CPU_PER_CALL KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED
DEFAULT IDLE_TIME KERNEL UNLIMITED
DEFAULT CONNECT_TIME KERNEL UNLIMITED
DEFAULT PRIVATE_SGA KERNEL UNLIMITED
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 3
DEFAULT PASSWORD_LIFE_TIME PASSWORD 180
DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD VERIFY_FUNCTION
DEFAULT PASSWORD_LOCK_TIME PASSWORD .0006
DEFAULT PASSWORD_GRACE_TIME PASSWORD 7
16 rows selected.
SYS@orcl2> alter profile default limit
password_verify_function null
password_lock_time unlimited;
Profile altered.
SYS@orcl2> drop profile
idle_profile cascade;
Profile dropped.
-- idle_profile의 값을 default로 바꿔준다.
SYS@orcl2> select profile from dba_users
where username='ALLEN';
PROFILE
------------------------------
DEFAULT
-- DEFAULT값으로 변경된 것을 확인 할 수 있다.
SYS@orcl2> conn scott/oracle_4U
Connected.
SCOTT@orcl2> alter user scott identified by tiger;
User altered.
-- verify_function이 비활성화 되어있으므로 패스워드를 tiger로 변경이 가능.
-- 지금까지의 실습을 원래대로 복구
SYS@orcl2> DROP USER user1 ;
SYS@orcl2> DROP USER user2 ;
SYS@orcl2> DROP USER ops$oracle ;
SYS@orcl2> DROP PROFILE test_p;
SYS@orcl2> drop profile idle_profile;
SYS@orcl2> DROP FUNCTION verify_function ;
SYS@orcl2> DROP ROLE secure_role ;
SYS@orcl2> DROP PROCEDURE system.secure_role_proc ;
SYS@orcl2> select * from dba_profiles
where profile='DEFAULT';
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- -------------------------
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
DEFAULT CPU_PER_CALL KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED
DEFAULT IDLE_TIME KERNEL UNLIMITED
DEFAULT CONNECT_TIME KERNEL UNLIMITED
DEFAULT PRIVATE_SGA KERNEL UNLIMITED
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 3
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 UNLIMITED
DEFAULT PASSWORD_GRACE_TIME PASSWORD 7
16 rows selected.
-- profile들을 다시 UNLIMITED로 변경
SYS@orcl2>
alter profile default limit
password_life_time unlimited
failed_login_attempts unlimited
password_grace_time unlimited;
-- 변경된 것을 확인.
SYS@orcl2> select * from dba_profiles
where profile='DEFAULT';
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- -------------------------
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
DEFAULT CPU_PER_CALL KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED
DEFAULT IDLE_TIME KERNEL UNLIMITED
DEFAULT CONNECT_TIME KERNEL UNLIMITED
DEFAULT PRIVATE_SGA KERNEL UNLIMITED
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED
DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
DEFAULT PASSWORD_LOCK_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_GRACE_TIME PASSWORD UNLIMITED
<<<<복습>>>>
1. ALLEN 에게 SCOTT의 EMP 테이블을 조회할 수 있는 권한을 확인하고 관련 dictionary에서 권한을 확인하세요.
SYS@orcl2> grant all on scott.emp to allen;
Grant succeeded.
ALLEN@orcl2> select * from user_tab_privs;
2. ALLEN 세션을 열어서 SCOTT의 EMP 테이블을 본인의 테이블처럼 조회할 수 있도록 private synonym을 생성하시오.필요하면 DBA에게 권한을 요청하여 생성합니다.
SYS@orcl2> grant create synonym to allen;
Grant succeeded.
ALLEN@orcl2> create synonym emp
for scott.emp;
Synonym created.
ALLEN@orcl2> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
'DATABASE(oracleDB 11g) > DBA' 카테고리의 다른 글
| 복습문제 (0) | 2019.04.05 |
|---|---|
| [Oracle DBA]데이터 동시성 관리 (0) | 2019.04.03 |
| [Oracle DBA]데이터베이스 저장 영역 구조 관리 (0) | 2019.03.28 |
| [Oracle DBA]Oracle 네트워크 관리 (0) | 2019.03.27 |
| [Oracle DBA]ASM Instance 관리 (0) | 2019.03.26 |