Re: Grant all Object Privilege from User to User

This behavior conforms to the SQL standard. If the object owner didn't grant the objects WITH GRANT OPTION then even SYS can not do this.

There are two ways around this

1
spool <anyfilename>
select password
from dba_users
where username='<owner>'
/
alter user <owner> identified by <anypassword> connect <owner>/<anypassword>
... do whatever you need to do
connect sys/<syspassword>
rem restore the password, getting it from the file above alter user <owner> identified by values '<hexstring in file>' 2
create a small procedure in user owner, to perform dynamic sql. The answer to this is version dependent. In 7.3 and 8.0 use dbms_sql, in 8i and 9i execute immediate. The only thing the procedure needs to do is execute a sqlstring.

Now
begin
<owner>.do_ddl('grant ... ');
end;

This should also resolve it.

Hth,

Sybrand Bakker, Senior Oracle DBA

jkyf0131_at_hotmail.com (JK Yao) wrote in message news:<93f65984.0107252131.6a606aec_at_posting.google.com>...
> If I login as sys, I cannot grant privilege of all userA's object to
> userB. If you know how to do so, please let me know. Thanks a lot.
>
> regards,
> JK
>
>
> "Yaroslav Perventsev" <p_yaroslav_at_cnt.ru> wrote in message news:<9jje0c$k6v$1_at_news247.cnt.ru>...
> > Hello!
> >
> > JK Yao <jkyf0131_at_hotmail.com> ñîîáùèë â íîâîñòÿõ
> > ñëåäóþùåå:93f65984.0107240045.69176b9c_at_posting.google.com...
> > > For example, I want to create USER1 which has the same roles and
> > > privileges with USER2. However, the USER2 has the privilege to access
> > > OBJECT3 which create by USER3.
 

> > > So, when I create USER1 like USER2, the system shows that I don't have
> > sufficient privilege.
> >
> > What is it mean user1 like user2?
> > Which system???
> > May be you use bad tools?
> >
> > If you login as sys, you can create any user.
> >
> > May be I something don't understand?
> >
> > Yaroslav.
> >
> >
> >
> > > Does that
> > > because I don't have grant privilege for OBJECT3 from USER3?
> > >
> > > If I want to grant all object privilege from USER3, how to do so? Or
> > > has any way to do?
> > >
> > > Thanks in advance,
> > >
> > > regards,
> > > JK
> > >
> > >
> > > "Yaroslav Perventsev" <p_yaroslav_at_cnt.ru> wrote in message
 news:<9jgfiq$e4n$1_at_news247.cnt.ru>...
> > > > Hello!
> > > >
> > > > JK Yao <jkyf0131_at_hotmail.com> ñîîáùèë â íîâîñòÿõ
> > > > ñëåäóþùåå:93f65984.0107221814.36ef2978_at_posting.google.com...
> > > > > dear all,
> > > > >
> > > > > Anyone knows how to grant all object privilege to another user?
> > > > > 1. I loggin as SYS with sysdba, but got wrong username/password. How
> > > > > to solve it?
> > > > If you whant logon as sysdba, you must create password file using orapwd
 (on
> > > > Unix) utility.
> > > > After that you must add to init<SID>.ora ->
> > > > "remote_login_passwordfile=exclusive" and restart Oracle.
> > > > After that you may using password, created by orapwd.
> > > >
> > > > >
> > > > > 2. Then I loggin as SYS in normal mode. When I "create user like", I
> > > > > cannot create that user with all same privilege.
> > > > Which privilege?
> > > >
> > > >
> > > >
> > > > Best regards!
> > > > Yaroslav.
> > > > >I think the problems
> > > > > is that I don't have the grant privilege for those object with user
> > > > > schema. Can anyone help?
> > > > >
> > > > > Thanks in advance,
> > > > >
> > > > > regards,
> > > > > JK
Received on Thu Jul 26 2001 - 06:20:38 CDT