ROLE FOR USERS
A user wanted granted roles to apply automatically at login so users wouldn't have to type SET ROLE in each dbaccess session. Answer: DEFAULT ROLE support was only added in later versions (10/11), so on his IDS 7.31 on AIX 5.3 there is no automatic option. Workarounds suggested: store each user's role in a PUBLIC-readable lookup table and have applications issue SET ROLE right after connecting; or give users an SQL script to run at the start of a dbaccess session; or modify Jonathan Leffler's sqlcmd to set the role on login. A shell script or stored procedure won't work, since SET ROLE affects only the current session and 7.31 lacks dynamic SQL in procedures. Upgrading was recommended.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I am trying to set up role based access to user on certain table privilege , I
have created the roles , granted specific table level privilege to role and
granted the role to users , but the user needs to give SET ROLE role_name
every time after he logs in to dbaccess , HOW to make the role default to the
users , so that user after login in gets by default has the role set for him,
I checked up the Documents also ,it says DBSA needs to GRANT DEFAULT ROLE to
PUBLIC or to specific user.I am login in as informix user ,My question is
1) How to set the role to user , so that it remains persistent across session.
2) how to identify which user is DBSA .
Version please. I believe you can only set default role in version 10 and
above.
Grant default role "xxxx" to "user_id";
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of SUJIT ROY
Sent: Wednesday, December 09, 2009 2:52 PM
To: ids@iiug.org
Subject: ROLE FOR USERS [18330]
I am trying to set up role based access to user on certain table privilege , I
have created the roles , granted specific table level privilege to role and
granted the role to users , but the user needs to give SET ROLE role_name
every time after he logs in to dbaccess , HOW to make the role default to the
users , so that user after login in gets by default has the role set for him,
I checked up the Documents also ,it says DBSA needs to GRANT DEFAULT ROLE to
PUBLIC or to specific user.I am login in as informix user ,My question is
1) How to set the role to user , so that it remains persistent across session.
2) how to identify which user is DBSA .
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
IDS version is 7.31
Default role is introduced in v10. Users will have to explicitly set their role in the application code in versions prior to that. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of SUJIT ROY Sent: Wednesday, December 09, 2009 3:20 PM To: ids@iiug.org Subject: Re: RE: ROLE FOR USERS [18332] IDS version is 7.31 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
YOU MUST POST YOUR VERSION AND PLATFORM INFORMATION IN ORDER FOR US TO GIVE
YOU A CORRECT AND SIMPLE RESPONSE TO YOUR
POSTS!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!!!!!!!!!!!!!!1
IF AND ONLY IF you have 11.XX you can define a DEFAULT ROLE for each user.
If you have any earlier release (ie 7, 9, 10) there's nothing you can do but
upgrade to 11.50 or modify all of your applications to set the user's role
for him/her. There are relatively simple ways to do that, but since I don't
know if I'm typing all of this for no reason - BECAUSE YOU DIDN"T POST YOUR
VERSION INFO - I'm not going to tell you how. 8-P
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. 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 Wed, Dec 9, 2009 at 3:51 PM, SUJIT ROY <roysujit@hotmail.com> wrote:
> I am trying to set up role based access to user on certain table privilege
> , I
> have created the roles , granted specific table level privilege to role and
> granted the role to users , but the user needs to give SET ROLE role_name
> every time after he logs in to dbaccess , HOW to make the role default to
> the
> users , so that user after login in gets by default has the role set for
> him,
>
> I checked up the Documents also ,it says DBSA needs to GRANT DEFAULT ROLE
> to
> PUBLIC or to specific user.I am login in as informix user ,My question is
> 1) How to set the role to user , so that it remains persistent across
> session.
> 2) how to identify which user is DBSA .
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--00032555bc32ecc1f9047a52fc44
Sorry , I missed out on Version and Platform , Version is 7.31 and platform is IBM AIX ver 5.3 .Please do give the steps how to implement the role's , and I need them desperately.
You should make a table that has PUBLIC SELECT privilege and place a row in there for each user which his default role. Then in your applications, immediately after connecting to the server, extract the default role from that lookup table and execute a SET ROLE... operation. It's not automatic, and it doesn't address interactive sessions or generic query tools, but it's the best you can do until you upgrade to 11.50 which you should do ASAP as 7.31 has been out of support since October 1, 2009. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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 Thu, Dec 10, 2009 at 12:41 AM, SUJIT ROY <roysujit@hotmail.com> wrote: > Sorry , I missed out on Version and Platform , Version is 7.31 and platform > is > IBM AIX ver 5.3 .Please do give the steps how to implement the role's , and > I > need them desperately. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0023545bd59088193b047a5d9dfd
Thanks .
can I use any method/shell script which will set role for the user while using
dbaccess?
You can write an SQL script for each group of users that sets the correct
role and each user can execute it from their dbaccess session before the
user does anything else, yes.
It can't be an external shell script because the SET ROLE has to be executed
from the session it is to take effect in. SET ROLE is not permanent, it
only has an effect during the current session.
You also can't use a stored procedure because the role name in the SET ROLE
statement can't be a host variable and 7.31 doesn't support dynamic SQL in
stored procedures so you can't PREPARE and execute a statement from text.
Another option would be to get Jonathan Leffler's sqlcmd SQL execution tool
from the IIUG Software Repository and modify it to automagically fetch the
user's proper role from that table I mentioned then create, prepare, and
execute a CREATE ROLE statement every time a user logs in. Then have your
users use sqlcmd instead of dbaccess.
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. 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 Thu, Dec 10, 2009 at 7:14 AM, SUJIT ROY <roysujit@hotmail.com> wrote:
> Thanks .
> can I use any method/shell script which will set role for the user while
> using
> dbaccess?
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--00151744857c47c385047a6086af
Thanks once again for your feedback