Administering queue [message #669048] |
Fri, 30 March 2018 06:31 |
arijit2004
Messages: 9 Registered: September 2007
|
Junior Member |
|
|
Can we create public synonym for queue.
A queue has been created in AQ ADMIN schema.How to grant execute privilege to OS USER?
User created in DB is OPS$CDR726.
|
|
|
Re: Administering queue [message #669049 is a reply to message #669048] |
Fri, 30 March 2018 07:49 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
You don't "execute" a queue. You can allow your user to subscribe to the queue with dbms_aqadm.add_subscriber, if that is what yo mean?
|
|
|
|
Re: Administering queue [message #669051 is a reply to message #669050] |
Fri, 30 March 2018 10:46 |
arijit2004
Messages: 9 Registered: September 2007
|
Junior Member |
|
|
Below pl/sql wasinvoked from AQ_ADMIN schema.
begin
dbms_aqadm.grant_queue_privilege (
privilege => 'ALL',
queue_name => 'QUEUE_1',
grantee => 'OPS$CDR726',
grant_option => TRUE );
end;
/
begin
sys.dbms_aqadm.stop_queue(queue_name=>'QUEUE_1', enqueue => false, dequeue => true);
end;
/
when I executed above pl/sql block using OPS$CDR726 I am getting error
OPS$CDR726.QUEUE_1 queue must be declared.
Should I include schema name explicitly with the queue name to execute above (dequeue) pl/sql block successfully? Will there be any other action to take to
run above piece of code successfully with OPS$CDR726?
[Updated on: Fri, 30 March 2018 10:47] Report message to a moderator
|
|
|
|
Re: Administering queue [message #669055 is a reply to message #669054] |
Fri, 30 March 2018 13:32 |
arijit2004
Messages: 9 Registered: September 2007
|
Junior Member |
|
|
Yes ALL means both.
I did not understand when you said "Where did you see it includes STOP?"
Quote:If it is not the owner the queue, yes, but it then needs much more privileges.
OPS$CDR726 is an external user identified externally by Oracle. Hence, it can not be owner.
What more privilege to be given to this user? What are they?
Thanks to you for giving your time
|
|
|
Re: Administering queue [message #669056 is a reply to message #669055] |
Fri, 30 March 2018 14:00 |
|
Michel Cadot
Messages: 68693 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote:I did not understand when you said "Where did you see it includes STOP?"
You said:
Quote:when I executed above pl/sql block using OPS$CDR726 I am getting error
And this block contains: dbms_aqadm.stop_queue
So, even if you gave the correct queue, you couldn't execute it with the privileges you granted.
Quote:OPS$CDR726 is an external user identified externally by Oracle. Hence, it can not be owner.
Why not?
SQL> create user ops$michel identified externally quota unlimited on ts_d01;
User created.
SQL> grant create session to ops$michel
2 ;
Grant succeeded.
SQL> begin
2 DBMS_AQADM.CREATE_QUEUE_TABLE ('OPS$MICHEL.MYQUEUE_TABLE','RAW');
3 DBMS_AQADM.CREATE_QUEUE ('OPS$MICHEL.MYQUEUE','OPS$MICHEL.MYQUEUE_TABLE');
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> connect /
Connected.
SQL> show user
USER is "OPS$MICHEL"
SQL> col object_name format a30
SQL> select object_name, object_type from user_objects order by 1, 2;
OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
AQ$MYQUEUE_TABLE VIEW
AQ$_MYQUEUE_TABLE_E QUEUE
AQ$_MYQUEUE_TABLE_F VIEW
AQ$_MYQUEUE_TABLE_I INDEX
AQ$_MYQUEUE_TABLE_T INDEX
MYQUEUE QUEUE
MYQUEUE_TABLE TABLE
SYS_C0067002 INDEX
SYS_IL0000228622C00028$$ INDEX
SYS_IL0000228622C00029$$ INDEX
SYS_LOB0000228622C00028$$ LOB
SYS_LOB0000228622C00029$$ LOB
|
|
|
Re: Administering queue [message #669057 is a reply to message #669056] |
Fri, 30 March 2018 15:00 |
arijit2004
Messages: 9 Registered: September 2007
|
Junior Member |
|
|
Thanks for the example.
The queue is created in AQ_ADMIN schema.
What privilege would be required so that OPS$CDR726 can disable queue?
What is the steps to be executed to make the pl/sql block run successfully by OPS$CDR726.
begin
sys.dbms_aqadm.stop_queue(queue_name=>'AQ_ADMIN.QUEUE_1', enqueue => false, dequeue => true);
end;
/
Thanks for help
|
|
|
|
Re: Administering queue [message #669064 is a reply to message #669058] |
Sat, 31 March 2018 00:50 |
arijit2004
Messages: 9 Registered: September 2007
|
Junior Member |
|
|
Thanks for giving your time to look into the problem.
AQ_ADMIN is the owner where all the queue resides.
If so, you meant the only way is to create a procedure instead of below anonymous block and grant execute privilege on the procedure to OPS$CDR726?
But again question is why I should not be able to execute the below piece of code embedding in a sql?
Would any other privilege be required?
|
|
|
|
Re: Administering queue [message #669069 is a reply to message #669065] |
Sat, 31 March 2018 04:05 |
arijit2004
Messages: 9 Registered: September 2007
|
Junior Member |
|
|
Quote:
Review this point to make the correct account the owner.
if OPS$CDR726 is the one that uses and, above all, manages the queue, it should be its owner. But in some cases there might be a need of invoking it using
other user with required privilege. In that case it is recommended to create a procedure in AQ_ADMIN and grant the privilege to execute this procedure to the OPS$CDR726?
Or login through OPS$CDR726 and connect to sqlplus using the owner of the queue ie AQ_ADMIN and invoke the PL/SQL block?
I believe with this way we can invoke the anonymous block?
or, this user should be part of DBA group??
[Updated on: Sat, 31 March 2018 04:05] Report message to a moderator
|
|
|
|
Re: Administering queue [message #669071 is a reply to message #669070] |
Sat, 31 March 2018 04:53 |
arijit2004
Messages: 9 Registered: September 2007
|
Junior Member |
|
|
Michel Cadot wrote on Sat, 31 March 2018 04:09
The owner should be the one that manages the queue (start/stop/...).
Other users have to be granted the appropriate privileges (ENQUEUE/DEQUEUE/...).
If the anonymous block has to be invoked then OS user should have DBA privs as well? May be invoke the block using the user which have DBA privs..
Many Thanks!!
|
|
|