Grant all Object Privilege from User to User

Grant all Object Privilege from User to User

Post by JK Y » Tue, 24 Jul 2001 11:14:39



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?

2. Then I loggin as SYS in normal mode.  When I "create user like", I
cannot create that user with all same privilege.  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

 
 
 

Grant all Object Privilege from User to User

Post by Yaroslav Perventse » Tue, 24 Jul 2001 15:23:55


Hello!



Quote:> 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.

Quote:

> 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.

Quote:>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


 
 
 

Grant all Object Privilege from User to User

Post by JK Y » Wed, 25 Jul 2001 17:45:39


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.  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 (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

 
 
 

Grant all Object Privilege from User to User

Post by Yaroslav Perventse » Wed, 25 Jul 2001 18:15:25


Hello!



Quote:> 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
(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

 
 
 

Grant all Object Privilege from User to User

Post by JK Y » Fri, 27 Jul 2001 14:31:13


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  


> 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
>  (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

 
 
 

Grant all Object Privilege from User to User

Post by Yaroslav Perventse » Fri, 27 Jul 2001 17:09:38


Hello!

If you login as sys, you cat grant only system privilege.
If you want grant object privelege, you must login as owner of objects or
user with privilege on needed object "with grant option".

Yaroslav.



> 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




> > 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
> >  (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

 
 
 

Grant all Object Privilege from User to User

Post by Sybrand Bakk » Fri, 27 Jul 2001 20:20:38


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


> 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  


> > 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
>  (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