OraFAQ Forum: Streams & AQ » Calling procedure with a different user (merged)

Home » Server Options » Streams & AQ » Calling procedure with a different user (merged)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Calling procedure with a different user (merged) [message #413991]

Mon, 20 July 2009 01:13

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,

Report message to a moderator

Re: Calling procedure with a different user (merged) [message #413994 is a reply to message #413991]

Mon, 20 July 2009 01:20

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

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

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" ?

Report message to a moderator

Re: Calling procedure with a different user (merged) [message #414010 is a reply to message #413998]

Mon, 20 July 2009 02:16

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Previous Topic:	Streams capture: waiting for archive log
Next Topic:	Triggers and Streams

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Feb 08 22:23:58 CST 2025