Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!g14g2000cwa.googlegroups.com!not-for-mail
From: fitzjarrell@cox.net
Newsgroups: comp.databases.oracle.server
Subject: Re: privilege create session problem
Date: 4 Apr 2005 14:39:43 -0700
Organization: http://groups.google.com
Lines: 65
Message-ID: <1112650783.681103.113470@g14g2000cwa.googlegroups.com>
References: <424f1957.0504041220.373affa4@posting.google.com>
NNTP-Posting-Host: 68.95.47.225
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Trace: posting.google.com 1112650788 2129 127.0.0.1 (4 Apr 2005 21:39:48 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Mon, 4 Apr 2005 21:39:48 +0000 (UTC)
In-Reply-To: <424f1957.0504041220.373affa4@posting.google.com>
User-Agent: G2/0.2
Complaints-To: groups-abuse@google.com
Injection-Info: g14g2000cwa.googlegroups.com; posting-host=68.95.47.225;
   posting-account=B_KC8Q0AAADcSTVy4DZ59utFaLrVLOo4
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:239618

Comments embedded.

Ariel Mendieta wrote:
> Hi
>
> I have a following problem
>
> 1. i have a 101 tables created in a regtec schema
> 2. i create public synonym for all tables in regtec's schema
> 3. i created an new user xyz, and i give it create session and select
> privilege on all public synonyms of the regtec's schema

That is just fine.  However by doing so you also grant select on the
underlying object, else you couldn't retrieve any data.

> 4. when the xyz user log in at the database between sqlnavigator,
this
> user can see all the regtec's tables and he can do an extract DDL of
> the all the tables, including views, synonyms, etc...so it can be a
> security problem,
>

Granting select on another users table also gives the user the ability
to describe the table in question, thus indirectly providing access to
the 'DDL', as you put it.  How would you select from a table not
knowing what columns are present?  Outside of 'select *' you can't, and
even then you can get a good idea of the datatypes for a table.  You've
granted access to the table and its data to a user other than the
owner.  Certainly that can be a security issue, but how else will this
user be able to function, not being able to see the table structure?

> how can i do if i want to anybody cannot perform an extract ddl
>

Anyone who can SELECT from a table can DESCRIBE the table, as I
mentioned previously.  That being said, HOW can you prevent anyone from
spooling to a file such output through SQL*Plus?  It appears  you'll
need to write your own 'secure' application to access this read-only
data so the end-user can't get access to SQL*Plus.  This essentially
means you'll be re-writing SQL*Plus without some of its functionality,
doesn't it?  Is that a worthwhile expenditure of your time and effort?

> do you know if this is a security bug.. just i give the user create
> session and select on the public synonyms
>

People who don't read the manual to understand how the software
functions consider all things not to their liking 'bugs'.  I would
strongly suggest you visit

tahiti.oracle.com

and begin reading, starting with the 'Concepts Manual'.  You REALLY
need to understand the product with which you're trying to work.

> i'm using a Oracle9i Enterprise Edition Release 9.2.0.4.0
>

That's great.  Those manuals are available at the address listed above.
 
> thank you for your suggestions


David Fitzjarrell