参考写queue赋权限

引用

DBMS_AQADM.CREATE_QUEUE_TABLE
(
QUEUE_TABLE => 'GCE_META.FPML_NOTIFY_QT_OGG'
,QUEUE_PAYLOAD_TYPE => 'SYS.AQ$_JMS_TEXT_MESSAGE'
,COMPATIBLE => '8.1.3'
,SORT_LIST => 'ENQ_TIME'
,MULTIPLE_CONSUMERS => false
,MESSAGE_GROUPING => 0
,COMMENT => 'fpml transform notify queue for OGG'
,SECURE => false
);


DBMS_AQADM.CREATE_QUEUE
(
QUEUE_NAME => 'GCE_META.FPML_NOTIFY_Q_OGG'
,QUEUE_TABLE => 'GCE_META.FPML_NOTIFY_QT_OGG'
,QUEUE_TYPE => 0
,MAX_RETRIES => 5
,RETRY_DELAY => 0
,RETENTION_TIME => 0
);



DBMS_AQADM.START_QUEUE
(
QUEUE_NAME => 'GCE_META.FPML_NOTIFY_Q_OGG'
,ENQUEUE => true
,DEQUEUE => true
);

DBMS_AQADM.GRANT_QUEUE_PRIVILEGE
(
PRIVILEGE => 'ALL',
QUEUE_NAME => 'GCE_META.FPML_NOTIFY_Q_OGG'
,GRANTEE => 'GCE_DMM_UPDATE_ROLE'
);

DBMS_AQADM.START_QUEUE
(
QUEUE_NAME => 'GCE_META.AQ$_FPML_NOTIFY_QT_OGG_E'
,ENQUEUE => false
,DEQUEUE => true
);

DBMS_AQADM.GRANT_QUEUE_PRIVILEGE
(
PRIVILEGE => 'DEQUEUE',
QUEUE_NAME => 'GCE_META.AQ$_FPML_NOTIFY_QT_OGG_E'
,GRANTEE => 'GCE_DMM_UPDATE_ROLE'
);


select * from dba_tab_privs where table_name = 'RELATIONAL_GTM_OTC_Q' AND OWNER = 'BUAT_META';

GCE1_DMM_UPDATE_ROLE BUAT_META RELATIONAL_GTM_OTC_Q BUAT_META ENQUEUE NO NO
GCE1_DMM_UPDATE_ROLE BUAT_META RELATIONAL_GTM_OTC_Q BUAT_META DEQUEUE NO NO


select * from dba_tab_privs where table_name = 'RELATIONAL_GTM_OTC_QT' AND OWNER = 'BUAT_META';

R2_DMM_READONLY_ROLE BUAT_META RELATIONAL_GTM_OTC_QT BUAT_META SELECT NO NO
GCE1_DMM_UPDATE_ROLE BUAT_META RELATIONAL_GTM_OTC_QT BUAT_META SELECT NO NO