Re: Role names and user ids

Jeff Craig wrote: > > Informix is likely looking at sysusers. Rows (i.e., "users") are added > to this table whenever the Database Administrator grants the CONNECT > privilege to the user. You can probably get rid of the error you are > encountering by issuing REVOKE CONNECT FROM VIEWONLY. > Jeff, Thanks for your input, however, VIEWONLY is not in sysusers. We don't grant CONNECT to users specifically, just to PUBLIC. I tried a new test - I created a new database and then the very first statement I tried to execute in the new database was "create role viewonly" and I got the same error. I'm wondering if it is finding viewonly somewhere in the sysmaster database. I didn't find it anywhere (like sysusers, sysroleauth, systabauth or syscolauth). Any more clues? Thanks, Jay. Jay Sullivan wrote: }>Hello, }> }>We have Online Workgroup Server 7.12.TC3 running on a Windows NT 4.0 }>server. I am trying to set up a few new roles with the "CREATE ROLE" }>statement and I'm having a problem creating a particular role. }> }>I would like to create a role called VIEWONLY. However, I didn't }>realize that you can't have a role with the same name as a login id. I }>had already created an NT user with the login id VIEWONLY. I had also }>logged into Informix at least once as VIEWONLY. }> }>After I realized that I couldn't have a role with the same name as a }>login id, I deleted the VIEWONLY user from NT. However, Informix must }>still think that it exists because I still get the same error when I try }>to add it. The error messages is: "SQL Error (-19800): Role name }>already exists as a user or role." I could not find this error in the }>Find Error utility. }> }>I ran a few tests and it seems like Informix is storing the login ids of }>everyone who has logged in and is checking that list for conflicts when }>setting up a role. For example, I can create an NT user called "ABC" }>and still create a role called "ABC". It is not until I have actually }>logged into Informix as ABC when I start having a problem. After I have }>logged in as ABC and then logged out and killed the NT user ABC, I }>cannot create a role called ABC. Informix still thinks that the user }>exists. }> }>Does Informix store these login ids in some system table where I can }>delete the non-existant VIEWONLY user? Just to let you know, I would }>normally give up and choose a different name besides VIEWONLY, but that }>is not practical in this situation. }> }>Thanks for any help, }>Jay Sullivan. }>