Re: Revoking delete causes Error 580
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
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