Issue with roles

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

To rule out an index problem: $ oncheck -cDI <db>:sysusersTo rule out a conflict with OS user database: $ id test_role_123 $ getent passwd test_role_123Can't think of another conflict right now. HTH! ------------------------------ Andreas Legner ------------------------------

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 ------------------------------

Check for a surrogate user: select * from syssurrogateauth;------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

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 ------------------------------

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 ------------------------------

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 ------------------------------

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 ------------------------------

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 ------------------------------

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 ------------------------------

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 ------------------------------