Hi,
I do a mistake. 
The all_sdo_geom_metadata is visible from all the users.
the user I use to test this has grants to connect-role e resource-role (but not admin-role).
I don' t know if a connect-only granted user can see the all_sdo_geom...
Hovewer I remenber wrong !
So I apologize for the wrong suggestion.
And is correct to use the all_sdo_geom... 
When was queryed on all_sdo_geom_metadata.
The user see all the tables with spatial data where it has grant to see.
when query on user_sdo.. it see only the table where it is owner.
Regards,
Andrea Peri.
------------------------------
Message: 4
Date: Wed, 02 Apr 2008 23:36:53 +0200
From: "andrea.peri@anonymised.com"
<andrea.peri@anonymised.com>
Subject: Re: [Geoserver-users] wms with oracle datastore didn't serve,
anything
To: geoserver-users@lists.sourceforge.net
Message-ID: <47F3FC75.9050804@anonymised.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowedHi,
The mdsys.all_sdo_geom_metadata is a special view of the mdsys schema
that is visible only to a user with special rights (dba for ex.).If the user connect is not administrator , it would use the
mdsys.user_sdo_geom_metadata.
that is a view filtered on the actual user connected. To see only the
records about the tables owned by the user.regards,
Andrea Peri.
geoserver-users-request@lists.sourceforge.net ha scritto:
>> ...
>> 01 Apr 13:50:42 DEBUG [data.oracle] - the sql statement for srid is
>> SELECT SRID FROM MDSYS.ALL_SDO_GEOM_METADATA WHERE TABLE_NAME='FFH_SDO'
>> AND COLUMN_NAME='SHAPE'
>>
>> ...
>> But the schema MDSYS doesn't contain a table ALL_SDO_GEOM_METADATA. So
>> the sql request doesnt work. therefore the next entry is:
>>
>
> Wait, how is this possible? That table is a standard one and everybody
> should be able to see it?
> Moreover:
>
> ...
>
Sigh. The OracleDataStore once used to access user_sdo_geom_metadata
and many users complained because they were using users without
schema access credentials and thus the user_sdo_geom_metadata
table was not visible to them, and asked the
ALL_SDO_GEOM_METADATA to be used instead:
http://jira.codehaus.org/browse/GEOT-1441
http://jira.codehaus.org/browse/GEOT-319
http://jira.codehaus.org/browse/GEOS-1592
http://jira.codehaus.org/browse/GEOS-1302I only fire up Oracle to fix some random bug, but given that
there is no real Oracle maintainer I trusted the various
users and switched the query to use all_sdo_geom_metadata.Cheers
Andrea