Re: Granting table privileges. Possibly Jonathan Lewis can advise here?

I haven't looked at BECOME USER for a long time - the last time was at best v7, maybe even v6.

However, the thing I noticed about it then was the when exp ran the SQL to set
its schema, the recursive SQL this caused was not the same as when a normal SQL*Plus session tried the same thing.

The BECOME USER privilege on its own does nothing unless you have access to some OCI function calls. I guess that basically it is a more powerful variant of the now publicly available PROXY USER option:

    alter user end_user1
    grant connect through app_server_id;

This SQL command appeared in 8.1, and allowed app_server_id to connect to the database, then start a session as end_user1 without knowing that user's password. But this is only implemented through OCI in 8.1 (I believe it can be done in Java in 9, but haven't checked that out yet).

Set current_schema only affects name resolution - it means that if you are using ABC, and use set current_schema to DEF, then do:

    select * from t1;
Oracle will look for DEF.T1 before it looks for anything else, rather than looking for ABC.T1. It doesn't give you any privileges you wouldn't otherwise have.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

____UK_______April 22nd
____Denmark__May 21-23rd
____USA_(FL)_May 2nd

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK_(Manchester)_May
____Estonia___June (provisional)
____Australia_June (provisional)
____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Paul Brewer" <paul_at_paul.brewers.org.uk> wrote in message
news:3e94aa5a_3_at_mk-nntp-1.news.uk.worldonline.com...


> "Chuck" <chuckh_at_softhome.net> wrote in message

> news:Xns935782584BA20chuckhsofthomenet_at_130.133.1.4...

> > Is there a way to grant privileges on a table where the owner has

not


> > granted you any privileges? IMP seems to have that ability and I

am trying


> > to duplicate that functionality. I have the "become user" system

privilege


> > but have not been able to find any SQL or package that will let me

do what


> > I want.

>

> Chuck,

>

> My answer differs from most others (hence the changed heading;

Jonathan is


> an accepted authority). I suspect, however, that Howard knows but

isn't


> telling, and I'm not saying he's wrong with that approach.

>

> Yes. IMP does exactly that, and has done so since 7.3 (or earlier)

if my


> understanding is correct.

>

> Folks; By all means correct me if I'm wrong, but since a *long*

while back,


> with the 'BECOME USER' privilege, one has been able to issue 'alter

session


> set current_schema = nnnnnn' and grant object privileges as

necessary.


>

> And I know it's not documented or supported, but if IMP uses it (and

I have


> expressed this opinion since 1997), I think we are safe for a while

yet. In


> any case, as Howard points out, with 9.2 we no longer need it.

>

> Regards,

> Paul

>

>

>