오라클 권한관련 분석 쿼리, 오브젝트 (테이블, 시퀀스, 스케쥴러, 프로시저 등)확인 쿼리
모든 user 확인 : all_users
실행예) SELECT * FROM all_users;
USERNAME | USER_ID | CREATED | COMMON | ORACLE_MAINTAINED | INHERITED | DEFAULT_COLLATION | IMPLICIT | ALL_SHARD |
---|---|---|---|---|---|---|---|---|
SYS | 0 | 18/01/26 | YES | Y | YES | USING_NLS_COMP | NO | NO |
계정에 적용중인 ROLE 확인 : DBA_ROLE_PRIVS
실행예) SELECT * FROM DBA_ROLE_PRIVS;
GRANTEE | GRANTED_ROLE | ADMIN_OPTION | DELEGATE_OPTION | DEFAULT_ROLE | COMMON | INHERITED |
---|---|---|---|---|---|---|
DBA | XDBADMIN | NO | NO | YES | NO | NO |
ROLE에 부여된 권한 확인 : ROLE_TAB_PRIVS
실행예) SELECT * FROM ROLE_TAB_PRIVS;
ROLE | OWNER | TABLE_NAME | COLUMN_NAME | PRIVILEGE | GRANTABLE | COMMON | INHERITED |
---|---|---|---|---|---|---|---|
SODA_APP | XDB | DBMS_SODA_ADMIN | EXECUTE | NO | NO | NO |
ROLE에 부여된 SYSTEM 권한 확인 : DBA_ROLE_PRIVS
실행예) SELECT * FROM ROLE_SYS_PRIVS;
ROLE | PRIVILEGE | ADMIN_OPTION | COMMON | INHERITED |
---|---|---|---|---|
CONNECT | CREATE | SESSION | NO | NO |
모든 오브젝트에 대한 권한 확인 : DBA_TAB_PRIVS
실행예) SELECT * FROM DBA_TAB_PRIVS;
GRANTEE | OWNER | TABLE_NAME | GRANTOR | PRIVILEGE | GRANTABLE | HIERARCHY | COMMON | TYPE | INHERITED |
---|---|---|---|---|---|---|---|---|---|
DBA | SYS | MAP_OBJECT | SYS | DELETE | NO | NO | NO | TABLE | NO |
DBA | SYS | MAP_OBJECT | SYS | DELETE | NO | NO | NO | PACKAGE | NO |
DBA | SYS | MAP_OBJECT | SYS | DELETE | NO | NO | NO | SEQUENCE | NO |
인덱스(INDEX) 정보 확인 : ALL_IND_COLUMNS
실행 예 ) SELECT * FROM ALL_IND_COLUMNS;
INDEX_OWNER | INDEX_NAME | TABLE_OWNER | TABLE_NAME | COLUMN_NAME | COLUMN_POSITION | COLUMN_LENGTH | CHAR_LENGTH | DESCEND | COLLATED_COLUMN_ID |
---|---|---|---|---|---|---|---|---|---|
SYS | HS_PARALLEL_METADATA_PK | SYS | HS$_PARALLEL_METADATA | DBLINK | 1 | 128 | 128 | ASC |
procedure 내용 확인 : ALL_SOURCE
실행예) SELECT * FROM ALL_SOURCE;
OWNER | NAME | TYPE | LINE | TEXT | ORIGIN_CON_ID |
---|---|---|---|---|---|
SYS | STANDARD | PACKAGE | 1 | “package STANDARD AUTHID CURRENT_USER is – careful on this line; SED edit occurs!” |
스케줄러 확인 : DBA_SCHEDULER_JOBS
실행예) SELECT * FROM DBA_SCHEDULER_JOBS;
(내용이 많아서 열<->행 변환하여 표시)
컬럼명 | 예시값 |
---|---|
OWNER | SYS |
JOB_NAME | PURGE_LOG |
JOB_SUBNAME | |
JOB_STYLE REGULAR | |
JOB_CREATOR | SYS |
CLIENT_ID | |
GLOBAL_UID | |
PROGRAM_OWNER | SYS |
PROGRAM_NAME | PURGE_LOG_PROG |
JOB_TYPE | |
JOB_ACTION | |
NUMBER_OF_ARGUMENTS | |
SCHEDULE_OWNER | SYS |
SCHEDULE_NAME | DAILY_PURGE_SCHEDULE |
SCHEDULE_TYPE | NAMED |
START_DATE | 18/01/27 03:00:00.530288000 ASIA/SEOUL |
REPEAT_INTERVAL | |
EVENT_QUEUE_OWNER | |
EVENT_QUEUE_NAME | |
EVENT_QUEUE_AGENT | |
EVENT_CONDITION | |
EVENT_RULE | |
FILE_WATCHER_OWNER | |
FILE_WATCHER_NAME | |
END_DATE | |
JOB_CLASS | DEFAULT_JOB_CLASS |
ENABLED | TRUE |
AUTO_DROP | FALSE |
RESTART_ON_RECOVERY | FALSE |
RESTART_ON_FAILURE | FALSE |
STATE | SCHEDULED |
JOB_PRIORITY | 3 |
RUN_COUNT | 2607 |
UPTIME_RUN_COUNT | |
MAX_RUNS | |
FAILURE_COUNT | 0 |
UPTIME_FAILURE_COUNT | |
MAX_FAILURES | |
RETRY_COUNT | 0 |
LAST_START_DATE | 23/03/17 03:00:01.185402000 ASIA/SEOUL |
LAST_RUN_DURATION | +00 00:00:00.646684 |
NEXT_RUN_DATE | 23/03/18 03:00:00.189294000 ASIA/SEOUL |
SCHEDULE_LIMIT | |
MAX_RUN_DURATION | |
LOGGING_LEVEL | OFF |
STORE_OUTPUT | TRUE |
STOP_ON_WINDOW_CLOSE | FALSE |
INSTANCE_STICKINESS | TRUE |
RAISE_EVENTS | |
SYSTEM | TRUE |
JOB_WEIGHT | 1 |
NLS_ENV | … |
SOURCE | |
NUMBER_OF_DESTINATIONS | 1 |
DESTINATION_OWNER | |
DESTINATION | |
CREDENTIAL_OWNER | |
CREDENTIAL_NAME | |
INSTANCE_ID | |
DEFERRED_DROP | FALSE |
ALLOW_RUNS_IN_RESTRICTED_MODE | FALSE |
COMMENTS | purge log job |
FLAGS | 9.0072E+15 |
RESTARTABLE | FALSE |
HAS_CONSTRAINTS | FALSE |
CONNECT_CREDENTIAL_OWNER | |
CONNECT_CREDENTIAL_NAME | |
FAIL_ON_SCRIPT_ERROR | FALSE |