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 |
'데이터베이스' 카테고리의 다른 글
| 오라클 권한 조회 (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 |
사용자 및 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 조회
※ 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 |
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 |
※ 다른 유저의 데이터를 조회할 수는 없음 |
| SYSDBA |
|
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 |