Pages

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:

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

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

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

    ReplyDelete
  4. buy tramadol online tramadol 100mg usa - online drugstore tramadol

    ReplyDelete
  5. generic xanax blue xanax pills generic - xanax dosage yellow pill

    ReplyDelete
  6. buy tramadol online tramadol addiction dosage - where to buy tramadol online safely

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

    ReplyDelete
  8. buy tramadol online tramadol 50 mg once a day - buy tramadol no prescription online

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

    ReplyDelete
  10. buy tramadol online tramadol 50 mg side effects - buy tramadol online no prescription usa

    ReplyDelete
  11. buy tramadol online buy tramadol online without rx - buy tramadol online mastercard overnight

    ReplyDelete
  12. generic xanax xanax bars order - alprazolam ratiopharm 0 5 mg nebenwirkungen

    ReplyDelete
  13. generic xanax xanax dosage 1mg - xanax 2mg pies

    ReplyDelete
  14. buy tramadol online tramadol generic for ultracet - can buy tramadol online

    ReplyDelete
  15. buy xanax xanax is what drug classification - xanax withdrawal onset

    ReplyDelete
  16. xanax alprazolam how long xanax drug screen - order xanax 2mg

    ReplyDelete
  17. buy tramadol online tramadol dosage webmd - buy cheap tramadol online

    ReplyDelete
  18. tramadol online buy tramadol online no prescription - ultram tramadol usa

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

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

    ReplyDelete
  21. tramadol online tramadol for dogs pain - buy ultram tramadol online

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

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

    ReplyDelete
  24. buy tramadol order cheap tramadol-overnight - tramadol high get

    ReplyDelete
  25. buy tramadol tramadol no prescription overnight cod delivery - tramadol hcl 37.5 mg

    ReplyDelete
  26. buy ativan online combining ativan and alcohol - much needed overdose ativan

    ReplyDelete
  27. ways to buy ativan online ativan dosage webmd - ativan dosage

    ReplyDelete
  28. discount ativan ativan interdose withdrawal - ativan dose to get high

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

    ReplyDelete
  30. buy tramadol online does tramadol high feel like - generic tramadol 627

    ReplyDelete
  31. xanax xanax drug dogs - difference between xanax pills bars

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

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

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

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

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

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

    ReplyDelete
  38. thank you. Because you have solved.

    ReplyDelete
  39. thank you. Because you have solved.

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

    ReplyDelete