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