Informix

 View Only
Expand all | Collapse all

Issue with roles

  • 1.  Issue with roles

    Posted Tue December 06, 2022 10:55 AM
    IDS 14.10.FC7W1
    RHEL 8.6

    I'm having the weirdest problem with roles.  I'm running the following sequence of statements while connected as the owner of the database:

    database my_dev_database;
    -- successfully connected to database

    select * from sysusers where usertype = "G";
    -- no rows returned, so no roles exist in database

    select * from sysusers where username = 'test_role_123';
    -- no rows returned, so user does not exist in database

    create role 'test_role_123';
    -- returns error -19800 Role name already exists as a user or role

    select * from sysusers where usertype = "G";
    -- no rows returned, so no roles exist in database

    select * from sysusers where username = 'test_role_123';
    -- no rows returned, so user does not exist in database

    drop role 'test_role_123';
    -- returns error -19804 Role does not exist

    So the role doesn't exist, and there is no user with the same name as the role, and yet I cannot add the role because it (or a user with that name) already exists and I can't drop it because it doesn't exist.

    I've also checked sysroleauth to confirm that there are no rows in that table.  I had thought that maybe there was a glitch where a sysroleauth row existed for that role.  I'm not sure how that would have happened, but at least now I've confirmed that this is NOT the reason for the above situation.

    There are only four rows in sysusers, two with usertype 'D' and two with 'C'.  These users do not have a defrole.

    I am able to create a role with a different name.  It's just a couple of roles that are giving me this problem.  Unfortunately, our app is expecting these roles that I can't create.  These roles exist in another database, but two databases exhibit this problem.  

    Any thoughts?

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

    #Informix


  • 2.  RE: Issue with roles

    IBM Champion
    Posted Tue December 06, 2022 11:18 AM
    To rule out an index problem:

    $ oncheck -cDI <db>:sysusers

    To rule out a conflict with OS user database:
    $ id test_role_123
    $ getent passwd test_role_123

    Can't think of another conflict right now.

    HTH!

    ------------------------------
    Andreas Legner
    ------------------------------



  • 3.  RE: Issue with roles

    Posted Tue December 06, 2022 11:27 AM
    Oncheck output:

    [informix@devserver ~]$ oncheck -cDI my_test_database:sysusers

    Validating indexes for my_test_database:informix.sysusers...
                              Index users

    TBLspace data check for my_test_database:informix.sysusers

    [informix@devserver ~]



    Output from 'id':

    [informix@devserver ~]$ id test_role_123
    id: 'test_role_123': no such user



    Output from getent:

    [informix@devserver ~]$ getent passwd test_role_123
    [informix@devserver ~]$



    Ran this for all three databases and both role names, with the same results in each case.

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



  • 4.  RE: Issue with roles

    IBM Champion
    Posted Tue December 06, 2022 11:35 AM
    Check for a surrogate user:

    select * from syssurrogateauth;

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



  • 5.  RE: Issue with roles

    Posted Tue December 06, 2022 11:53 AM
    I must admit, syssurrogateauth is a new table to me.  I ran the SELECT * in all three databases, and received 0 rows in all three.

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



  • 6.  RE: Issue with roles

    IBM Champion
    Posted Tue December 06, 2022 01:22 PM
    Mark:

    FWIW: The syssurrogateauth table holds mappings enabling the SET SESSION AUTHORIZATION statement to be used by an authorized user to take on the privileges of another user, essentially to become that user. The privilege is set, creating the row in syssurrogateauth, with the GRANT SETSESSIONAUTH statement.

    Art



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



  • 7.  RE: Issue with roles

    IBM Champion
    Posted Tue December 06, 2022 12:12 PM
    Here's a more comprehensive list of tables to check:
    • sysroleauth
    • systabauth
    • syscolauth
    • sysprocauth
    • sysxtdtypeauth
    • sysfragauth
    you got to check both grantor and grantee columns.

    Hope this will do now!

    ------------------------------
    Andreas Legner
    ------------------------------



  • 8.  RE: Issue with roles

    Posted Tue December 06, 2022 01:15 PM
    Andreas,

    Thank you.  I had looked at sysroleauth and systabauth, but not all of the others.  In this case, the issue was with sysprocauth.  I dropped all of the execute permissions granted to these roles, created the roles, and then re-granted the execute permissions.

    It would be nice if the catalog tables used RI to prevent these sort of things.


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



  • 9.  RE: Issue with roles

    IBM Champion
    Posted Tue December 06, 2022 01:26 PM
    Hi Mark,

    understanding your concern, but there generally is no RI defined among catalogs, probably for good reason.
    I'd see room for improvement in 'finderr 19800', though ...

    It's also interesting that you can grant privileges to a non-existing role/user ... and then are prevented from creating the missing role ;-)
    But there probably are a couple of reasons for this, you just don't immediately expect it when only thinking 'roles'.

    Cheers,
     Andreas

    ------------------------------
    Andreas Legner
    ------------------------------



  • 10.  RE: Issue with roles

    Posted Tue December 06, 2022 03:59 PM
    yeah, maybe RI is a bit more than should be expected, perhaps for performance reasons.  But definitely I would expect the system to confirm that the grantee was in fact a known user before allowing the GRANT to complete.

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



  • 11.  RE: Issue with roles

    IBM Champion
    Posted Tue December 06, 2022 04:23 PM
    One problem with this certainly would be that user and db privilege administration typically occur on different levels and are managed by different people; think only of the most simple case: OS users.
    And then imagine e.g. a dbimport (from a different system) failing because of missing users...

    I requested an update to 'finderr 19800'.

    ------------------------------
    Andreas Legner
    ------------------------------