How to set role in distributed query in IDS 11.5
A user on IDS 11.5 had a stored procedure in database db_devA doing a cross-database SELECT from db_devB, which now failed with no select privileges because access requires enabling a role. He asked where to put SET ROLE in the procedure. Art Kagel explained that SET ROLE (or EXEC SQL) in the local procedure doesn't carry to the remote database, since the cross-database access runs in a separate, non-persistent context. Suggested fixes: call a DBA-privileged procedure in the secure database (rejected, as the user can't create procedures there), or assign each user a DEFAULT ROLE in the secure database so no manual SET ROLE is needed. The thread ends without the poster confirming success.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi All,
I have a stored procedure which talks to a different database residing in the
same server. Previously there were no roles defined but now since we have
various roles defined the regular select query doesn't work. I have to set the
role first.
So suppose if I am in database db_devA and a stored procedure is defined as
'test_procA' and I have to get some information from database db_devB by using
this stored procedure, I am getting the error that I don't have any select
privileges.
here is my stored procedure:
CREATE Procedure test_procA() RETURNING char(20),char(40);define value1 char(20);
define value1desc char(40);
FOREACH curr FOR
select distinct column1,column2 into value1,value1desc from db_devB:tbl_admin
RETURN value1,value1desc WITH RESUME;
END FOREACH;
end procedure;
I have to add "SET ROLE ROLE_SELECT_PRIVELGE" somewhere in the above defined
stored procedure but don't know how It should be done.
Any Suggestions?
You can just have the local stored procedure execute a DBA procedure in the
other database to return the required data. A DBA procedure will execute
with the DBAs privileges and will therefore have access to the data.
Otherwise, something like this should work:
CREATE FUNCTION "art".role_proc () returning int;
define ret int;
set role select_access_role;
select one into ret from role_test_table;
return ret;
end function;
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Mon, Aug 23, 2010 at 1:01 PM, SAMEER MALHOTRA <srma.kapil@gmail.com>wrote:
> Hi All,
>
> I have a stored procedure which talks to a different database residing in
> the
> same server. Previously there were no roles defined but now since we have
> various roles defined the regular select query doesn't work. I have to set
> the
> role first.
>
> So suppose if I am in database db_devA and a stored procedure is defined as
> 'test_procA' and I have to get some information from database db_devB by
> using
> this stored procedure, I am getting the error that I don't have any select
> privileges.
>
> here is my stored procedure:
>
> CREATE Procedure test_procA() RETURNING char(20),char(40);> define value1 char(20);
>
> define value1desc char(40);
> FOREACH curr FOR
> select distinct column1,column2 into value1,value1desc from> db_devB:tbl_admin
>
> RETURN value1,value1desc WITH RESUME;
> END FOREACH;
> end procedure;
>
> I have to add "SET ROLE ROLE_SELECT_PRIVELGE" somewhere in the above
> defined
> stored procedure but don't know how It should be done.
>
> Any Suggestions?
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0016e6509f48eb1a83048e81a5bc
Thanks for your reply. I am not allowed to create any stored procedure in the other database. This is secure database and only after specifying the role I can select some tables of that.
You can set the role in a local procedure, however, it will not, I think, carry over to the other database. Give it a try. Otherwise, since you are using IDS 11.xx you can set a default role that give the correct access level in the secure database for the users who will have to execute the remote access procedure. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Aug 23, 2010 at 2:42 PM, SAMEER MALHOTRA <srma.kapil@gmail.com>wrote: > Thanks for your reply. I am not allowed to create any stored procedure in > the > other database. This is secure database and only after specifying the role > I > can select some tables of that. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636af0211704b45048e821f28
Options one did not work, Tried it already. Option two cannot work since I don't have that role defined in my current database. This role is only valid for the secure database. And if I set the default role it gives the error that no role found.
As I suspected, option 1 was no option. As to option #2, NO. You misunderstand! The default role for the user IN THE OTHER - SECURE - database must be set so that the user can access the data without having to manually set a role there. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Aug 23, 2010 at 2:55 PM, SAMEER MALHOTRA <srma.kapil@gmail.com>wrote: > Options one did not work, Tried it already. > Option two cannot work since I don't have that role defined in my current > database. This role is only valid for the secure database. And if I set the > default role it gives the error that no role found. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016e68ef43e203302048e823d43
Just saw the EXEC SQL command and I thought I could do something like EXEC SQL SET ROLE ROLE_SELECT_PRIVELGE; if there is a way to specify which database to use. But the question is how to specify which database to use?
No, that will not work, it still executes only in the local database. Even if you could execute it in the remote database, that kind of remote connection is not persistent, so the SELECT statement(s) would be executing in a different session on the remote database and so would not retain the role you have set. DEFAULT ROLE for each user in the secure database! Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Aug 23, 2010 at 3:00 PM, SAMEER MALHOTRA <srma.kapil@gmail.com>wrote: > Just saw the EXEC SQL command and I thought I could do something like > > EXEC SQL SET ROLE ROLE_SELECT_PRIVELGE; if there is a way to specify which > database to use. > > But the question is how to specify which database to use? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636af0209d5bfa0048e8280ab