Re: Revoking delete causes Error 580

Is a solution to grant resouce privilege to the database management team and keep DBA privilege separate? regards Malcolm At 11:57 AM 1/21/00 -0500, Art S. Kagel wrote: >Actually the answer is: Don't make yourself a DBA, use a specific DBA >account for maintenance when priveleges are needed or, conversely, >create a non-priveleged account for making dangerous mods. > >Art S. Kagel > >Spyros Macris wrote: >> >> Thanks very much for your reply. >> >> My ultimate question is: can a DBA have its powers limited - specifically >> revoking delete on certain tables -- and the answer is no. >> >> For example, what I was looking for is the DBA being able to set certain >> controls or privileges - like revoke delete - to protect the integrity of the >> data so that the DBA would not accidentally delete row(s) of data. >> >> Since I need to maintain my DBA status, I assume the answer to my request is >> 1) be careful and 2) backup regularly. >> >> Mark, do you have any other thoughts? Thanks again for taking the time to >> reply. >> >> "Mark D. Stock" wrote: >> >> > 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 revoke>> > DBA 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 |/ ////////| >> > +----------------------+-----------------------------------+-----------+ >> >> -- >> Spyros Macris, President >> Philadelphia Candies, Inc. >> 1546 East State Street >> Hermitage, PA 16148 >> >> Phone - 724 981 6341 >> >> Fax - 724 981 6490 >> >> email - spyros@phillyc.com > >