Home › Oracle AQ Operations Privileges

Oracle AQ Operations Privileges 

Dr. Xi
Joined:
04/09/2007
Posts:
703

August 15, 2009 17:15:59    Last update: August 15, 2009 17:28:29
OperationsPrivileges Required
CREATE/DROP/MONITOR
own queues		Must have EXECUTE rights on DBMS_AQADM
ENQUEUE/DEQUEUE
to own queues		Must have EXECUTE rights on DBMS_AQ
ENQUEUE/DEQUEUE
to other's queues		Must have EXECUTE rights on DBMS_AQ
Queue owner must grant privileges using DBMS_AQADM.GRANT_QUEUE_PRIVILEGE
CREATE/DROP/MONITOR
any queues		Must have EXECUTE rights on DBMS_AQADM
Must be granted AQ_ADMINISTRATOR_ROLE
ENQUEUE/DEQUEUE
to any queues		Must have EXECUTE rights on DBMS_AQ
Must be granted "ENQUEUE_ANY" or "DEQUEUE_ANY" system privileges using DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE


Examples: 
-- Create an admin
CREATE ROLE MY_AQ_ADMIN_ROLE;
GRANT CONNECT, RESOURCE, AQ_ADMINISTRATOR_ROLE TO MY_AQ_ADMIN_ROLE;
CREATE USER AQADMIN IDENTIFIED BY aqadmin
    DEFAULT TABLESPACE tab
    TEMPORARY TABLESPACE temp;
GRANT MY_AQ_ADMIN_ROLE TO AQADMIN;

-- create a user
CREATE ROLE MY_AQ_USER_ROLE;
GRANT CREATE SESSION, AQ_USER_ROLE TO MY_AQ_USER_ROLE;

EXEC DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
        PRIVILEGE => 'ENQUEUE_ANY',
        GRANTEE => 'MY_AQ_USER_ROLE',
        ADMIN_OPTION => FALSE);

EXEC DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
        PRIVILEGE => 'DEQUEUE_ANY',
        GRANTEE => 'MY_AQ_USER_ROLE',
        ADMIN_OPTION => FALSE);

CREATE USER AQUSER IDENTIFIED BY aquser
    DEFAULT TABLESPACE tab
    TEMPORARY TABLESPACE temp;
GRANT MY_AQ_USER_ROLE TO AQUSER;




Share |
| Comment  | Tags
 
Easy email testing with http://www.ximailstop.com

Related Notes