create new user

Hi,
I've create a new user: (oracle 8.1.7.0 O.S. win 2000)
connect system/manager@dboracle

create user ora
identified by tree
default tablespace
temporary tablespace temp;

grant connect, resource to ora;

grant select any table to ora;

grant select_catalog_role to ora;

grant create public synonym to ora;

Now how Can user ora see all objects (view, table, procedure, package.....) of user ora1??

regards
Raf

look at the grants which you have granted to ora..

Code:

---


SQL*Plus: Release 8.1.6.0.0 - Production on Thu Nov 7 17:15:37 2002

(c) Copyright 1999 Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production

appdev@NICK>create user nosey identified by nosey;

User created.

appdev@NICK>grant create session to nosey;

Grant succeeded.

appdev@NICK>@conn nosey/nosey
Input truncated to 15 characters
nosey@NICK>select * from scott.emp;
select * from scott.emp
                    *
ERROR at line 1:
ORA-00942: table or view does not exist
/* well it does he just can't see it! */


nosey@NICK>@conn appdev
Enter password:
Input truncated to 15 characters
appdev@NICK>grant select any table to nosey;

Grant succeeded.

appdev@NICK>@conn nosey/nosey
Input truncated to 15 characters
nosey@NICK>desc scott.emp
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 EMPNO                                                NOT NULL NUMBER(4)
 ENAME                                                          VARCHAR2(10)
 JOB                                                            VARCHAR2(9)
 MGR                                                            NUMBER(4)
 HIREDATE                                                      DATE
 SAL                                                            NUMBER(7,2)
 COMM                                                          NUMBER(7,2)
 DEPTNO                                                        NUMBER(2)

nosey@NICK>@conn appdev
Enter password:
Input truncated to 15 characters


appdev@NICK>drop user nosey;

User dropped.

appdev@NICK>

---

Tarry, you've missed the point of the question (IMHO). Sure, "GRANT SELECT ANY TABLE" gives him the ability to see any table, but the question was:

Quote:

---

Now how Can user ora see all objects (view, table, procedure, package.....)

---

So the main poit here is on other type of objects, not just tables!

Well, the answer is: Raf, you are wrong. He can't realy use those non-table objects of other users, at least not all type of them. I mean he can't execute their procedures, functions, packages, he can't use their sequences... He can select from their tables and views, he can also use their private synnonym, though - GRANT SELECT ANY TABLE gives him that power.

But if by "how Can user ora see all objects " you mean how can he see all those objects from database dictionary, well, in that case the answer is pretty simple - GRANT_SELECT_CATALOG role enables him to do that, so nothing strange here.

Jurij,
You're my hero & You're very right there.
I am aware of that catalog role I was merely diplaying the any table grant thing.

Darn, Right now I'm deleting some 50 garbage spam, I got one also from Jeff, and lot of a naresh(dunno who this retard is) with lots of virus's. I better edit that post or else it'll keep me busy all day fighting spam.