Re: privileg problem

From: Andreas Mosmann <mosmann_at_expires-31-08-2011.news-group.org>
Date: Sun, 21 Aug 2011 13:45:06 +0200
Message-ID: <1313927106.45_at_user.newsoffice.de>

Mark D Powell schrieb am 19.08.2011 in
<97e143a4-5c07-4046-bd11-9f630cdec483_at_l8g2000prd.googlegroups.com>:

> On Aug 19, 4:21 am, Andreas Mosmann <mosm..._at_expires-31-08-2011.news-
> group.org> wrote:

>> Carlos schrieb am 19.08.2011 in
>> <71848b9e-ef97-4b22-bf03-8dc917764..._at_fv14g2000vbb.googlegroups.com>:
>>
>>
>>
>>
>>

>>> On Aug 18, 11:02 pm, Andreas Mosmann <mosm..._at_expires-31-08-2011.news-
>>> group.org> wrote:
>>>> Hi,

>>

>>>> I have a problem with privileges.

>>

>>>> I can *select* rows from a user

>>

>>>> select   BlaBla, BlaBlub
>>>> from  OtherUser.Table

>>

>>>> I can *not* *create* *a* *view* from the same select
>>>> (ORA-01031: Unzureichende Berechtigungen)

>>

>>>> create view MyBlaBla as
>>>> select   BlaBla, BlaBlub
>>>> from  OtherUser.Table

>>

>>>> I can *create* *a* *table* from the same select
>>>> create table MyBlaBla as
>>>> select   BlaBla, BlaBlub
>>>> from  OtherUser.Table

>>

>>>> I can create views, tables and so on on any selects to tables of other
>>>> users on remote databases, but not on the same db (no prob to select,
>>>> but to create view)

>>

>>>> I have the privilegs
>>>> "SELECT ANY TABLE", "CREATE ANY VIEW"
>>>> Why I can not create the view? What privileg is needed for this? Do I
>>>> really have to use object privilegs?

>>

>>>> Thanks in advance
>>>> Andreas

>>

>>>> --
>>>> wenn email, dann AndreasMosmann <bei> web <punkt> de
>>> GRANT SELECT ON *** TO *** WITH GRANT OPTION;

>>
>> this was the right hint. Without Grant Option I can not create a view,
>> even if I can execute the select- statement itself.
>> I granted "select any table with grant option" and it works fine.
>>

>>> HTH.

>>
>> yes, it did :)
>>

>>> btw:
>>>>> " I can *select* rows from a user"
>>> This does not sound like Oracle terminology... ;-)

>>
>> what is the oracle terminology in this case?
>>

>>> Cheers.
>>> Carlos.

>>
>> Thank you
>> Andreas
>>
>> --
>> wenn email, dann AndreasMosmann <bei> web <punkt> de- Hide quoted text -
>>
>> - Show quoted text -

> What Carlos did not explain is that if the owner of a view references
> any other username's object then he or she needs to be granted access
> on the object with the grant option much like a package owner needs a
> direct grant on third-party objects.  This is a security feature.

> You took care of the problem by granting the onwer select privilege on
> ANY table with the grant option.  As a general rule you should try to
> avoid granting ANY privileges.  The most basic rule of security is
> that a user should not have any access that the user does not need to
> perform their job function.  Many of the rdbms security issues have

> revolved around the ANY privileges. Some auditors will look for and > question ANY privileges.

> Just something to think about.

> HTH -- Mark D Powell --
Thank you very much. But security is not my problem in this case. There are about 10 Datasources (some Excel- Sheets, some Oracle- DB, DBF- Files, Access- DBs ...) that I have to combine. In all that databases I have the right to collect the data. I decided to use a local Oracle- DB to work with the data and collect by DB- links or copied DBF-, Excel- and other data as tables into the local DB. Inside this are more users/schemas only for that purpose. And for not having too much work I granted "select any with grant option". This is only my very private db. (that means, if I am ready the result is given to the customer of course)
Andreas

-- 
wenn email, dann AndreasMosmann <bei> web <punkt> de

Received on Sun Aug 21 2011 - 06:45:06 CDT