Tuesday, May 22, 2012

On ORA-01031: insufficient privileges error received when selecting from a view

I was asked to give an example on how to solve the ORA-01031: insufficient privileges error received by a user who select a view OWNED by another user (view created on a table owned by another user as well).
I mean: user W is the owner of a table (T1); user V has built a view (VIEW_T1) on that table; user R want to query V.VIEW_T1 view... but he receives the ORA-01031: insufficient privileges error.
I think the easy way is to provide some line codes. First of all the following are the schema and objects settings:
CREATE USER W
  IDENTIFIED BY W
  DEFAULT TABLESPACE USERS
  TEMPORARY TABLESPACE TEMP_GROUP_SYS
  PROFILE DEFAULT
  ACCOUNT UNLOCK
/
  -- 1 Role for W 
  GRANT CONNECT TO W
/
  ALTER USER W DEFAULT ROLE ALL
/
  GRANT CREATE TABLE TO W
/
  GRANT CREATE SESSION TO W
/
  ALTER USER W QUOTA 1M ON USERS
  /
CREATE USER V
  IDENTIFIED BY V
  DEFAULT TABLESPACE USERS
  TEMPORARY TABLESPACE TEMP_GROUP_SYS
  PROFILE DEFAULT
  ACCOUNT UNLOCK
/
  -- 1 Role for R 
  GRANT CONNECT TO V
/
  ALTER USER V DEFAULT ROLE ALL
/
  GRANT CREATE TABLE TO V
/
  GRANT CREATE SESSION TO V
/
  ALTER USER V QUOTA 1M ON USERS
/
CREATE USER R
  IDENTIFIED BY R
  DEFAULT TABLESPACE USERS
  TEMPORARY TABLESPACE TEMP_GROUP_SYS
  PROFILE DEFAULT
  ACCOUNT UNLOCK
/
  -- 1 Role for R 
  GRANT CONNECT TO R
/
  ALTER USER R DEFAULT ROLE ALL
/
  GRANT CREATE TABLE TO R
/
  GRANT CREATE SESSION TO R
/
  ALTER USER R QUOTA 1M ON USERS
/
CREATE TABLE W.T1 (A NUMBER)
/
INSERT INTO W.T1 VALUES (1)
/
INSERT INTO W.T1 VALUES (2)
/
INSERT INTO W.T1 VALUES (3)
/
GRANT SELECT ON W.T1 TO V
/
CREATE OR REPLACE VIEW V.T1_VIEW (A_VIEW) AS SELECT A FROM W.T1
/
Now let's try to select those records from all the 3 users, W, V and R respectively and let's see what we get:
user@hostname:~$ sqlplus w/w
SQL> select * from t1;

  A
----------
  1
  2
  3

SQL> set feed on
SQL> /

  A
----------
  1
  2
  3

3 rows selected.
Now connect using the V user...
SQL> conn v/v
Connected.
SQL> select * from t1_view;

    A_VIEW
----------
  1
  2
  3

3 rows selected.

SQL> select * from w.t1;

  A
----------
  1
  2
  3

3 rows selected.
And now using the R user...
SQL> conn r/r
Connected.
SQL> select * from v.t1_view;
select * from v.t1_view
                *
ERROR at line 1:
ORA-00942: table or view does not exist
Those code lines state: 1) V user has the permissions to query his view and, of course, the table on which his view was built on; 2) R user is trying to query an object that seems it doesn't exist on the V schema; that's because R has no permissions to query that object; So let's try to grant the select permission to R user so he can query the view on V schema:
SQL> conn sys/my_pwd
Connected.
SQL> grant select on v.t1_view to r;

Grant succeeded.
Let's try to query again V.T1_VIEW from R user and see what happens:
SQL> conn r/r
Connected.
SQL> select * from v.t1_view;
select * from v.t1_view
                *
ERROR at line 1:
ORA-01031: insufficient privileges
We are getting finally the ORA-01031: insufficient privileges error. Now the question was: Do I need to grant select permission on the W.T1 table directly to R user ? The answer is: no.
SQL> conn sys/my_pwd
Connected.
SQL> grant select on w.t1 to r;

Grant succeeded.

SQL> conn r/r
Connected.
SQL> select * from v.t1_view;
select * from v.t1_view
                *
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> select * from w.t1;

  A
----------
  1
  2
  3

3 rows selected.
What's happening ? We are able to select rows directly from W user, but not from the view owned by V, built and with the right privileges on W.T1 table ?!?! The error is in the grant option granted to V user to select W.T1 table. It should be like the following command:
SQL> conn sys/my_pwd
Connected.
SQL> grant select on w.t1 to v with grant option;

Grant succeeded.
And now...
SQL> conn r/r
Connected.
SQL> select * from v.t1_view;

    A_VIEW
----------
  1
  2
  3

3 rows selected.
Of course even if we revoke the privilege to select directly the W.T1 table from R user, R user is still able to query the view owned by V user.
SQL> conn sys/my_pwd
Connected.
SQL> revoke select on w.t1 from r;

Revoke succeeded.

SQL> conn r/r
Connected.
SQL> select * from v.t1_view;

    A_VIEW
----------
  1
  2
  3

3 rows selected.
That's all

42 comments:

Anonymous said...

dating rituals in mexico http://loveepicentre.com/ dating san francisco

Anonymous said...

We are a gaggle of volunteers and opening a brand new scheme in our
community. Your site offered us with valuable information to work on.
You've performed a formidable job and our entire group will likely be grateful to you.

My site - diets that work fast
Also see my page > Diets That work

Anonymous said...

Now I am going away to do my breakfast, afterward having my breakfast coming yet again to read further news.


Feel free to visit my homepage - thrombosed hemorrhoid treatment

Anonymous said...

buy tramadol online tramadol 100mg usa - online drugstore tramadol

Anonymous said...

generic xanax blue xanax pills generic - xanax dosage yellow pill

Anonymous said...

buy tramadol online tramadol addiction dosage - where to buy tramadol online safely

Anonymous said...

generic xanax xanax side effects yahoo answers - .5 mg xanax effects

Anonymous said...

buy tramadol online tramadol 50 mg once a day - buy tramadol no prescription online

Anonymous said...

xanax online does xanax show up in a pre-employment drug test - buy xanax online reviews

Anonymous said...

buy tramadol online tramadol 50 mg side effects - buy tramadol online no prescription usa

Anonymous said...

buy tramadol online buy tramadol online without rx - buy tramadol online mastercard overnight

Anonymous said...

generic xanax xanax bars order - alprazolam ratiopharm 0 5 mg nebenwirkungen

Anonymous said...

generic xanax xanax dosage 1mg - xanax 2mg pies

Anonymous said...

buy tramadol online tramadol generic for ultracet - can buy tramadol online

Anonymous said...

buy xanax xanax is what drug classification - xanax withdrawal onset

Anonymous said...

xanax alprazolam how long xanax drug screen - order xanax 2mg

Anonymous said...

buy tramadol online tramadol dosage webmd - buy cheap tramadol online

Anonymous said...

tramadol online buy tramadol online no prescription - ultram tramadol usa

Anonymous said...

learn how to buy tramdadol cheap tramadol on line - tramadol hydrochloride online

Anonymous said...

learn how to buy tramdadol tramadol online with cod - tramadol for dogs usa

Anonymous said...

tramadol online tramadol for dogs pain - buy ultram tramadol online

Anonymous said...

http://buytramadolonlinecool.com/#50897 buy cheap tramadol online no prescription - tramadol 50 mg is used to treat

Anonymous said...

http://buytramadolonlinecool.com/#56411 buy tramadol dogs usa - tramadol hcl lethal dose

Anonymous said...

buy tramadol order cheap tramadol-overnight - tramadol high get

Anonymous said...

buy tramadol tramadol no prescription overnight cod delivery - tramadol hcl 37.5 mg

Anonymous said...

buy ativan online combining ativan and alcohol - much needed overdose ativan

Anonymous said...

ways to buy ativan online ativan dosage webmd - ativan dosage

Anonymous said...

discount ativan ativan interdose withdrawal - ativan dose to get high

Anonymous said...

http://ranchodelastortugas.com/#58720 death from xanax and alcohol - how much does generic xanax cost

Anonymous said...

buy tramadol online does tramadol high feel like - generic tramadol 627

Anonymous said...

xanax xanax drug dogs - difference between xanax pills bars

Anonymous said...

http://bayshorechryslerjeep.com/#2902 xanax bars no markings - xanax drug anxiety

Anonymous said...

YĪæur own write-uĻ provides establishŠµd necessary tĪæ me.
It’s extremely infoŠ³mative and you're clearly very well-informed in this region. You have got opened my own eye for you to numerous opinion of this kind of subject matter with interesting and reliable content material.
Also visit my webpage ... viagra

Anonymous said...

What's up to every one, as I am genuinely eager of reading this weblog's post to be updated on a regular
basis. It consists of good information.

My web blog; calories walking calculator

Anonymous said...

I would like to thank you for the efforts you've put in writing this site. I really hope to check out the same high-grade blog posts by you later on as well. In fact, your creative writing abilities has encouraged me to get my very own site now ;)

Also visit my site how to lose weight fast

Anonymous said...

Your repoŠ³t features ŠµstablishŠµd beneficial to us.
It’ѕ Š³eally useful Š°nŌ you really are obviouslу really educatŠµd of
this type. You have gŠ¾t eхĻosed my sight for yĪæu to vŠ°rious views on
thіѕ рartiсular subject matter tŠ¾getheŠ³
with іntriquing, notable anŌ sound content material.



My hĪæmeĻage ... Buy CIALIS Online

Anonymous said...

I blog frequently and I genuinely thank you for your content.
The article has really peaked my interest. I'm going to take a note of your site and keep checking for new details about once a week. I subscribed to your RSS feed as well.

my web page :: website

oakleyses said...

abercrombie and fitch, instyler, ghd, bottega veneta, ugg boots, jimmy choo outlet, soccer shoes, ugg pas cher, herve leger, beats by dre, birkin bag, abercrombie and fitch, north face jackets, soccer jerseys, mont blanc, rolex watches, lululemon outlet, celine handbags, nike roshe run, nike trainers, giuseppe zanotti, hollister, wedding dresses, nike huarache, mcm handbags, vans shoes, chi flat iron, babyliss pro, north face outlet, nike roshe, ugg australia, ugg, marc jacobs, barbour, nfl jerseys, p90x, new balance shoes, asics running shoes, ferragamo shoes, mac cosmetics, insanity workout, uggs outlet, reebok outlet, longchamp, valentino shoes

Unknown said...

thank you. Because you have solved.

Unknown said...

thank you. Because you have solved.

Unknown said...

HTTP://WWW.ATLAS-DAMAM.COM
Ų“Ų±ŁƒŲ© Ł†Ł‚Ł„ Ų§Ų«Ų§Ų« ŲØŲ§Ł„ŲÆŁ…Ų§Ł…
Ų“Ų±ŁƒŲ© Ł†Ł‚Ł„ Ų¹ŁŲ“ ŲØŲ§Ł„ŲÆŁ…Ų§Ł…
Ų“Ų±ŁƒŲ© Ł†Ł‚Ł„ Ų§Ų«Ų§Ų« ŲØŲ§Ł„Ų®ŲØŲ±
Ų“Ų±ŁƒŲ© Ł†Ł‚Ł„ Ų¹ŁŲ“ ŲØŲ§Ł„Ų®ŲØŲ±
Ų“Ų±ŁƒŲ© Ł†Ł‚Ł„ Ų¹ŁŲ“ ŲØŲ§Ł„Ų§Ų­Ų³Ų§Ų”
Ų“Ų±ŁƒŲ© Ł†Ł‚Ł„ Ų§Ų«Ų§Ų« ŲØŲ§Ł„Ų§Ų­Ų³Ų§Ų”
Ų“Ų±ŁƒŲ© Ł†Ł‚Ł„ Ų¹ŁŲ“ ŲØŲ§Ł„Ų¬ŲØŁŠŁ„
Ų“Ų±ŁƒŲ© Ł†Ł‚Ł„ Ų§Ų«Ų§Ų« ŲØŲ§Ł„Ų¬ŲØŁŠŁ„
Ų“Ų±ŁƒŲ© ŲŖŲ®Ų²ŁŠŁ† Ų¹ŁŲ“ ŲØŲ§Ł„ŲÆŁ…Ų§Ł…
Ų“Ų±ŁƒŲ© ŲŖŲ®Ų²ŁŠŁ† Ų§Ų«Ų§Ų« ŲØŲ§Ł„ŲÆŁ…Ų§Ł…
Ų“Ų±ŁƒŲ© ŲŖŲ®Ų²ŁŠŁ† Ų¹ŁŲ“ ŲØŲ§Ł„Ų®ŲØŲ±
Ų“Ų±ŁƒŲ© ŲŖŲ®Ų²ŁŠŁ† Ų§Ų«Ų§Ų« ŲØŲ§Ł„Ų®ŲØŲ±
Ų“Ų±ŁƒŲ© ŲŖŲ®Ų²ŁŠŁ† Ų§Ų«Ų§Ų« ŲØŲ§Ł„Ų¬ŲØŁŠŁ„
Ų“Ų±ŁƒŲ© ŲŖŲ®Ų²ŁŠŁ† Ų¹ŁŲ“ ŲØŲ§Ł„Ų¬ŲØŁŠŁ„
Ų“Ų±ŁƒŲ© ŲŖŲ®Ų²ŁŠŁ† Ų¹ŁŲ“ ŲØŲ§Ł„Ų§Ų­Ų³Ų§Ų”
Ų“Ų±ŁƒŲ© ŲŖŲ®Ų²ŁŠŁ† Ų§Ų«Ų§Ų« ŲØŲ§Ł„Ų§Ų­Ų³Ų§Ų”
Ų“Ų±ŁƒŲ© ŲŖŲ®Ų²ŁŠŁ† Ų¹ŁŲ“ ŲØŲ§Ł„Ł‚Ų·ŁŠŁ
Ų“Ų±ŁƒŲ© ŲŖŲ®Ų²ŁŠŁ† Ų§Ų«Ų§Ų« ŲØŲ§Ł„Ł‚Ų·ŁŠŁ

tajmohammadshaikh1000@gmail.com said...

Sustanon 250 for Sale USA

If you want to purchase Testosterone Mix and Sustanon 250 for Sale in the USA, then LegalRoids offer the best Sustanon 250 &Testomix product online at loewst price. Now buy Sustanon 250 for Sale in the USA.