Re: Grant select on another owners table

Bryan Grenn wrote:
>
> Problem : I'm creating a new user and I was asked to grant that user
> select access on:
> xxxxxx.tablea
> yyyyyy.tableb and
> zzzzzz.tablec.
>
> I have the password for account xxxxxx, but the users have
> developed tables under yyyyyy and zzzzzz that they support.
> How do I grant access to these tables without involving the
> owners of yyyyyy and zzzzzz ? I know I can change the password
> for these accounts, do it and change them back, but I don't want
> to affect the user if they happen to try to login at the same
> time. Is there a way ?????

In theory not even a DBA can grant access to another user's objects unless that user has given the DBA grants on its objects with grant option. - However this is just not true because (amongst other things) imp will create grants during an import of YYYYYY's account by a DBA with grants=y. This is done using a 'secret' version of the alter user command...

• the plot thickens ***

Follow these 3 steps:

1. First fetch the encrypted password for the user YYYYYY from sys.user$.password. It will look something like this: 'B182D427ED9C682B'
2. Then change the password to barney and log on as YYYYYY/barney
3. then *immediately* change the password back to the original using this command:

SQL> alter user YYYYYY identified by values 'B182D427ED9C682B';

This can all be done in one simple SQL script which would look a *bit* like this:

conn system/manager

col crypt new_value cryptval

select password crypt from sys.user$
where username = 'YYYYYY'
/

alter user yyyyyy identified by barney

conn yyyyyy/barney

alter user barney identified by values '&cryptval';

Please note - I just made this script up and haven't actually run it anywhere!!!

Now you are logged in as YYYYYY - but you have only changed his/her password for a split second!

This also allows you to become any user without even knowing their password.

• Only DBA's can do this of course!

It's a good idea to perform all grants back to the DBA with grant option while you're in there - so in future you grant directly as the DBA.

Hope this helps...

:-) TBL.   Received on Wed Apr 17 1996 - 00:00:00 CEST