Oracle

게시글 보기
작성자 유건데이타 등록일 2019-08-13
제목 role 권한
select grantee||','||privilege||','||owner||','||table_name
from dba_tab_privs
where grantee not in
(select role from dba_roles)
and grantee not in ('ORDSYS', 'OTULN', 'PUBLIC', 'SYS', 'SYSTEM',
'CYXSYS', 'MDSYS', 'ODM', 'OLAPSYS', 'WKSYS', 'WMSYS', 'XDB')
order by 1 asc;
Comment
2019-08-13 유건데이타 SQL> select username, lock_date from dba_users; USERNAME LOCK_DATE ------------------------------ ------------ UPIS NAVI_USER SYS SYSTEM LDSS TEST GSS OUTLN 28-AUG-13 DBSNMP 28-AUG-13 WMSYS 28-AUG-13 EXFSYS 28-AUG-13 USERNAME LOCK_DATE ------------------------------ ------------ TSMSYS 28-AUG-13 DIP 28-AUG-13 ORACLE_OCM 28-AUG-13 DBSNMP CREATE PROCEDURE DBSNMP CREATE TABLE DBSNMP SELECT ANY DICTIONARY DBSNMP UNLIMITED TABLESPACE EXFSYS ADMINISTER DATABASE TRIGGER EXFSYS CREATE ANY TRIGGER EXFSYS CREATE INDEXTYPE EXFSYS CREATE LIBRARY EXFSYS CREATE OPERATOR EXFSYS UNLIMITED TABLESPACE EXP_FULL_DATABASE ADMINISTER RESOURCE MANAGER EXP_FULL_DATABASE BACKUP ANY TABLE EXP_FULL_DATABASE EXECUTE ANY PROCEDURE EXP_FULL_DATABASE EXECUTE ANY TYPE EXP_FULL_DATABASE READ ANY FILE GROUP EXP_FULL_DATABASE RESUMABLE EXP_FULL_DATABASE SELECT ANY SEQUENCE EXP_FULL_DATABASE SELECT ANY TABLE GSS ALTER ANY TABLE GSS CREATE ANY SEQUENCE GSS CREATE ANY TRIGGER GSS CREATE ANY VIEW GSS CREATE INDEXTYPE GSS CREATE LIBRARY GSS CREATE OPERATOR GSS CREATE PROCEDURE GSS CREATE PUBLIC SYNONYM GSS CREATE TABLE GSS CREATE TYPE GSS DROP PUBLIC SYNONYM GSS SELECT ANY SEQUENCE GSS SELECT ANY TABLE GSS UNLIMITED TABLESPACE IMP_FULL_DATABASE ADMINISTER DATABASE TRIGGER IMP_FULL_DATABASE ADMINISTER RESOURCE MANAGER IMP_FULL_DATABASE ALTER ANY PROCEDURE IMP_FULL_DATABASE ALTER ANY TABLE IMP_FULL_DATABASE ALTER ANY TRIGGER IMP_FULL_DATABASE ALTER ANY TYPE IMP_FULL_DATABASE ANALYZE ANY IMP_FULL_DATABASE AUDIT ANY IMP_FULL_DATABASE BECOME USER IMP_FULL_DATABASE COMMENT ANY TABLE IMP_FULL_DATABASE CREATE ANY CLUSTER IMP_FULL_DATABASE CREATE ANY CONTEXT IMP_FULL_DATABASE CREATE ANY DIMENSION IMP_FULL_DATABASE CREATE ANY DIRECTORY IMP_FULL_DATABASE CREATE ANY INDEX IMP_FULL_DATABASE CREATE ANY INDEXTYPE IMP_FULL_DATABASE CREATE ANY LIBRARY IMP_FULL_DATABASE CREATE ANY MATERIALIZED VIEW IMP_FULL_DATABASE CREATE ANY OPERATOR IMP_FULL_DATABASE CREATE ANY PROCEDURE IMP_FULL_DATABASE CREATE ANY SEQUENCE IMP_FULL_DATABASE CREATE ANY SQL PROFILE IMP_FULL_DATABASE CREATE ANY SYNONYM IMP_FULL_DATABASE CREATE ANY TABLE IMP_FULL_DATABASE CREATE ANY TRIGGER IMP_FULL_DATABASE CREATE ANY TYPE IMP_FULL_DATABASE CREATE ANY VIEW IMP_FULL_DATABASE CREATE DATABASE LINK IMP_FULL_DATABASE CREATE PROFILE IMP_FULL_DATABASE CREATE PUBLIC DATABASE LINK IMP_FULL_DATABASE CREATE PUBLIC SYNONYM IMP_FULL_DATABASE CREATE ROLE IMP_FULL_DATABASE CREATE ROLLBACK SEGMENT IMP_FULL_DATABASE CREATE TABLESPACE IMP_FULL_DATABASE CREATE USER IMP_FULL_DATABASE DROP ANY CLUSTER IMP_FULL_DATABASE DROP ANY CONTEXT IMP_FULL_DATABASE DROP ANY DIMENSION IMP_FULL_DATABASE DROP ANY DIRECTORY IMP_FULL_DATABASE DROP ANY INDEX IMP_FULL_DATABASE DROP ANY INDEXTYPE IMP_FULL_DATABASE DROP ANY LIBRARY IMP_FULL_DATABASE DROP ANY MATERIALIZED VIEW IMP_FULL_DATABASE DROP ANY OPERATOR IMP_FULL_DATABASE DROP ANY OUTLINE IMP_FULL_DATABASE DROP ANY PROCEDURE IMP_FULL_DATABASE DROP ANY ROLE IMP_FULL_DATABASE DROP ANY SEQUENCE IMP_FULL_DATABASE DROP ANY SQL PROFILE IMP_FULL_DATABASE DROP ANY SYNONYM IMP_FULL_DATABASE DROP ANY TABLE IMP_FULL_DATABASE DROP ANY TRIGGER IMP_FULL_DATABASE DROP ANY TYPE IMP_FULL_DATABASE DROP ANY VIEW IMP_FULL_DATABASE DROP PROFILE IMP_FULL_DATABASE DROP PUBLIC DATABASE LINK IMP_FULL_DATABASE DROP PUBLIC SYNONYM IMP_FULL_DATABASE DROP ROLLBACK SEGMENT IMP_FULL_DATABASE DROP TABLESPACE IMP_FULL_DATABASE DROP USER IMP_FULL_DATABASE EXECUTE ANY PROCEDURE IMP_FULL_DATABASE EXECUTE ANY TYPE IMP_FULL_DATABASE GLOBAL QUERY REWRITE IMP_FULL_DATABASE INSERT ANY TABLE IMP_FULL_DATABASE MANAGE ANY QUEUE IMP_FULL_DATABASE RESUMABLE IMP_FULL_DATABASE SELECT ANY TABLE IMP_FULL_DATABASE UPDATE ANY TABLE JAVADEBUGPRIV DEBUG ANY PROCEDURE JAVADEBUGPRIV DEBUG CONNECT SESSION NAVI_USER CREATE ANY SYNONYM NAVI_USER CREATE ANY VIEW NAVI_USER CREATE SYNONYM NAVI_USER CREATE VIEW NAVI_USER DROP ANY VIEW NAVI_USER UNDER ANY VIEW OEM_ADVISOR ADMINISTER SQL TUNING SET OEM_ADVISOR ADVISOR OEM_ADVISOR CREATE JOB ORACLE_OCM SELECT ANY DICTIONARY OUTLN EXECUTE ANY PROCEDURE OUTLN UNLIMITED TABLESPACE SCHEDULER_ADMIN CREATE ANY JOB SCHEDULER_ADMIN CREATE EXTERNAL JOB SCHEDULER_ADMIN CREATE JOB SCHEDULER_ADMIN EXECUTE ANY CLASS SCHEDULER_ADMIN EXECUTE ANY PROGRAM SCHEDULER_ADMIN MANAGE SCHEDULER TEST UNLIMITED TABLESPACE TSMSYS UNLIMITED TABLESPACE UPIS CREATE TABLE UPIS UNLIMITED TABLESPACE 시스템 테이블 접근 권한 AQ_USER_ROLE EXECUTE DBMS_AQJMS_INTERNAL AQ_USER_ROLE EXECUTE DBMS_AQIN AQ_USER_ROLE EXECUTE DBMS_AQ AQ_USER_ROLE EXECUTE DBMS_TRANSFORM DBSNMP EXECUTE DBMS_SERVER_ALERT EXFSYS EXECUTE DBMS_SYS_ERROR EXFSYS EXECUTE DBMS_JOB EXFSYS EXECUTE DBMS_REGISTRY EXFSYS EXECUTE DBMS_SCHEDULER EXFSYS EXECUTE EXF$DBMS_EXPFIL_SYSPACK EXP_FULL_DATABASE DELETE INCEXP EXP_FULL_DATABASE INSERT INCEXP EXP_FULL_DATABASE UPDATE INCEXP EXP_FULL_DATABASE DELETE INCVID EXP_FULL_DATABASE INSERT INCVID EXP_FULL_DATABASE UPDATE INCVID EXP_FULL_DATABASE DELETE INCFIL EXP_FULL_DATABASE INSERT INCFIL EXP_FULL_DATABASE UPDATE INCFIL EXP_FULL_DATABASE SELECT EXU10LNK EXP_FULL_DATABASE SELECT EXU9LNK EXP_FULL_DATABASE SELECT EXU8LNK EXP_FULL_DATABASE SELECT EXU8PHS EXP_FULL_DATABASE EXECUTE DBMSZEXP_SYSPKGGRNT EXP_FULL_DATABASE EXECUTE DBMS_RULE_EXIMP EXP_FULL_DATABASE EXECUTE DBMS_AQ_IMPORT_INTERNAL EXP_FULL_DATABASE SELECT _DBA_CAPTURE EXP_FULL_DATABASE SELECT _DBA_APPLY EXP_FULL_DATABASE SELECT KU$_PSW_HIST_LIST_VIEW EXP_FULL_DATABASE SELECT KU$_PSW_HIST_VIEW EXP_FULL_DATABASE READ DATA_PUMP_DIR EXP_FULL_DATABASE WRITE DATA_PUMP_DIR EXP_FULL_DATABASE EXECUTE DBMS_TRANSFORM_EXIMP EXP_FULL_DATABASE EXECUTE DBMS_PSWMG_IMPORT EXP_FULL_DATABASE EXECUTE DBMS_IAS_INST_UTL_EXP EXP_FULL_DATABASE EXECUTE DBMS_CDC_IMPDPV EXP_FULL_DATABASE EXECUTE DBMS_IREFRESH EXP_FULL_DATABASE SELECT _DBA_APPLY_SOURCE_SCHEMA EXP_FULL_DATABASE SELECT _DBA_APPLY_SOURCE_OBJ EXP_FULL_DATABASE SELECT _DBA_APPLY_ERROR_HANDLER EXP_FULL_DATABASE SELECT _DBA_APPLY_CONF_HDLR_COLUMNS EXP_FULL_DATABASE SELECT _DBA_APPLY_MILESTONE EXP_FULL_DATABASE SELECT _DBA_APPLY_PROGRESS EXP_FULL_DATABASE SELECT _DBA_APPLY_ERROR EXP_FULL_DATABASE SELECT _DBA_APPLY_ERROR_TXN EXP_FULL_DATABASE SELECT _DBA_APPLY_SPILL_TXN EXP_FULL_DATABASE SELECT _DBA_STREAMS_MESSAGE_CONSUMERS EXP_FULL_DATABASE SELECT _DBA_STREAMS_MSG_NOTIFICATIONS EXP_FULL_DATABASE SELECT _DBA_STREAMS_RULES EXP_FULL_DATABASE SELECT _DBA_STREAMS_MESSAGE_RULES EXP_FULL_DATABASE SELECT _DBA_STREAMS_PRIVILEGED_USER EXP_FULL_DATABASE SELECT _DBA_STREAMS_TRANSFORMATIONS EXP_FULL_DATABASE EXECUTE DBMS_JVM_EXP_PERMS IMP_FULL_DATABASE UPDATE EXPIMP_TTS_CT$ IMP_FULL_DATABASE SELECT EXPIMP_TTS_CT$ IMP_FULL_DATABASE EXECUTE DBMS_CDC_IMPDPV IMP_FULL_DATABASE EXECUTE DBMS_REPCAT_MIG IMP_FULL_DATABASE DELETE EXPIMP_TTS_CT$ IMP_FULL_DATABASE INSERT EXPIMP_TTS_CT$ IMP_FULL_DATABASE EXECUTE DBMS_IJOB IMP_FULL_DATABASE EXECUTE DBMS_RULE_EXIMP IMP_FULL_DATABASE EXECUTE DBMS_AQADM IMP_FULL_DATABASE EXECUTE DBMS_AQ_IMPORT_INTERNAL IMP_FULL_DATABASE EXECUTE DBMS_JVM_EXP_PERMS IMP_FULL_DATABASE READ DATA_PUMP_DIR IMP_FULL_DATABASE WRITE DATA_PUMP_DIR IMP_FULL_DATABASE EXECUTE DBMS_TRANSFORM_EXIMP IMP_FULL_DATABASE EXECUTE DBMS_PSWMG_IMPORT IMP_FULL_DATABASE EXECUTE DBMS_IREFRESH ORACLE_OCM READ ORACLE_OCM_CONFIG_DIR ORACLE_OCM EXECUTE DBMS_SCHEDULER ORACLE_OCM EXECUTE UTL_INADDR ORACLE_OCM EXECUTE UTL_FILE ORACLE_OCM WRITE ORACLE_OCM_CONFIG_DIR UPIS EXECUTE DBMS_CRYPTO role 권한(d-15) SQL> / DBSNMP,EXECUTE,SYS,DBMS_SERVER_ALERT EXFSYS,EXECUTE,SYS,DBMS_JOB EXFSYS,EXECUTE,SYS,DBMS_REGISTRY EXFSYS,EXECUTE,SYS,DBMS_SCHEDULER EXFSYS,EXECUTE,SYS,DBMS_SYS_ERROR EXFSYS,EXECUTE,SYS,EXF$DBMS_EXPFIL_SYSPACK LDSS,SELECT,UPIS,V_TN_PRMISN_LC LDSS,SELECT,UPIS,V_TN_PRMISN_PRJCT NAVI_USER,SELECT,UPIS,NAVI_PRMISN_LOG_V ORACLE_OCM,EXECUTE,SYS,DBMS_SCHEDULER ORACLE_OCM,EXECUTE,SYS,UTL_FILE ORACLE_OCM,EXECUTE,SYS,UTL_INADDR ORACLE_OCM,READ,SYS,ORACLE_OCM_CONFIG_DIR ORACLE_OCM,WRITE,SYS,ORACLE_OCM_CONFIG_DIR OUTLN,EXECUTE,SYS,OUTLN_PKG SELECT profile, limit FROM dba_profiles WHERE resource_name='PASSWORD_VERIFY_FUNCTION' and limit in ('UNLIMITED', 'NULL'); D-19 DBSNMP,EXECUTE,SYS,DBMS_SERVER_ALERT EXFSYS,EXECUTE,SYS,DBMS_JOB EXFSYS,EXECUTE,SYS,DBMS_REGISTRY EXFSYS,EXECUTE,SYS,DBMS_SCHEDULER EXFSYS,EXECUTE,SYS,DBMS_SYS_ERROR EXFSYS,EXECUTE,SYS,EXF$DBMS_EXPFIL_SYSPACK LDSS,SELECT,UPIS,V_TN_PRMISN_LC LDSS,SELECT,UPIS,V_TN_PRMISN_PRJCT NAVI_USER,SELECT,UPIS,NAVI_PRMISN_LOG_V ORACLE_OCM,EXECUTE,SYS,DBMS_SCHEDULER ORACLE_OCM,EXECUTE,SYS,UTL_FILE ORACLE_OCM,EXECUTE,SYS,UTL_INADDR ORACLE_OCM,READ,SYS,ORACLE_OCM_CONFIG_DIR ORACLE_OCM,WRITE,SYS,ORACLE_OCM_CONFIG_DIR OUTLN,EXECUTE,SYS,OUTLN_PKG UPIS,EXECUTE,SYS,DBMS_CRYPTO