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

81 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 alprazolam online without prescription reviews of xanax for anxiety - xanax withdrawal pregnancy

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...

xanax online do xanax pills expire - generic xanax l441

Anonymous said...

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

Anonymous said...

where to buy xanax online no prescription xanax 5mg street value - xanax job interview

Anonymous said...

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

Anonymous said...

generic xanax clonazepam vs. xanax for anxiety - xanax side effects in the elderly

Anonymous said...

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

Anonymous said...

tramadol online no prescription tramadol dosage schedule - tramadol dosage for small dogs

Anonymous said...

buy tramadol no rx buy tramadol in europe - 200 mg tramadol high

Anonymous said...

xanax online what does xanax and alcohol do to the body - xanax recreational use

Anonymous said...

generic xanax xanax small white pills - pictures of xanax pills generic

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...

buying xanax online no prescription xanax klonopin side effects - ambien xanax and alcohol together

Anonymous said...

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

Anonymous said...

buy carisoprodol carisoprodol unrecognized drug abuse - soma carisoprodol 250

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...

xanax online using xanax recreational use - xanax drug contradictions

Anonymous said...

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

Anonymous said...

buy tramadol online tramadol nights - tramadol good suboxone withdrawal

Anonymous said...

buy xanax xanax is what drug classification - xanax withdrawal onset

Anonymous said...

buy cialis online how to buy cialis online in australia - cialis price in thailand

Anonymous said...

cialis online cialis daily use insurance - cialis 5 coupon

Anonymous said...

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

Anonymous said...

buy xanax online cheap no prescription xanax side effects recreational - xanax dosage 2mg

Anonymous said...

buy cialis online order cialis online mastercard - generic cialis us

Anonymous said...

cialis online generic cialis us suppliers - buy cialis online usa no prescription

Anonymous said...

can you really buy xanax online xanax side effects tremors - xanax side effects withdrawal

Anonymous said...

buy alprazolam online no prescription xanax and alcohol behavior - xanax side effects hair loss

Anonymous said...

cialis online cialis 20mg reviews - cialis online no prescription usa

Anonymous said...

cialis online cialis retail price - cialis 5mg reviews

Anonymous said...

buy tramadol online tramadol dosage webmd - buy cheap tramadol online

Anonymous said...

buy tramadol tramadol withdrawal what to do - tramadol xl

Anonymous said...

buy tramadol online buy tramadol online no prescription cheap - boompanjang blogspot buy tramadol online

Anonymous said...

http://buytramadolonlinecool.com/#28875 tramadol equivalent - tramadol for dogs+urination

Anonymous said...

http://landvoicelearning.com/#51602 par 822 tramadol - tramadol dosage 50 lb dog

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...

buy tramadol tramadol hcl good - tramadol 50 mg generic

Anonymous said...

buy tramadol tramadol online without prescriptions - buy tramadol online cheap

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...

buy ativan online ativan gtt alcohol withdrawal - ativan high cortisol

Anonymous said...

ways to buy ativan online ativan dosage webmd - ativan dosage

Anonymous said...

ativan for anxiety order ativan online - ativan dosage 0.25 mg

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 xanax valium online florida does xanax show up 12 panel drug test - 2mg xanax bars online

Anonymous said...

http://staam.org/#92453 zydol sr 100mg tramadol hydrochloride - buy tramadol cheap online no prescription

Anonymous said...

http://ranchodelastortugas.com/#51462 xanax 029 - xanax overdose coma

Anonymous said...

http://ranchodelastortugas.com/#61301 xanax pills description - buy xanax online real

Anonymous said...

xanax price pictures generic 2mg xanax - much generic xanax without insurance

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://ranchodelastortugas.com/#71453 xanax recreational use grasscity - how to buy xanax online in ireland

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...

oakley sunglasses, prada handbags, oakley sunglasses, longchamp handbags, longchamp handbags, louboutin shoes, louis vuitton handbags, coach factory outlet, tiffany and co, coach purses, louis vuitton outlet, polo ralph lauren outlet, air max, prada outlet, longchamp outlet, oakley sunglasses cheap, ray ban sunglasses, louboutin outlet, michael kors outlet, michael kors outlet, tiffany and co, burberry outlet, christian louboutin shoes, coach outlet store online, jordan shoes, polo ralph lauren outlet, louboutin, kate spade handbags, michael kors outlet, coach outlet, air max, gucci outlet, michael kors outlet, ray ban sunglasses, chanel handbags, michael kors outlet, tory burch outlet, nike free, kate spade outlet, louis vuitton outlet, burberry outlet, louis vuitton outlet stores, louis vuitton, nike shoes, michael kors outlet

oakleyses said...

air max, hollister, true religion outlet, nike blazer, louboutin, ray ban sunglasses, polo ralph lauren, michael kors, true religion jeans, sac guess, sac longchamp, hogan outlet, ralph lauren, vans pas cher, sac louis vuitton, air max pas cher, nike free pas cher, nike free, air max, mulberry, nike roshe run, sac burberry, hollister, vanessa bruno, louis vuitton, lululemon, michael kors pas cher, oakley pas cher, air jordan, ray ban pas cher, new balance pas cher, polo lacoste, converse pas cher, north face, sac louis vuitton, michael kors, sac hermes, nike tn, timberland, louis vuitton uk, longchamp, true religion jeans, nike air max, air force, north face

oakleyses said...

air max, hollister, true religion outlet, nike blazer, louboutin, ray ban sunglasses, polo ralph lauren, michael kors, true religion jeans, sac guess, sac longchamp, hogan outlet, ralph lauren, vans pas cher, sac louis vuitton, air max pas cher, nike free pas cher, nike free, air max, mulberry, nike roshe run, sac burberry, hollister, vanessa bruno, louis vuitton, lululemon, michael kors pas cher, oakley pas cher, air jordan, ray ban pas cher, new balance pas cher, polo lacoste, converse pas cher, north face, sac louis vuitton, michael kors, sac hermes, nike tn, timberland, louis vuitton uk, longchamp, true religion jeans, nike air max, air force, north face

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.

sunglyoung kim said...

thank you. Because you have solved.

fattoma said...

http://www.prokr.net/2016/09/eliminate-cockroaches-21.html
http://www.prokr.net/2016/09/eliminate-cockroaches-20.html
http://www.prokr.net/2016/09/eliminate-cockroaches-19.html
http://www.prokr.net/2016/09/eliminate-cockroaches-18.html
http://www.prokr.net/2016/09/eliminate-cockroaches-16_12.html
http://www.prokr.net/2016/09/eliminate-cockroaches-16.html
http://www.prokr.net/2016/09/eliminate-cockroaches-15.html
http://www.prokr.net/2016/09/eliminate-cockroaches-13_12.html
http://www.prokr.net/2016/09/eliminate-cockroaches-13.html
http://www.prokr.net/2016/09/eliminate-cockroaches-12.html

Atlas Damam said...

HTTP://WWW.ATLAS-DAMAM.COM
شركة نقل اثاث بالدمام
شركة نقل عفش بالدمام
شركة نقل اثاث بالخبر
شركة نقل عفش بالخبر
شركة نقل عفش بالاحساء
شركة نقل اثاث بالاحساء
شركة نقل عفش بالجبيل
شركة نقل اثاث بالجبيل
شركة تخزين عفش بالدمام
شركة تخزين اثاث بالدمام
شركة تخزين عفش بالخبر
شركة تخزين اثاث بالخبر
شركة تخزين اثاث بالجبيل
شركة تخزين عفش بالجبيل
شركة تخزين عفش بالاحساء
شركة تخزين اثاث بالاحساء
شركة تخزين عفش بالقطيف
شركة تخزين اثاث بالقطيف

roba gad said...

http://www.prokr.net/2016/09/waterproofing-companies-10.html
http://www.prokr.net/2016/09/waterproofing-companies-9.html
http://www.prokr.net/2016/09/waterproofing-companies-8.html
http://www.prokr.net/2016/09/waterproofing-companies-7.html
http://www.prokr.net/2016/09/waterproofing-companies-6.html
http://www.prokr.net/2016/09/waterproofing-companies-5.html
http://www.prokr.net/2016/09/waterproofing-companies-4.html
http://www.prokr.net/2016/09/waterproofing-companies-3.html
http://www.prokr.net/2016/09/waterproofing-companies.html