Informix

Informix

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only

  • 1.  Which users have been granted which roles?

    Posted 05/15/25 12:09 PM

    Now this oughta be straightforward.  (Famous last words?)

    Which system catalog(s) can I query to see which users have granted a specific role?

    In catalog sysusers I see a group (sysusers where usertype = "G") named "managers". (Fake name, of course). What query can I run to see which users have been granted that role?  Or any role, for that matter?  My google search keep turning up les-than-helpful advice to use dbschema or just displaying the same page from the "Guide to SQL Syntax".

    Thanks, all!

    (WOW! Less than a page!  You're losing you touch, Jake! ;-)



    ------------------------------
    Jacob Salomon
    ------------------------------


  • 2.  RE: Which users have been granted which roles?
    Best Answer

    Posted 05/15/25 12:58 PM

    Take a look at sysroleauth for the names of the roles.



    ------------------------------
    Mike Walker
    xDB Systems, Inc
    www.xdbsystems.com
    ------------------------------



  • 3.  RE: Which users have been granted which roles?

    Posted 05/15/25 02:01 PM

    That was it, Mike!  Thanks much.

    Unfortunately for my efforts, it turns out that the solution to my user's issue will not involve roles.  They don't want to deal with "set role" and it's too big a solution for their issue anyway.



    ------------------------------
    Jacob Salomon
    ------------------------------

    Original Message


  • 4.  RE: Which users have been granted which roles?

    Posted 05/15/25 02:18 PM

    Sorry to hear that - roles can be useful.

    FYI, for completeness, I didn't have time to post this earlier, but here's the query joining sysusers and sysroleauth:

    select a.rolename, a.grantee
    from sysroleauth a, sysusers u
    where u.usertype = "G"
      and u.username = a.rolename



    ------------------------------
    Mike Walker
    xDB Systems, Inc
    www.xdbsystems.com
    ------------------------------

    Original Message


  • 5.  RE: Which users have been granted which roles?

    Posted 05/19/25 10:13 AM

    Jacob,

    Is it a case that the users would have to switch from role to role to do their work, and they don't want to do the SET ROLE TO xxxx?  Or is it a case that each user would only have one role, and they don't want to manually run the SET ROLE statement after logging in?

    If it is the latter, did you let them know that each user can have a default role that is assigned each time they connect to the database?



    ------------------------------
    mark collins
    ------------------------------

    Original Message


  • 6.  RE: Which users have been granted which roles?

    Posted 05/19/25 10:55 AM

    Hi Mark.

    In my case, a user was requesting a set of identical privs to be granted to a large number of users.  I proposed the idea of a role to my user but wanted to keep control of the granting.  Hence my question to the group.  And it was simple - a catalog whose name I might have guessed after a while.

    That aside, my user rejected the idea of a role, likely because several apps would have to be modified to add the "set role" command.  And most developers in a large wish to avoid the rigmarole of testing after the most minor (we think) of code changes.



    ------------------------------
    Jacob Salomon
    ------------------------------

    Original Message


  • 7.  RE: Which users have been granted which roles?

    Posted 05/19/25 12:13 PM

    I know I've had developers tell me that either it was too complicated to run a SET ROLE statement, or that the application code (e.g., Ace reports, Perform screens) couldn't be modified to add SET ROLE.  I was able to get around that by doing a GRANT DEFAULT ROLE role_name TO user_name.  This automatically sets the role to the desired role_name based solely on the user_name that was used when connecting to the database, with no source code changes required.



    ------------------------------
    mark collins
    ------------------------------

    Original Message


  • 8.  RE: Which users have been granted which roles?

    Posted 05/19/25 12:42 PM

    Jacob:

    Besides the SET DEFAULT ROLE <role>; to give a userid an initial role everytime they log in after that point, you can use the <username>.sysdbopen() function or the public.sysdbopen() function to set a role at connect time based on other criteria such as the app name or session's IP address. The <username>.sysdbopen() function executes for the user that owns the function and the public.sysdbopen() function executes for all users who do not have a private sysdbopen function that they own. Setting the  role in the sysdbopen() function would work without changing any application code.

    Note that there can also be a sysdbclose() function for user public or for any given user that works similarly which runs when the session disconnects from the server.

    Art



    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------

    Original Message


  • 9.  RE: Which users have been granted which roles?

    Posted 05/20/25 09:14 AM

    If you were to do GRANT DEFAULT ROLE xxx TO user_name, they wouldn't have to do a SET ROLE.  Whatever user_name the app used when connecting to the database, that default role would automatically be put into effect.

    But you did say that roles wouldn't fix their problem anyway.  I'm just trying to point out that developers will argue that it's too much work for them to do something, not necessarily knowing that you can automate things so that they do not have to do anything.



    ------------------------------
    mark collins
    ------------------------------

    Original Message


  • 10.  RE: Which users have been granted which roles?

    Posted 05/15/25 03:16 PM

    Jacob:

    The sysroleauth table defines what roles are valid for a give user to accept using the SET ROLE statement. The sysusers table also contains the column defrole which is the users default role on logging in.

    Art



    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



Related Content