Revoke Weirdness 1

[Michael42]

Hello All,

As sysdba, when I attempt to REVOKE a privlege as sysdba I am getting the message:

[b]ORA-01927[/b] Cannot REVOKE privleges you did not grant

Though MetaLink seems to be confirming this, I cannot believe you cannot dor a REVOKE as sysdba. In my case there a hundreds of privs that need to be yanked in this environment. Determining and then deleting them as the actual creator may not be practical.

Any suggestions?

Thanks,

Michael42

 

[SantaMufasa]

Michael,

As you discovered, in Oracle 8, only the user who granted a privilege on a table can revoke it; not even the table owner can revoke privileges on her/his own table if s/he did not grant the privilege to the user!!! (What a stupid design decision on Oracle's part.)

Since, as you also discovered, that is an administrative nightmare, Oracle changed that behaviour in Oracle 9: a DBA can revoke table privileges on any table from any user, even if s/he did not originally grant the privilege.

I could probably write a script to deal with this problem in Oracle 8. Let me know if that is something you want.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 19:31 (05Jan05) UTC (aka "GMT" and "Zulu"),
@ 12:31 (05Jan05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.

 

[Michael42]

SantaMufasa,

>> not even the table owner can revoke privileges on her/his own table if s/he did not grant the privilege to the user!!!

Wow! Thanks for confirming and for your offer on a script. Perhaps if I know a bit more about the options here I could be more effective.

How would one change to another user to perform a REVOKE, i.e. is there an su - <username> like in UNIX?

One more related question please:

I also noticed that in this same environment that many privs are ganted to PUBLIC (yikes). Does this follow the same rules where as they can only be revoked by the creator?

Thanks,

Michael42

 

[SantaMufasa]

Michael,

Well, I've spent all day trying to programmatically resolve this problem. What a tangled mess !!! No wonder Oracle changed their GRANT/REVOKE strategies for Oracle 9.

Here are the problems I encountered during programming to automate REVOKEs:

1) Had to identify Who, granted What, to Whom. Resolved.
2) Had to be connected to GRANTing user to REVOKE. Resolved.
3) Had to ensure that REVOKEs occur in reverse order from GRANTs. Unresolved. (This is the mess-maker.)

But, there is a way simple resolution that takes care of your problem with three commands (without having to go through all the connecting and highly ordered revoking):

CREATE TABLE x AS SELECT * FROM <target table>;
DROP TABLE <target table>;
RENAME x TO <target table>;

Once you DROP TABLE <target table>, all GRANTS on your <target table> disappear, regardless of who is responsible for the original GRANT.

The only problem with the above technique is that declared Primary Key, Foreign Key, and Unique constraints do not replicate with the "CREATE TABLE...as SELECT..." command. There are several ways to easily resolve that problem, as well. But before I go through that exercise with you, let's confirm whether or not that is an issue/need for you.

Let me know,

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 07:13 (06Jan05) UTC (aka "GMT" and "Zulu"),
@ 00:13 (06Jan05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.

 

[Michael42]

SantaMufasa,

>> I've spent all day trying to programmatically resolve this problem

I do not have words to convey my gratitude for your time. Though this is a task that must be completed, I am not in emergency mode (yet). Your suggestions here, as always go a long way.

>> The only problem with the above technique is that declared Primary Key, Foreign Key, and Unique constraints do not replicate with the "CREATE TABLE...as SELECT..." command.

No worries here...I have scripts at this point to recreate these (via DDL wizard - GREAT tool). Hey, an X college of mine seems to have an an "su" type technique. I'll post it as soon as I can, it looks really cool.



Thanks,

Michael42

 

[Michael42]

As prmomised, here are the steps to change to another user's account to perform a task (REVOKE in this case):

Enjoy!

First capture the  user's current encrypted
password and display it on the screen:
SQL> create table somedude_pword as select
     password from dba_users 
    where username =   'SOMEDUDE';
 
    Table created.
    SQL> select * from somedude_pword;
 
   PASSWORD
   ----------------
   C8051199DC00FA48
 
   1 row selected.
 
 Next, alter the user to whatever password you like,
 connect, and grant away:
 
   SQL> alter user somedude identified by my_pword;
 
   User altered.
 
   SQL> connect somedude/my_pword
   Connected.
   SQL> -- grant away
 
 Last, connect w/dba privs again and put the password
 back:
 
   SQL> connect /
   Connected.
   SQL> alter user somedude identified by values
 'C8051199DC00FA48';
 
   User altered.
 
Notice the 'by values' part of the above command and
the single quotes.

 

[SantaMufasa]

Correct, Michael. The technique you list is the one I coded for my Step 2, above. Step 3 is the "bear"...I had to use Oracle's "START WITH...CONNECT BY..." logic. During the Step 3 coding, I discovered the simplicity of the "duplicate...drop...rename" technique.

And another "yes" to the DDL Wizard. That was my suggestion if you have PKs, FKs, and UKs to deal with.

Good thinking, bud. Hope all goes well for you.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 15:41 (06Jan05) UTC (aka "GMT" and "Zulu"),
@ 08:41 (06Jan05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.