Re: REVOKE

>From: bgirard@crash.cts.com (Brian Girard) >Subject: REVOKE >Date: Fri, 13 May 1994 22:40:19 GMT >X-Informix-List-Id: <news.6731> > >Hello fellow informix users: > > I am having great trouble with using the revoke command. Every so often >a user will "accidently" remove a row(s) and I must unload and load from back- >up. This is ok if the incident is discovered relatively soon. But to combat >this problem, I am trying to revoke all table priviledges except for update. >I have dba priviledges and tried this to no avail: > > revoke delete,alter,index, on clients from brian> >I went in as user brian (who has connect priviledges only) and I was still >able to delete rows?? > >We are using Informix 2.10.03f on SCO sysV r3.2.4 with MPX 3.0 This is an old version running on a much newer system. The first, and key, issue is "Who granted the permissions?" You can investigate this by selecting information from SysTables and SysTabAuth; SELECT T.TabName, A.Grantor, A.Grantee, A.TabAuth FROM SysTables T, SysTabAuth A WHERE T.Tabid = A.Tabid AND T.TabName = "clients"; Now, if there is a row with Grantee = "brian" or Grantee = "public" which gives insert, delete, etc privileges, then regardless of what you say as someone else, brian has the access privileges. So the actual privileges enjoyed by brian are the inclusive OR of all the privileges granted by individuals. Now, as DBA on 4.0 and above systems, you can say: RVEOKE DELETE, ALTER, INDEX ON Clients FROM brian AS owner; where owner is the user who owns the table. This option may not be available in 2.10.03 -- I don't remember when it was introduced and don't have time to go checking. If this doesn't work, you'll have to get the actual table owner to revoke the privileges. In general, giving permissions to public means that it will be very difficult to constrain individual users. On systems I want to be secure, then I do not grant public any database level privileges, and most tables either grant no privileges to public or only grant select privileges. I then have various complex schemes which allow the DBA to grant selected users selected privileges as required by the use they make of the application. Note that once a user has UPDATE privilege, they can do as much damage as someone with INSERT or DELETE privilege by simply changing all the fields in the record, and that if they need the privilege to run a particular I4GL program, then they also have that privilege using any other program, and in particular they can use ISQL or DBACCESS to make the same sorts of changes, but without the programmed controls. See Walt Hultgren's APPSTART program for one way around that problem. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>