데이터베이스 2017. 6. 30. 08:00

오라클 Hidden Column - SYS_로 시작하는 컬럼


Function Based Index 또는 Descending Index로 인덱스 생성 시 Dictionary 테이블(ALL_IND_COLUMNS)의 인덱스 컬럼이 SYS_로 시작하는 알 수 없는 컬럼으로 조회가 됩니다.  또는 ALL_TAB_COLS 테이블에는 생성하지 않는 컬럼(SYS_로 시작하는)이 조회됩니다. 테이블 정의서 작성을 위해 위의 Dictionary 테이블을 사용할 때 주의하세요!



Descending Index 생성 및 Dictionary 테이블 조회


1. 테스트 테이블 생성 및 Descending Index 생성


1
2
3
4
5
6
7
8
9
10
/* 테이블 생성 */
CREATE TABLE TB_IND_TEST(
COL1            VARCHAR2(10),
COL2            VARCHAR2(20),
COL3            VARCHAR2(30) GENERATED ALWAYS AS ( COL1 || COL2 ) VIRTUAL
);
 
/* Descending Index 생성 */
CREATE INDEX IDX_TB_IND_TEST_01 
ON TB_IND_TEST (COL1 DESC, COL2 DESC);
cs


ALL_TAB_COLS 테이블의 HIDDEN_COLUMN항목과 VIRTUAL_COLUMN의 차이를 알기 위해 테이블을 생성 시 COL3 컬럼을 가상컬럼으로 생성하였습니다.


가상컬럼 SYNTAX

COLUMN_NAME [DATA_TYPE] [GENERATED ALWAYS] AS ( expression ) [VIRTUAL]


2. Dictionary 테이블 조회


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
/* 인덱스 컬럼 조회 */
SELECT T.INDEX_NAME
     , T.TABLE_NAME
     , T.COLUMN_NAME
  FROM ALL_IND_COLUMNS T
 WHERE 1=1
   AND T.TABLE_NAME = 'TB_IND_TEST'
;
 
/* ALL_TAB_COLUMNS 테이블 조회 */
SELECT T.TABLE_NAME
     , T.COLUMN_NAME
     , T.DATA_TYPE
  FROM ALL_TAB_COLUMNS T
 WHERE 1=1
   AND T.TABLE_NAME = 'TB_IND_TEST'
 ORDER BY T.COLUMN_ID
;
 
/* ALL_TAB_COLS 테이블 조회 */
SELECT T.TABLE_NAME
     , T.COLUMN_NAME
     , T.DATA_TYPE
     , T.HIDDEN_COLUMN
     , T.VIRTUAL_COLUMN
  FROM ALL_TAB_COLS T
 WHERE 1=1
   AND T.TABLE_NAME = 'TB_IND_TEST'
 ORDER BY T.COLUMN_ID
;
cs


- ALL_TAB_COLS 조회 결과



ALL_TAB_COLUMNS 테이블에서는 조회되는 않는 SYS_로 시작되는 컬럼이 조회가 되었습니다. Descending Index의 가상컬럼이자 히든컬럼으로 생성되었습니다.

그리고 가상컬럼으로 생성한 COL3 컬럼은 가상컬럼이지만, HIDDEN COLUMN은 아닙니다.


'데이터베이스' 카테고리의 다른 글

오라클 권한 조회  (0) 2017.06.28
오라클 계정과 권한  (0) 2017.06.23
오라클 TABLESPACE와 DATAFILE  (0) 2017.06.16
TIMESTAMP 연산  (0) 2017.05.11
오라클 - PIVOT과 UNPIVOT  (0) 2017.02.02
posted by 생각퍼즐
:
데이터베이스 2017. 6. 28. 08:00

오라클 권한 조회


사용자 및 ROLE에 부여된 권한조회하기



데이터베이스 작업 시 "권한이 불충분합니다."라는 오류 메시지와 마주할 때가 있습니다. 그럴때는 당황하지 마시고 현재 사용자의 권한을 확인하여 처리하시면 됩니다.


사용자 생성


권한부여 및 조회를 위한 테스트 계정을 만들어보겠습니다.


- SYNTAX

CREATE USER USER_NAME IDENTIFIED BY PASSWORD

[ DEFAULT TABLESPACE TABLESPACE_NAME ]

[ TEMPORARY TABLESPACE TEMP ]

;


- EX)

CREATE USER TEST IDENTIFIED BY TEST1234

DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TEMP;


※ 사용자 삭제

- SYNTAX

DROP USER USER_NAME [CASCADE];


- EX)

DROP USER TEST_USER CASCADE;


- CASCADE : 사용자에게 부여된 권한이나 사용자가 만든 객체가 존재할 경우 CASCADE 옵션을 사용해야 해당 사용자를 삭제할 수 있음



데이터베이스 접속 테스트


사용자가 정상적으로 생성되었는지 데이터베이스에 접속을 시도하면

아래와 같은 오류가 발생됩니다.


sqlplus TEST_USER/TEST1234@ORCL


ERROR: ORA-01045: user TEST_USER lacks CREATE SESSION privilege; logon denied


사용자에게 접속권한이 없다고 ERROR 메시지를 던져주네요!



권한부여

sqlplus / as sysdba

SQL> GRANT CREATE SESSION, CREATE TABLE TO TEST_USER;


-접속확인

SQL> conn TEST_USER/TEST1234@ORCL


※ 사용자에게 권한 부여 및 회수와 관련된 자세한 내용은 오라클 계정과 권한 포스팅을 참고하세요!



권한조회


1. USER 또는 ROLE에 부여된 시스템 권한조회


SELECT GRANTEE, PRIVILEGE, ADMIN_OPTION

 FROM DBA_SYS_PRIVS;

또는

SELECT USERNAME, PRIVILEGE, ADMIN_OPTION

 FROM USER_SYS_PRIVS;


- GRANTEE : 시스템 권한을 부여받은 USER 또는 ROLE

- USERNAME : 현재 접속된 사용자

- PRIVILEGE : 부여받은 시스템 권한

- ADMIN_OPTION : 부여받은 시스템 권한을 다른 사용자에게 부여할 수 있음(YES일 경우)


2. USER 또는 ROLE에 부여된 객체 권한조회


SELECT GRANTEE, OWNER, TABLE_NAME, PRIVILEGE, GRANTOR

 FROM DBA_TAB_PRIVS

또는

SELECT GRANTEE, OWNER, TABLE_NAME, PRIVILEGE, GRANTOR

 FROM USER_TAB_PRIVS


- GRANTEE : 객체 권한을 부여받은 USER 또는 ROLE

- OWNER : 객체의 소유자

- TABLE_NAME : 객체

- GRANTOR : 객체 권한을 부여해준 USER


3. USER 또는 ROLE에 부여된 ROLE 조회


SELECT *
 FROM DBA_ROLE_PRIVS
또는
SELECT *
 FROM USER_ROLE_PRIVS

- GRANTED_ROLE : 부여된 ROLE
- DEFAULT_ROLE : DEFAULT_ROLE 여부


※ DEFAULT ROLE

   - 데이터베이스 설치 시 자동으로 생성되는 ROLE

   - 대표적인 Default Role은 CONNECT, RESOURCE가 있음

   - RESOURCE 롤은 UNLIMITED_TABLESPACE라는 권한이 할당되어 있음

   - UNLIMITED_TABLESPACE 권한을 가진 사용자는 데이터베이스에 존재하는 모든 테이블      스페이스를 저장공간으로 사용가능 

'데이터베이스' 카테고리의 다른 글

오라클 Hidden Column - SYS_로 시작하는 컬럼  (1) 2017.06.30
오라클 계정과 권한  (0) 2017.06.23
오라클 TABLESPACE와 DATAFILE  (0) 2017.06.16
TIMESTAMP 연산  (0) 2017.05.11
오라클 - PIVOT과 UNPIVOT  (0) 2017.02.02
posted by 생각퍼즐
:
데이터베이스 2017. 6. 23. 14:10

오라클 계정과 권한


SYS와 SYSTEM 계정


1. SYS 계정

  • 오라클 데이터베이스가 설치되면 자동으로 생성되는 계정(슈퍼 사용자)
  • Dictionary Table의 소유자
  • SYS 스키마에는 테이블 생성 불가(데이터베이스에 의해서만 수정가능)
  • sysdba로 접속할 때 기본 스키마
  • 데이터베이스 생성가능(sysdba privilege를 가짐)


2. SYSTEM

  • SYS계정과 마찬가지로 데이터베이스가 설치되면서 생성되는 계정
  • Dictionary View 소유자
  • 데이터베이스 생성 불가
  • 데이터베이스 관리에 필요한 table 및 view 생성


권한
  • 시스템 권한 : 데이터베이스 접속, 사용자 및 오브젝트 생성 등
  • 오브젝트 권한 : 오브젝트 사용에 대한 권한

1. 시스템 권한


시스템 권한 (CREATE, ALTER, DROP)

설명 

 CREATE USER

 사용자 생성 권한 

 CREATE SESSION

 데이터베이스 접속 권한 

 CREATE ANY TABLE

 모든 사용자의 테이블 생성 권한 

 SELECT ANY TABLE

 모든 사용자의 테이블 조회 권한 

 CREATE TABLE

 테이블 생성 권한 

 CREATE VIEW

 뷰 생성 권한 

 CREATE PROCEDURE

 프로시저 생성 권한 

 CREATE SEQUENCE

 시퀀스 생성 권한 

 SYSDBA

 데이터베이스 관리를 위한 슈퍼권한
 SYSOPER 데이터베이스 관리 권한



1.1 SYSDBA와 SYSOPER의 상세권한


권한 

 설명

 SYSOPER
  • 데이터베이스 수정(open, mount, backup 등)
  • 데이터베이스 인스턴스 시작/종료
  • 파라미터 파일 생성
  • 테이블스페이스 및 컨트롤파일 백업
  • 데이터베이스 아카이브/노아카이브 로그 모드 전환
  • 데이터베이스 완전 복구
  • 접속세션 제한
※ 다른 유저의 데이터를 조회할 수는 없음
 SYSDBA
  • SYSOPER 권한
  • 데이터베이스 생성 및 삭제 
  • 데이터베이스 불완전 복구


SQL PLUS를 이용해서 SYSDBA로 접속하기

=> sqlplus / as sysdba

=> show user 명령어 결과는 SYS로 나옵니다.

위의 SYS계정에서 설명했듯이 SYSDBA 접속 기본 스키마이기 때문입니다.



1.2 시스템 권한 부여 및 회수 문법


1.2.1 권한 부여


GRANT [권한/ROLE] TO [USER/ROLE/PUBLIC]

[WITH ADMIN OPTION]


- PUBLIC : 시스템 권한 및 ROLE을 모든 사용자에게 부여

- WITH ADMIN OPTION : 부여받은 권한을 다른 사용자 및 ROLE에게 부여

※ WITH ADMIN OPTION을 사용하여 부여한 권한은 연쇄적으로 회수되지 않음 


EX) GRANT CREATE TABLE, CREATE USER TO SCOTT WITH ADMIN OPTION;



1.2.2. 권한 회수


REVOKE [권한/ROLE] FROM [USER/ROLE/PUBLIC]

 

EX) REVOKE CREATE TABLE, CREATE USER FROM SCOTT;



2. 오브젝트 권한


오브젝트 소유자는 해당 오브젝트의 모은 권한을 가지며, 오브젝트에 대한 특정 권한을 다른 사용자에게 부여할 수 있습니다.


권한

TABLE

 VIEW

 PROCEDURE

 SEQUENCE

 SELECT

O

O 

 

O 

 INSERT

O

 

 

 

 DELETE

O

O 

 

 

 ALTER

O

 

 

O 

 INDEX

O

 

 

 

 EXECUTE


 

 O

 


2.1 오브젝트 권한 부여 및 회수 문법


2.1.1 권한 부여


GRANT 권한[COLUMN] ON OBJECT TO [USER/ROLE/PUBLIC]

[WITH GRANT OPTION]


- WITH GRANT OPTION : 부여받은 권한을 다른 사용자 및 ROLE에게 부여

※ WITH GRANT OPTION을 사용하여 부여한 권한은 연쇄적으로 회수됨 


EX) GRANT INSERT ON TB_TEST TO SCOTT WITH ADMIN OPTION;



2.1.2 권한 회수


REVOKE [권한/ALL] ON OBJECT FROM [USER/ROLE/PUBLIC]

[CASCADE CONSTRAINTS]


- 객체권한의 회수는 부여한 사용자만이 할 수 있음

- CASCADE CONSTRAINTS : 참조 객체에 사용된 참조 무결성 제약을 함께 삭제할 수 있음

 

EX) REVOKE INSERT ON TB_TEST FROM SCOTT;


'데이터베이스' 카테고리의 다른 글

오라클 Hidden Column - SYS_로 시작하는 컬럼  (1) 2017.06.30
오라클 권한 조회  (0) 2017.06.28
오라클 TABLESPACE와 DATAFILE  (0) 2017.06.16
TIMESTAMP 연산  (0) 2017.05.11
오라클 - PIVOT과 UNPIVOT  (0) 2017.02.02
posted by 생각퍼즐
:
데이터베이스 2017. 2. 2. 00:43

오라클 - PIVOT과 UNPIVOT

PIVOT과 UNPIVOT


출력 데이터를 열 --> 행으로 또는 행 --> 열로 변환하기 위해 오라클 11g 이전 버전에서는 decode(CASE구분)을 사용해 해결하였으나, SQL의 가독성 및 성능에 문제점이 있었습니다. 그래서 오라클 11g 에서는 PIVOT/UNPIVOT이라는 기능을 지원하여 보다 쉽게 데이터를 가공할 수 있게 되었습니다. 


사용 사례를 보면서 PIVOT과 UNPIVOT의 기능을 알아보겠습니다.


1. PIVOT(열 --> 행)


오라클에 기본적으로 설치된 SCOTT계정의 "SALGRADE" 라는 테이블을 사용해 설명하겠습니다.

PIVOT 기능을 적용할 데이터는 아래 그림과 같습니다.


   

[PIVOT 적용전 데이터]


PIVOT 기능을 적용하여 열로(LOSAL 값) 된 데이터를 행으로 변환

SYNTAX

PIVOT (집계함수([행의로 변환할 칼럼]) FOR [칼럼명으로 사용될 칼럼] IN ( 칼럼명으로 사용될 칼럼의 값, ...))
1
2
3
4
5
WITH PIVOT_TEST AS (SELECT LOSAL, GRADE
                      FROM SALGRADE)
SELECT *
  FROM PIVOT_TEST
  PIVOT (SUM(LOSAL) FOR GRADE IN (1 AS G1,2 AS G2,3 AS G3,4 AS G4,5 AS G5))
cs

 


2. UNPIVOT(행 --> 열)

위에서 PIVOT 기능을 이용해 열 --> 행으로 변환된 데이터를 UNPIVOT 기능을 사용해 원래되로 다시 변환해 보겠습니다.

1
2
3
4
5
6
7
8
9
10
11
WITH PIVOT_TEST AS (
SELECT LOSAL
     , GRADE
  FROM SALGRADE )
, UNPIVOT_TEST AS ( 
SELECT *
  FROM PIVOT_TEST
  PIVOT ( SUM(LOSAL) FOR GRADE IN (1 AS G1,2 AS G2,3 AS G3,4 AS G4,5 AS G5)))
SELECT *
  FROM UNPIVOT_TEST
UNPIVOT ( LOSAL FOR GRADE IN ( G1 AS 1, G2 AS 2, G3 AS 3, G4 AS 4, G5 AS 5 ))
cs


이론적으로 좀 더 깊게 알고 싶으시면 여기를 참조하세요!

'데이터베이스' 카테고리의 다른 글

오라클 권한 조회  (0) 2017.06.28
오라클 계정과 권한  (0) 2017.06.23
오라클 TABLESPACE와 DATAFILE  (0) 2017.06.16
TIMESTAMP 연산  (0) 2017.05.11
오라클의 동적 PL SQL 작성 - EXECUTE IMMEDIATE  (0) 2017.01.24
posted by 생각퍼즐
:
데이터베이스 2017. 1. 24. 02:11

오라클의 동적 PL SQL 작성 - EXECUTE IMMEDIATE


PL SQL 작성 시 동적으로 DDL구문  또는 SELECT/INSERT/UPDATE/DELETE 구문을 수행해야 할 때가 있다.

이때 유용하게 사용하는 것이 EXECUTE IMMEDIATE 문법이다.


SCOTT 계정의 테이블들의 건수를 TBL_CNT_LIST라는 테이블에 INSERT하는 예시로 EXECUTE IMMEDIATE 구문을 이해해보자!


1. SCOTT 계정으로 생성된 테이블들의 레코드 건수 정보를 INSERT할 TBL_CNT_LIST 테이블 생성


1
2
3
4
CREATE TABLE TBL_CNT_LIST(
TBL_NM      VARCHAR2(30),
CNT         NUMBER(10)
);
cs

2. SCOTT계정의 테이블 목록으로 동적쿼리를 생성
   - 예시가 심플하여 PL SQL에 짧지만 DDL 및 DML 구문을 활용하여 복잡한 업무를 처리할 수 있다. 
   - 필요하다면 위의 TBL_CNT_LIST 테이블도 PL SQL 구문안에서 처리할 수 도 있습니다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DECLARE
BEGIN
    -- 테이블 건수 정보 테이블의 데이터 삭제
    DELETE FROM TBL_CNT_LIST;
    FOR X IN ( -- SCOTT 계정의 테이블 목록을 활용하여 INSERT 구문을 동적으로 생성한다.
                SELECT 'INSERT INTO TBL_CNT_LIST SELECT ' || CHR(39) || T.TABLE_NAME || CHR(39) || ' AS TBL_NM, COUNT(*) AS CNT FROM ' || T.TABLE_NAME AS INS_SQL
                  FROM USER_TABLES T
                 WHERE 1=1
    )
    LOOP
        -- EXECUTE IMMEDIATE 구문을 사용하여 INSERT 구문 수행
        EXECUTE IMMEDIATE X.INS_SQL;
    END LOOP;
    COMMIT;
END;
/
cs

3. 결과 보기

1
2
3
SELECT *
  FROM TBL_CNT_LIST T
 ORDER BY T.TBL_NM
cs




'데이터베이스' 카테고리의 다른 글

오라클 권한 조회  (0) 2017.06.28
오라클 계정과 권한  (0) 2017.06.23
오라클 TABLESPACE와 DATAFILE  (0) 2017.06.16
TIMESTAMP 연산  (0) 2017.05.11
오라클 - PIVOT과 UNPIVOT  (0) 2017.02.02
posted by 생각퍼즐
:

티스토리툴바