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
alter user <owner> identified by <anypassword>
... do whatever you need to do
rem restore the password, getting it from the file above
alter user <owner> identified by values '<hexstring in file>'
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.
<owner>.do_ddl('grant ... ');
This should also resolve it.
Sybrand Bakker, Senior Oracle DBA
> 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.
> > Hello!
> > > 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
> > > > Hello!
> > > > > 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
> > > > 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