Re: Revoking delete causes Error 580

Spyros Macris wrote: > > If the DBA ( where DBA = my user name) tries to - " revoke delete on > table from DBA " error 580 occurs - cannot revoke permission and none > of the conditions in the error 580 description are true in this case. > > I am the DBA, owner of the database and table, and am able to - " > revoke delete on table from public" -- or another user as long as that> user has been granted the privilege I am revoking. > > However, I am still able to delete from this table and I would like to > revoke my delete privileges - I do not want to accidentally delete from> certain tables either from a Perform screen or an SQL statement. > > Is this because as DBA, the DBA cannot revoke permission from himself > or herself? > > For example, I tested the following: > 1) Revoked all on table > 2) Then selected for that table in systabauth (and syscolauth) and found > no rows. > 3) Granted delete to DBA > 4) Still found no rows. > > Other than creating a database or table under another user's ownership > or logging in as another user, is there any way to accomplish this DBA > revoking of a the same DBA's delete privilege? > > I am using SE and SQL version 7.20.UD1. I am not quite sure what your ultimate question is. So I will answer from the other end, and explain the permissions. You are perhaps missing the meaning of the different layers of privilege, which are: Database level Table level Column level First of all you need privileges on the database before you can access tables or columns. Then you need privileges on the tables you need access to, etc. The database level privileges, which seems to be where you are stuck, are as follows: DBA - Can do anything RESOURCE - Can create and drop tables CONNECT - Can only connect to the database If you are a DBA (Informix has implicit DBA privileges on all databases), then you can access anything and you can also grant and revoke privileges to and from other users. However, you cannot revokeDBA from yourself. You must get another DBA to do this. I think perhaps the solution to your problem is to lower this users database privilege to either CONNECT or RESOURCE. <GROAN="nothing to do with your problem, but might help others"> Incidentally, while I am on the subject, I found an interesting bug the other day. Although Informix has implicit DBA privileges on any database, even a remote database, this is not recognised by SPs. SPs accessing remote tables are looking for explicit DBA privileges for the user running the SP, which in my case was informix. So you can: SELECT * FROM <DB>@<REMOTESERVER>:<table> INTO TEMP tab1 in dbaccess, but you get NO SELECT PERMISSION if you try: CREATE PROCEDURE p1() SELECT * FROM <DB>@<REMOTESERVER>:<table> INTO TEMP tab1 END PROCEDURE ; EXECUTE PROCEDURE p1() </GROAN> Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@mydas.freeserve.co.uk |//////// /| | http://www.informix.com http://www.informixhandbook.com |///// / //| | http://www.iiug.org +-----------------------------------+//// / ///| | |What year 2000 bug? year 2000 bug? |/// / ////| | |year 2000 bug? year 2000 bug? year |// / /////| | |2000 bug? year 2000 bug? year 1900 |/ ////////| +----------------------+-----------------------------------+-----------+