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.  Issue with role

    Posted 04/05/22 03:14 PM
    IDS 14.10.FC7W1
    RHEL 8.5

    We have two databases that are related, and are creating some roles and assigning permissions to those roles.  The same role name will exist in both databases.  The first database works just fine, the role gets created, permissions assigned, the role is assigned as a default role to some users.

    Then we change to the second database and attempt to create the role.  At this point, we get '19800: Role name already exists as a user or role.'  However, if I do:


    SELECT username, usertype
    FROM sysusers
    WHERE username like "my_role%";


    I get "no rows found".  And if that wasn't weird enough, I can do:

    GRANT SELECT ON some_table TO my_role;

    and it does not generate any errors.  I confirmed that the systabauth table now has entries for this table/row combination.

    I've tried "DROP ROLE my_role;", but get "19804: Role does not exist." followed by "111: ISAM error: no record found."

    So from the above, it appears that the role can't be added, because it exists, yet it cannot be dropped, because it does not exist.  And I can add a permission to that role with no errors, with the permission(s) recorded in systabauth.

    I then looked at systabauth and found that there were several other permissions (in addition to the permission(s) that I GRANTed a moment ago) with that role listed as grantee.  Hmmm.

    After revoking all of the table-level permissions from that role, I was able to add the role in this second database, and then re-GRANT the permissions to that role.

    This leads to a few questions, such as:

    1.  Why does Informix allow GRANTing a table-level permission when there is no matching entry in sysusers?
    2.  Why does it cause an error trying to CREATE a role if there are already rows in systabauth with that value for grantee?  (I'm guessing that it's a security thing, so that you don't inadvertently create a role and it get permissions that were just hanging around in systabauth, but would like confirmation or other theories.)
    3.  Can we get the finderr output for -19800 updated to point out that we need to check systabauth (and possibly sysprocauth) when receiving this error?
    4.  Why doesn't Informix REVOKE all permissions GRANTed to a user you REVOKE CONNECT/RESOURECE/DBA permissions from that user?  I've tried setting up a user with GRANT DBA, and then revoking DBA, RESOURCE, and CONNECT, and confirming that there are no rows in sysusers for that user.  (I'm guessing that the reason for this is for cases where permissions had been granted WITH GRANT OPTION, so that you would end up with a potentially huge set of cascading REVOKEs, but would like to hear other opinions or confirmation.)
    5.  Why does it not cause a problem if I GRANT table-level permissions to a non-existent user (as opposed to a role) and then subsequently create that user with a "GRANT CONNECT TO user" statement?  The end result of both the GRANT CONNECT and the CREATE ROLE is to create a row in sysusers, so why is one treated so different from the other?

    I probably will come up with additional questions based on the responses to the above, but that's a pretty good start.


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

    #Informix


  • 2.  RE: Issue with role

    Posted 04/05/22 03:50 PM
    Marc:

    Simple answer to, I believe, all of your questions:

    Remember that primarily, and originally only, supports OS level users and it has no idea if a user name is valid or not when you grant a privilege to it. So, since there were privileges already granted to the name you wanted to add as a role, that was a clash between the role and the assumed user name and so that failed. You could grant more privs to the "user", no problem. Then once all of the privileges associated with that name had been dropped you could then add the role.

    Informix permits assigning privs to user names that are not in sysusers because most users in the Informix world are NOT EVER in sysusers. That table is used to record the default role of a user and to record any user id that has a database level privilege (connect, resource, or dba). If a user has no database level privilege and no default role then it will have no record in sysusers.

    Art

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



  • 3.  RE: Issue with role

    Posted 04/05/22 04:17 PM
    Hi Art,

    I'm not sure I understand your comment that "most users in the Informix world are NOT EVER in sysusers".  If a user doesn't have at least CONNECT permission, how are they considered even an Informix user?  How can they do anything in the database?  

    I know that for OS users, Informix uses the OS to authenticate the user/passwd combination, but I thought that the sysusers table was necessary to confirm that the user had any privileges in the database.  Even if the user has no explicit permissions on tables or procedures, and relies strictly on "public" permissions, they would require database privileges, and thus would require a sysusers row.

    On the other point, the part that doesn't make sense to me is that I can do a GRANT CONNECT  statement even if there are already privileges GRANTed to the user.  It seems to me that it should behave the same whether adding the user via GRANT CONNECT or adding a role via CREATE ROLE.  But this might be because I am missing your point about Informix users not being in sysusers.

    Have I been mistaken about the purpose of sysusers for all these years?  




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

    Original Message


  • 4.  RE: Issue with role

    Posted 04/05/22 05:22 PM

    Marc:

    Yeah, don't worry about that comment about "most users". Not enough coffee this morning to get me through the afternoon.

    OK, so you could not create the role because when a user is added to the systabauth, syscolauth, or even sysusers indirectly say by creating a table or column privilege or granting a default role to it, that user name is assumed to be a "user" and not a role. So, when you tried to create a role with the same name it fails because the name already exists and it's not a role. The error, -19800 is described as "Role name already exists as a user or role". Any role must have an entry in sysusers with usertype 'G', so if there are privs for some name that does not have a row in sysusers, it must be a partially privileged "user", it can't be a role and you cannot create a role with the same name as any existing user name.

    As to why it is permitted to create a user privilege for a user id that has no connect privilege, that probably has to do with design decisions made over 30 years ago to not have any relational integrity support in the system catalog tables. There are no constraints requiring that entries in systabauth or syscolauth refer to a row in sysusers.



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

    Original Message


  • 5.  RE: Issue with role

    Posted 04/05/22 05:48 PM
    Hi Art,

    Thanks.  That clears things up a bit.  And I totally get that many of these decisions (like no RI on catalog tables) happened many years ago, and must be continued for backward compatibility.

    It just was odd to be told that the role already existed as a role or a user, and yet not be able to find it in sysusers.  It just seems like it should work like the validation that happens on table names.  By that I mean, I cannot do "GRANT SELECT ON mc_nonexistent_table TO fred;" if there is no table named mc_nonexistent_table, yet it doesn't care that there is no row in sysusers for username = 'fred'.  Obviously, in this case, the failure is due to the fact that systabauth uses tabid, so the engine has to first look up the table name in systables, and when it can't find that, it gives us the error message.  While it may not be implemented as true RI, it has the same end result.

    For whatever reason (30+ years ago), there is no userid column in sysusers analogous to the tabid in systables.  Thus systabauth, sysroleauth, syscolauth, and sysprocauth all have to use the char(32) name for grantor/grantee.

    Now I'm wondering how it knows to reject the role name.  How does it get the list of these "partially privileged 'users'" as you called them?  Does it scan all of the indexes on the *auth tables, looking for that name as grantee for any privilege?  That seems terribly inefficient, especially as grantee isn't the first column of any of the indexes on the *auth tables (edit - it is the leading column of the gteerole index on sysroleauth).


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

    Original Message


Related Content