Re: grant, revoke and ownership

>From: David Williams <djw@smooth1.demon.co.uk> >Date: Mon, 30 Jun 1997 23:24:39 +0100 >X-Informix-List-Id: <news.39895> > >In article <33B17D42.6DF@no.spam>, Peter Jennings <no.spam@no.spam> >writes >>We are running Online 5 under AIX 3.2.5 (soon to be upgraded to 7.2 >>under AIX 4.1.5) >> >>Our customer requires that all tables in his system be owned by >>informix, even those created by other users (in ESQL-C or shell >>scripts). In addition many of the tables require revoking of all >>privilages from "public" and then granting just, say, select. >> >>Users with dba privilage have no problem creating a table owned by >>informix eg:- >>exec sql create table informix.my_name (...etc...); >>which creates a table with all privilages granted to public. >> >>However if the user now attempts to "revoke all on my_name from public" >>there are no error messages, the sqlca structure shows all zeros in the >>significant places and the command appears to have been executed. As is documented... In the 7.2 Informix Guide to SQL: Syntax, Volume 2, on p1-439, it says: You can revoke all or some of the privileges you granted to other users. No-one can revoke privileges that another user grants. [...] Users cannot revoke privileges from themselves. On p1-444, it also says: The ALL keyword revokes all table-level privileges. If any or all of the table privieleges do not exist for the revokee, the REVOKE statement with the ALL keyword executes successfully but returns the following SQLSTATE code: 01006 - Privilege not revoked. Note that when you do a 'GRANT...AS', the database does not record who asked the DB to do the GRANT...AS; it simply records the person who is responsible for the request -- the AS grantor. Therefore, only that person can revoke the privilege. >>The same thing happens if the user does this from inside isql. BUT the >>information on the table shows that public still has all privilages >>granted. Adding "as informix" to the command has no effect. Adding 'AS informix' should cause a syntax error -- it isn't part of the documented REVOKE syntax. > Have you tried running the revoke command in dbaccess? It would make no odds whatsoever which tool is used -- the statement is handled by the engine. >[...] > Revoke you revoked database level privileges from public? > Revoke connet and resource privileges at the database level? A DBA can do this if they granted the database privilege (but not otherwise, eg they can't do it if they did the GRANT...AS informix). >>Is this a "feechur" of Online 5 or should we be doing something else / >>different? This is the documented behaviour -- it is also the correct behaviour. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> PS: I decline to respond to messages with anti-spam in the return path. This message doesn't qualify (quite) - David had an OK address. I didn't reply to Peter, and won't until he removes the no-spam stuff. Nor will I reply to anybody else's message which has anti-spam in it. I don't have the time to mess around with return addresses. And if that means I post fewer answers to c.d.i, so be it. Striking out for honesty in email addresses!