Reverse a Revoke Delete on owned table

Database
tmw
Enthusiast

Reverse a Revoke Delete on owned table

Okay, so I own a table that I created with my own user ID.  On a regular basis, I insert new records into the table.  To prevent accidentally deleting records, I revoked delete access to myself (revoke del on table to my_id).

Sure enough, now I have a legitimate need to delete a record from the table.  I can't grant myself delete access (grant del on table to my_id fails), although I can grant all on table to my_id which says it works, although it doesn't grant delete access.

Is there any way to restore the delete access which I revoked to myself on my own table?  I am getting the feeling that I will have to drop and re-create the table to be able to delete the record.  [drop privilege still works].

Thanks

1 REPLY
Senior Apprentice

Re: Reverse a Revoke Delete on owned table

Seems you're the Creator (submited the CREATE), but not an Owner (based on the hierarchy of databases). In that case you lost the Delete right and can't grant it again.

Either ask your DBA to grant it back to you or do a CREATE TABLE AS and DROP.