Home » Server Options » Streams & AQ » Calling procedure with a different user (merged)
Calling procedure with a different user (merged) [message #413991] Mon, 20 July 2009 01:13 Go to next message
kumar19841984
Messages: 2
Registered: July 2009
Junior Member
hi,

I have an issue with AQ.

I have created a subscriber on an AQ by
BEGIN
    DBMS_AQADM.ADD_SUBSCRIBER (
    queue_name => 'TEST_REQUEST_QUEUE',
    subscriber => SYS.AQ$_AGENT(
            'TEST_REQ_QUEUE_SUBSCRIBER',
            NULL,
            NULL)
    );
end;


And have registered this user to run a procedure as:

BEGIN
    DBMS_AQ.REGISTER (
        SYS.AQ$_REG_INFO_LIST(
        SYS.AQ$_REG_INFO(
            'TEST_REQUEST_QUEUE:TEST_REQ_QUEUE_SUBSCRIBER',
            DBMS_AQ.NAMESPACE_AQ,
            'plsql://SCHEMA_TEST.pkgtest.test.request_queue_callback',
            HEXTORAW('FF')
                )
            ),
            1
        );
END;


Now when this procedure is executed after enqueue, It runs under SYS user, I want it to run under some specific user say "testUser". Is it possible ?

ALSO,

Is it possible to call a procedure specifying a specific user name and password to run the procedure with ?

thanks a lot,

Re: Calling procedure with a different user (merged) [message #413994 is a reply to message #413991] Mon, 20 July 2009 01:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Grant the appropriate privilege for this
2/ No but a procedure execute by default as (about) the procedure owner

Regards
Michel

[Updated on: Mon, 20 July 2009 01:21]

Report message to a moderator

Re: Calling procedure with a different user (merged) [message #413998 is a reply to message #413994] Mon, 20 July 2009 01:34 Go to previous messageGo to next message
kumar19841984
Messages: 2
Registered: July 2009
Junior Member
Thanks for the reply Smile

1) Cant give access permissions to user SYS. thats not an option here...

2)Inside the procedure i have a insert statement that is something like "Insert USER into ***" this USER is being logged in as SYS... How is this happening if the procedure execute by default as the procedure owner ?

Also is there a way i can make either (1) my specific user "testUser" as a listener to the AO ? or (2) inside the procedure SCHEMA_TEST.pkgtest.test.request_queue_callback run the code as user "testUser" ?

Re: Calling procedure with a different user (merged) [message #414010 is a reply to message #413998] Mon, 20 July 2009 02:16 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Cant give access permissions to user SYS. thats not an option here...

This is not what I said, grant permission to the account you want it executes the procedure.

Quote:
is this happening if the procedure execute by default as the procedure owner ?

This is the purpose of my "(about)". USER function returns the connected user. Nevertheless the procedure executes with the privileges and in the schema of the owner.

Quote:
(1) my specific user "testUser" as a listener to the AO ?

I don't understand the question.

Quote:
(2) inside the procedure SCHEMA_TEST.pkgtest.test.request_queue_callback run the code as user "testUser" ?

Use AUTHID CURRENT_USER in the procedure definition (see CREATE PROCEDURE statement in doc).

Regards
Michel
Previous Topic: Streams capture: waiting for archive log
Next Topic: Triggers and Streams
Goto Forum:
  


Current Time: Sat Dec 03 01:15:30 CST 2016

Total time taken to generate the page: 0.22598 seconds