Re: privileg problem
From: Carlos <miotromailcarlos_at_netscape.net>
Date: Fri, 19 Aug 2011 10:15:50 -0700 (PDT)
Message-ID: <f2b8ae39-363a-4052-896c-05be717986e5_at_fv14g2000vbb.googlegroups.com>
On 19 ago, 15:12, Mark D Powell <Mark.Powe..._at_hp.com> wrote:
> 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 --
Date: Fri, 19 Aug 2011 10:15:50 -0700 (PDT)
Message-ID: <f2b8ae39-363a-4052-896c-05be717986e5_at_fv14g2000vbb.googlegroups.com>
On 19 ago, 15:12, Mark D Powell <Mark.Powe..._at_hp.com> wrote:
> 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 --
Carlos did not explain because it was NOT an explanation but a hint that the op should investigate further. (The op could find an explanation here: http://carlosal.wordpress.com/2007/02/12/grants-ora-01720-y-views/ -in spanish though- ).
>"As a general rule you should try to avoid granting ANY privileges."
I couldn't agree more.
>"what is the oracle terminology in this case?"
I would use 'schema' in Oracle, and 'database' in Teradata ;-)
Cheers.
Carlos. Received on Fri Aug 19 2011 - 12:15:50 CDT