Re: SYS not able to GRANT -- Strange !

From: Darrell Landrum <dlandrum_at_zalecorp.com>
Date: Fri, 13 Jun 2003 05:48:07 -0700
Message-ID: <F001.005B11FC.20030613043937@fatcity.com>

Try granting to sys with grant option....

SQL> create user t1 identified by t1 quota unlimited on users;

User created.

SQL> create user t2 identified by t2;

User created.

SQL> grant connect to t1;

Grant succeeded.

SQL> grant connect to t2;

Grant succeeded.

SQL>
SQL> create table t1.firsttab(col1 varchar2(10)) tablespace users;

Table created.

SQL> insert into t1.firsttab values('somedata');

1 row created.

SQL>
SQL> connect t1/t1;
Connected.
SQL> GRANT SELECT ON T1.FIRSTTAB TO SYS WITH GRANT OPTION; Grant succeeded.

SQL>
SQL> connect sys/password;
Connected.
SQL>
SQL> grant select on t1.firsttab to t2;

Grant succeeded.

SQL> connect t2/t2
Connected.
SQL> select * from t1.firsttab;

COL1

somedata

1 row selected.

>>> [EMAIL PROTECTED] 06/13/03 05:20AM >>>
No user, not even SYS, can grant on another user's objects.

You have two options

Login as the owner of the object on which grants are to be provided and provide the grants
Create a grants package[which can give grants on specified objects] in the owner's schema, grant execute on that package to SYS or whichever DBA account you use and then use the package from SYS or the DBA account. The package runs in Owner's Rights !

Hemant

Prem Khanna J <[EMAIL PROTECTED]> wrote:

> Guys,
>
> CONNECT SYS AS SYSDBA;
>
> create user testuser1 identified by testuser1 ;
> grant connect, resource to testuser1;
>
> create user testuser2 identified by testuser2 ;
> grant create session to testuser2;
>
> create table testuser1.table1 ( a int ) ;
>
> grant select on testuser1.table1 to testuser2;
> error at line 1:
> ora-01031: insufficient privileges
>
> WHERE AS :
>
> connect testuser1/testuser1;
>
> grant select on testuser1.table1 to testuser2;
>
> grant succeeded.
>
>
> why is it so ?
> why sys is not able to GRANT ?
> seems to be strange !
>
> the env. is 8.1.6.0./win2k.
>
> Jp.
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Prem Khanna J
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting
> services
>

> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like
> subscribing).
>

Hemant K Chitale
http://hkchital.tripod.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net 
-- 
Author: Hemant K Chitale
  INET: [EMAIL PROTECTED] 

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com 
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Darrell Landrum
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Fri Jun 13 2003 - 07:48:07 CDT