Home » SQL & PL/SQL » SQL & PL/SQL » Administering queue (Oracle 11.2.0.3, AIX)
Administering queue [message #669048] Fri, 30 March 2018 06:31 Go to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8137
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 #669050 is a reply to message #669049] Fri, 30 March 2018 08:49 Go to previous messageGo to next message
Michel Cadot
Messages: 66808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Or execute DBMS_AQADM.GRANT_QUEUE_PRIVILEGE to grant ENQUEUE/DEQUEUE privilege on a queue.

Re: Administering queue [message #669051 is a reply to message #669050] Fri, 30 March 2018 10:46 Go to previous messageGo to next message
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 #669054 is a reply to message #669051] Fri, 30 March 2018 11:15 Go to previous messageGo to next message
Michel Cadot
Messages: 66808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Did you read the documentation I pointed you to?

Quote:
ALL means both ENQUEUE and DEQUEUE.

Where did you see it includes STOP?

Quote:
Should I include schema name explicitly with the queue name
If it is not the owner the queue, yes, but it then needs much more privileges.


Re: Administering queue [message #669055 is a reply to message #669054] Fri, 30 March 2018 13:32 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 66808
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 Go to previous messageGo to next message
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 #669058 is a reply to message #669057] Fri, 30 March 2018 15:06 Go to previous messageGo to next message
Michel Cadot
Messages: 66808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The best way is to create a procedure with this code in the owner schema and grant the privilege to execute this procedure to the OPS$CDR726.

However if OPS$CDR726 is the one that uses and, above all, manages the queue, it should be its owner.

Re: Administering queue [message #669064 is a reply to message #669058] Sat, 31 March 2018 00:50 Go to previous messageGo to next message
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 #669065 is a reply to message #669064] Sat, 31 March 2018 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 66808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
AQ_ADMIN is the owner where all the queue resides.

Review this point to make the correct account the owner.

Quote:
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?

Yes.

Quote:
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?

High privileges that should be granted only to DBA.

Re: Administering queue [message #669069 is a reply to message #669065] Sat, 31 March 2018 04:05 Go to previous messageGo to next message
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 #669070 is a reply to message #669069] Sat, 31 March 2018 04:09 Go to previous messageGo to next message
Michel Cadot
Messages: 66808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The owner should be the one that manages the queue (start/stop/...).
Other users have to be granted the appropriate privileges (ENQUEUE/DEQUEUE/...).

Re: Administering queue [message #669071 is a reply to message #669070] Sat, 31 March 2018 04:53 Go to previous message
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!!
Previous Topic: getting error "table table1 is mutating, trigger/function may no" on executing trigger
Next Topic: Resource allocation problem
Goto Forum:
  


Current Time: Wed Jan 29 01:23:03 CST 2020