Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: privilege create session problem

Re: privilege create session problem

From: <fitzjarrell_at_cox.net>
Date: 4 Apr 2005 14:39:43 -0700
Message-ID: <1112650783.681103.113470@g14g2000cwa.googlegroups.com>


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 Received on Mon Apr 04 2005 - 16:39:43 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US