오라클 권한관련 분석 쿼리, 오브젝트 (테이블, 시퀀스, 스케쥴러, 프로시저 등)확인 쿼리

오라클 권한관련 분석 쿼리, 오브젝트 (테이블, 시퀀스, 스케쥴러, 프로시저 등)확인 쿼리

모든 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

인덱스 정보 확인 : 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	1	1	ASC

 

스케줄러 확인 : 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