Re: privileg problem
Date: Fri, 19 Aug 2011 06:12:40 -0700 (PDT)
Message-ID: <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 -- Received on Fri Aug 19 2011 - 08:12:40 CDT