0%

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

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

모든 user 확인 : all_users

실행예) SELECT * FROM all_users;

USERNAMEUSER_IDCREATEDCOMMONORACLE_MAINTAINEDINHERITEDDEFAULT_COLLATIONIMPLICITALL_SHARD
SYS018/01/26YESYYESUSING_NLS_COMPNONO

계정에 적용중인 ROLE 확인 : DBA_ROLE_PRIVS

실행예) SELECT * FROM DBA_ROLE_PRIVS;

GRANTEEGRANTED_ROLEADMIN_OPTIONDELEGATE_OPTIONDEFAULT_ROLECOMMONINHERITED
DBAXDBADMINNONOYESNONO

ROLE에 부여된 권한 확인 : ROLE_TAB_PRIVS

실행예) SELECT * FROM ROLE_TAB_PRIVS;

ROLEOWNERTABLE_NAMECOLUMN_NAMEPRIVILEGEGRANTABLECOMMONINHERITED
SODA_APPXDBDBMS_SODA_ADMINEXECUTENONONO

ROLE에 부여된 SYSTEM 권한 확인 : DBA_ROLE_PRIVS

실행예) SELECT * FROM ROLE_SYS_PRIVS;

ROLEPRIVILEGEADMIN_OPTIONCOMMONINHERITED
CONNECTCREATESESSIONNONO

모든 오브젝트에 대한 권한 확인 : DBA_TAB_PRIVS

실행예) SELECT * FROM DBA_TAB_PRIVS;

GRANTEEOWNERTABLE_NAMEGRANTORPRIVILEGEGRANTABLEHIERARCHYCOMMONTYPEINHERITED
DBASYSMAP_OBJECTSYSDELETENONONOTABLENO
DBASYSMAP_OBJECTSYSDELETENONONOPACKAGENO
DBASYSMAP_OBJECTSYSDELETENONONOSEQUENCENO

인덱스(INDEX) 정보 확인 : ALL_IND_COLUMNS

실행 예 ) SELECT * FROM ALL_IND_COLUMNS;

INDEX_OWNERINDEX_NAMETABLE_OWNERTABLE_NAMECOLUMN_NAMECOLUMN_POSITIONCOLUMN_LENGTHCHAR_LENGTHDESCENDCOLLATED_COLUMN_ID
SYSHS_PARALLEL_METADATA_PKSYSHS$_PARALLEL_METADATADBLINK1128128ASC

procedure 내용 확인 : ALL_SOURCE

실행예) SELECT * FROM ALL_SOURCE;

OWNERNAMETYPELINETEXTORIGIN_CON_ID
SYSSTANDARDPACKAGE1“package STANDARD AUTHID CURRENT_USER is – careful on this line; SED edit occurs!”

스케줄러 확인 : DBA_SCHEDULER_JOBS

실행예) SELECT * FROM DBA_SCHEDULER_JOBS;
(내용이 많아서 열<->행 변환하여 표시)

컬럼명예시값
OWNERSYS
JOB_NAMEPURGE_LOG
JOB_SUBNAME
JOB_STYLE REGULAR
JOB_CREATORSYS
CLIENT_ID
GLOBAL_UID
PROGRAM_OWNERSYS
PROGRAM_NAMEPURGE_LOG_PROG
JOB_TYPE
JOB_ACTION
NUMBER_OF_ARGUMENTS
SCHEDULE_OWNERSYS
SCHEDULE_NAMEDAILY_PURGE_SCHEDULE
SCHEDULE_TYPENAMED
START_DATE18/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_CLASSDEFAULT_JOB_CLASS
ENABLEDTRUE
AUTO_DROPFALSE
RESTART_ON_RECOVERYFALSE
RESTART_ON_FAILUREFALSE
STATESCHEDULED
JOB_PRIORITY3
RUN_COUNT2607
UPTIME_RUN_COUNT
MAX_RUNS
FAILURE_COUNT0
UPTIME_FAILURE_COUNT
MAX_FAILURES
RETRY_COUNT0
LAST_START_DATE23/03/17 03:00:01.185402000 ASIA/SEOUL
LAST_RUN_DURATION+00 00:00:00.646684
NEXT_RUN_DATE23/03/18 03:00:00.189294000 ASIA/SEOUL
SCHEDULE_LIMIT
MAX_RUN_DURATION
LOGGING_LEVELOFF
STORE_OUTPUTTRUE
STOP_ON_WINDOW_CLOSEFALSE
INSTANCE_STICKINESSTRUE
RAISE_EVENTS
SYSTEMTRUE
JOB_WEIGHT1
NLS_ENV
SOURCE
NUMBER_OF_DESTINATIONS1
DESTINATION_OWNER
DESTINATION
CREDENTIAL_OWNER
CREDENTIAL_NAME
INSTANCE_ID
DEFERRED_DROPFALSE
ALLOW_RUNS_IN_RESTRICTED_MODEFALSE
COMMENTSpurge log job
FLAGS9.0072E+15
RESTARTABLEFALSE
HAS_CONSTRAINTSFALSE
CONNECT_CREDENTIAL_OWNER
CONNECT_CREDENTIAL_NAME
FAIL_ON_SCRIPT_ERRORFALSE