Re: grant, revoke and ownership

Jonathan Leffler wrote: > > >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! Jonathan, Thankyou for your reply, I'm sure it will be usefull when we migrate to 7.2 and get the doccumentation you quoted from, but, as stated, we are currently running 5.0 (and 5.05) and the behaviour of this engine is as reported in my original append. REVOKE ignores 'AS informix' and there is no error message or sqlca structure codes. We are not trying to grant or revoke database privilages, only table ones. Does your reply imply that if the table was created with owner 'informix' the engine assumes that 'informix' granted all the permissions as well, not the actual dba who issued the create command? This would explain the behaviour, although not the absence of error codes. I assume that there is no way of changing the ownership of a table once it has been created. The 5.0 SQL Reference Manual states this under the RENAME TABLE command on Page 7-233. I'm sorry that you do not like my no.spam email address, but I'm fed up with receiving get-rich-quick and other junk mail from people who can't be bothered to note that I'm in the UK not the USA and consequently their trash is irrelevant as well as offensive. However this is not the place to discuss our difference of opinion, but I would be happy to do so in some appropriate news group of your choice. Peter. -- Please send e-mail to peter_jennings@uk.ibm.com