Re: need help on create view as select ...
- From: Bradd Piontek <piontekdd@xxxxxxxxx>
- To: dba1 mcc <mccdba1@xxxxxxxxx>
- Date: Mon, 23 Nov 2009 19:29:54 -0600
Being able to 'select' from a table doesn't tell me whether or not the user
has that privilege via a role or table grant.
as userA
SELECT * from user_tab_privs where table_name = 'Table1'.
What you likely need to do as SYSTEM is
'GRANT select ON userb.table1 TO usera'; (that was the 2nd bullet point in
my previous email).
Bradd Piontek
"Next to doing a good job yourself,
the greatest joy is in having someone
else do a first-class job under your
direction."
-- William Feather
On Mon, Nov 23, 2009 at 7:27 PM, dba1 mcc <mccdba1@xxxxxxxxx> wrote:
> to answer your questions:
>
> 1. I did log in as "system" and "grant create view to userA;"
>
> 2. if I login as "userA", I can run "select * from userB.XXX;" without
> problem.
>
> any ideal??
>
> --- On Mon, 11/23/09, Bradd Piontek <piontekdd@xxxxxxxxx> wrote:
>
> > From: Bradd Piontek <piontekdd@xxxxxxxxx>
> > Subject: Re: need help on create view as select ...
> > To: mccdba1@xxxxxxxxx
> > Cc: oracle-l@xxxxxxxxxxxxx
> > Date: Monday, November 23, 2009, 8:22 PM
> > I can think of two
> > possibilities.userA has not been granted the
> > 'create view' privelege .userA does not
> > have direct 'select' privileges on userB.table1
> > (Grants through a role won't be used via PL/SQL or
> > Views)
> >
> > Furthermore, if userA is going to grant privileges on
> > the view, it will need the 'with grant option' on
> > the SELECT to userb.Table1.
> > Bradd Piontek
> > "Next to doing a good job yourself,
> > the greatest joy is in having someone
> >
> >
> > else do a first-class job under your
> > direction."
> > -- William Feather
> >
> >
> >
> > On Mon, Nov 23, 2009 at 6:52 PM,
> > dba1 mcc <mccdba1@xxxxxxxxx>
> > wrote:
> >
> >
> > We are login as "userA" and tried to issue
> > following SQL command and failed.
> >
> >
> >
> > create view table1 as select * from userb.table1;
> >
> >
> >
> > ERROR at line 1:
> >
> > ORA-01031: insufficient privileges
> >
> >
> >
> > "userA" can run "select * from
> > userb.table1;" without problem. I even grant
> > "dba" right to "userA". this
> > "create View ..." still failed.
> >
> >
> >
> > any ideal?
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > --
> >
> > //www.freelists.org/webpage/oracle-l
> >
> >
> >
> >
> >
> >
> >
> >
>
>
>
>
>
Other related posts:
