Re: DBA user, and rights on another user's schema

You can't do it in an easy way, but you can get around the problem by exploiting the following loopholes:

1. A dba can create a procedure in another schema;
2. Dynamic SQL can be used within a procedure to grant privileges on tables;
3. Procedures run as the owning schema, not the user calling them;
4. A DBA can execute a procedure in any schema.

I have done this a few times, but it is not polite to grant access to other schemas without their knowing, so rather try to get user1 to do the grants or grant you admin option on their tables.

I use this procedure within a database refresh script to grant access to the SYS objects without having to connect to the database as SYS.

Regards
Andrew

"Vik" <viklall_at_my-deja.com> wrote in message news:8l7i1f$2a9$1_at_nnrp1.deja.com...
> I have three users in my database. A DBA account which I want to
> have all possible privileges, and two users that the DBA account
> creates.
>
> User 1 goes on to create some tables and indexes.
>
> I want (as DBA) to be able to grant access to User 2, to be able
> to read User 1's tables via select rights (and some synonyms).
>
> I can grant these as User 1, over to User 2.
>
> Shouldnt I also be able to do this as the DBA?
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Fri Jul 21 2000 - 00:00:00 CDT