One stop shop revoke tool

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

cbielins@gmail.com - 05 Dec 2005 19:40 GMT

I'm not sure if DB2 has a tool specifically for this, but I was
wondering if there's a revoke tool that will completely wipe a
group/user off the database without having to revoke from the table
level, index level, etc

Thanks,
Chris

Reply to this Message

Ian - 05 Dec 2005 20:15 GMT

> I'm not sure if DB2 has a tool specifically for this, but I was
> wondering if there's a revoke tool that will completely wipe a
> group/user off the database without having to revoke from the table
> level, index level, etc

In Control Center, if you find the User or Group you want and select
"Remove" this should revoke all privileges.

Reply to this Message

cbielins@gmail.com - 05 Dec 2005 20:21 GMT

Ahh yes the lovely Control Center, is there any way to do it from the
CLP?

Reply to this Message

Serge Rielau - 06 Dec 2005 04:48 GMT

> Ahh yes the lovely Control Center, is there any way to do it from the
> CLP?

Sure it's called CREATE PROCEDURE and CALL :-)
Should be less than 20 lines of SQL PL.
And takes about 1min to post teh script for subsequent generations in
this group :-)

Cheers
Serge

Signature

Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Reply to this Message

Gert van der Kooij - 06 Dec 2005 10:12 GMT

> > Ahh yes the lovely Control Center, is there any way to do it from the
> > CLP?
[quoted text clipped - 6 lines]
> Cheers
> Serge

With the following SYSCAT tables more then 20 lines might be needed :)

COLAUTH
DBAUTH
INDEXAUTH
LIBRARYAUTH
PACKAGEAUTH
PASSTHRUAUTH
ROUTINEAUTH
SCHEMAAUTH
SEQUENCEAUTH
TABAUTH
TBSPACEAUTH
XMLOBJECTAUTH

Reply to this Message

Knut Stolze - 06 Dec 2005 11:19 GMT

>> > Ahh yes the lovely Control Center, is there any way to do it from the
>> > CLP?
[quoted text clipped - 21 lines]
> TBSPACEAUTH
> XMLOBJECTAUTH

I'd think that you're tempting us to actually verify this. ;-)

But given that you can write a single SELECT statement that covers all the
tables (using UNION), and given that a SELECT can be written in a single
line, I get about 5 to 10 lines that are needed in total.

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

Gert van der Kooij - 06 Dec 2005 11:25 GMT

> >> > Ahh yes the lovely Control Center, is there any way to do it from the
> >> > CLP?
[quoted text clipped - 23 lines]
>
> I'd think that you're tempting us to actually verify this. ;-)

That's right :)

> But given that you can write a single SELECT statement that covers all the
> tables (using UNION), and given that a SELECT can be written in a single
> line, I get about 5 to 10 lines that are needed in total.

You also need different revoke statements, but maybe they can be created
in the select statement already. I will give it a try at home tonight
(after watching soccer).

Reply to this Message

Serge Rielau - 06 Dec 2005 12:19 GMT

>>>>>Ahh yes the lovely Control Center, is there any way to do it from the
>>>>>CLP?
[quoted text clipped - 33 lines]
> in the select statement already. I will give it a try at home tonight
> (after watching soccer).

That would be Schalke vs Milan?
Knut is right with the UNION (ALL). But I do grant you one line per arm.
An a CASE expression should be able to nicely figure out the rest.

Cheers
Serge

Signature

Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Reply to this Message

Gert van der Kooij - 06 Dec 2005 12:29 GMT

> >>>>>Ahh yes the lovely Control Center, is there any way to do it from the
> >>>>>CLP?
[quoted text clipped - 34 lines]
> > (after watching soccer).
> That would be Schalke vs Milan?

PSV vs Fenerbahce

> Knut is right with the UNION (ALL). But I do grant you one line per arm.

Thank you very much :)

Reply to this Message