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