Re: Role names and user ids

}>From ilist@rmy.emory.edu Mon Apr 21 19:01:36 1997 }>Received: from rmy.rmy.emory.edu by hobbes.dbintellect.com via ESMTP (940816.SGI.8.6.9/930416.SGI.AUTO) }> for <settler@dbintellect.com> id TAA02591; Mon, 21 Apr 1997 19:01:31 -0600 }>Received: (from ilist@localhost) by rmy.rmy.emory.edu (8.7.1/8.7.1) id TAA14233 for informix-list-out; Mon, 21 Apr 1997 19:00:01 -0400 (EDT) }>From: "Jay D. Sullivan" <jnelaine@erols.com> }>Message-Id: <335BE4CE.6BE7@erols.com> }>Subject: Role names and user ids }>Date: Mon, 21 Apr 1997 18:06:06 -0400 }>Reply-To: "Jay D. Sullivan" <jnelaine@erols.com> }>Organization: Erol's Internet Services }>Sender: informix-list-owner@rmy.emory.edu }>To: informix-list@rmy.emory.edu }>X-Informix-List-Id: <news.36959> }> }> }>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. }> 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 Craig | dbINTELLECT Technologies, Inc. Senior Consultant | Golden, CO | (303) 275-6954 ext. 8008 | email: settler@dbintellect.com | fax: (303) 275-2134 -------------------------------------------------------------------