RE: Privileges for other's table

From: Hand, Michael T <HANDM_at_polaroid.com>
Date: Tue, 10 Sep 2002 09:12:49 -0400
Message-Id: <22528.293299@fatcity.com>

zhu chao,
Thanks for checking this out. I tried it again this morning and it = worked
like your example. At first I thought there might be a difference when creating a view on all rows (*) versus creating the view on a subset = (x, y,
z) but under both conditions a view was created successfully. Of = course,
attempting to grant select on the view failed with a ORA-1720. May be = this
is an hidden feature of 8.1.7.3 on Tru64. Unfortunately, I still have = the
screen dumps so I can't tell my boss that I'm going crazy and need a extended vacation ;)

Mike

-----Original Message-----
Hand, Michael T,

        hi, what you tested is not the normal behavior of oracle, neither the document said like that nor in my database behavior like that.Maybe something is wrong with your database:)
=09

        Look:
SQL> conn internal
Connected.
SQL> create user t identified by t;
User created.
SQL> grant connect to t;
 Grant succeeded.
SQL> conn t/t
Connected.
SQL> select count(*) from sys.obj$;
select count(*) from sys.obj$

                        *

ERROR at line 1:
ORA-00942: table or view does not exist

 SQL> conn internal
Connected.
SQL> grant select on obj$ to t;
Grant succeeded.
SQL> conn t/t
Connected.
SQL> select count(*) from sys.obj$;

  COUNT(*)

---

     26011

SQL> create or replace view sysobj as select * from sys.obj$; View created.

        =09
SQL> select * from v$version;

BANNER

---

Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production PL/SQL Release 8.1.7.0.0 - Production
CORE 8.1.7.0.0 Production
TNS for Linux: Version 8.1.7.0.0 - Development NLSRTL Version 3.4.1.0.0 - Production

Regards
zhu chao
Eachnet DBA
86-21-32174588-667
chao_ping_at_vip.163.com
www.happyit.net

=3D=3D=3D=3D=3D=3D=3D 2002-09-09 16:23:00 ,you = wrote=A3=BA=3D=3D=3D=3D=3D=3D=3D

>Ok, I haven't had to deal with privileges much lately but this one had =
be
>stumped for a while, V8.1.7 Tru64.
>
>This is from an account with minimal privileges:
>
>SQL> select count(*) from sys.obj$;
>select count(*) from sys.obj$
> *
>ERROR at line 1:
>ORA-00942: table or view does not exist
>
>SQL> connect sys
>Enter password:=20
>Connected.
>SQL> grant select on obj$ to utility;
>
>Grant succeeded.
>
>SQL> connect utility/xxxx
>Connected.
>
>SQL> select count(*) from sys.obj$;
>
> COUNT(*)
>----------
> 37742
>
>SQL> create view o_by_vlo as select name, obj# from sys.obj$;
>create view o_by_vlo as select name, obj# from sys.obj$
> *
>ERROR at line 1:
>ORA-00942: table or view does not exist
>
>SQL> connect sys
>Enter password:=20
>Connected.
>SQL> grant select on obj$ to utility with grant option;
>
>Grant succeeded.
>
>SQL> connect utility/xxxx
>Connected.
>SQL> create view o_by_vlo as select name, obj# from sys.obj$;
>
>View created.
>. . .
>So it would seem that with grant option is needed to create a view on
>another schema's tables. It took a little thinking on my part to =
realize
>that this makes sense as the view owner would be able to grant =
privileges
on
>the underlying tables. Incidentally, I also found out that you can't =
use
>the grant option within roles. I wonder why?
>
>Michael Hand
>Polaroid Corp
>
>
>--=20
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--=20
>Author: Hand, Michael T
> INET: HANDM_at_polaroid.com
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (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).

=3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =
=3D =3D
=09

--=20
Please see the official ORACLE-L FAQ: http://www.orafaq.com --=20
Author: zhu chao
  INET: chaospku_at_163.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L Received on Tue Sep 10 2002 - 08:12:49 CDT