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 existThose 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 privilegesWe 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
dating rituals in mexico http://loveepicentre.com/ dating san francisco
ReplyDeleteWe are a gaggle of volunteers and opening a brand new scheme in our
ReplyDeletecommunity. 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
Now I am going away to do my breakfast, afterward having my breakfast coming yet again to read further news.
ReplyDeleteFeel free to visit my homepage - thrombosed hemorrhoid treatment
buy tramadol online tramadol 100mg usa - online drugstore tramadol
ReplyDeletegeneric xanax blue xanax pills generic - xanax dosage yellow pill
ReplyDeletebuy tramadol online tramadol addiction dosage - where to buy tramadol online safely
ReplyDeletegeneric xanax xanax side effects yahoo answers - .5 mg xanax effects
ReplyDeletebuy tramadol online tramadol 50 mg once a day - buy tramadol no prescription online
ReplyDeletexanax online does xanax show up in a pre-employment drug test - buy xanax online reviews
ReplyDeletebuy tramadol online tramadol 50 mg side effects - buy tramadol online no prescription usa
ReplyDeletebuy tramadol online buy tramadol online without rx - buy tramadol online mastercard overnight
ReplyDeletegeneric xanax xanax bars order - alprazolam ratiopharm 0 5 mg nebenwirkungen
ReplyDeletegeneric xanax xanax dosage 1mg - xanax 2mg pies
ReplyDeletebuy tramadol online tramadol generic for ultracet - can buy tramadol online
ReplyDeletebuy xanax xanax is what drug classification - xanax withdrawal onset
ReplyDeletexanax alprazolam how long xanax drug screen - order xanax 2mg
ReplyDeletebuy tramadol online tramadol dosage webmd - buy cheap tramadol online
ReplyDeletetramadol online buy tramadol online no prescription - ultram tramadol usa
ReplyDeletelearn how to buy tramdadol cheap tramadol on line - tramadol hydrochloride online
ReplyDeletelearn how to buy tramdadol tramadol online with cod - tramadol for dogs usa
ReplyDeletetramadol online tramadol for dogs pain - buy ultram tramadol online
ReplyDeletehttp://buytramadolonlinecool.com/#50897 buy cheap tramadol online no prescription - tramadol 50 mg is used to treat
ReplyDeletehttp://buytramadolonlinecool.com/#56411 buy tramadol dogs usa - tramadol hcl lethal dose
ReplyDeletebuy tramadol order cheap tramadol-overnight - tramadol high get
ReplyDeletebuy tramadol tramadol no prescription overnight cod delivery - tramadol hcl 37.5 mg
ReplyDeletebuy ativan online combining ativan and alcohol - much needed overdose ativan
ReplyDeleteways to buy ativan online ativan dosage webmd - ativan dosage
ReplyDeletediscount ativan ativan interdose withdrawal - ativan dose to get high
ReplyDeletehttp://ranchodelastortugas.com/#58720 death from xanax and alcohol - how much does generic xanax cost
ReplyDeletebuy tramadol online does tramadol high feel like - generic tramadol 627
ReplyDeletexanax xanax drug dogs - difference between xanax pills bars
ReplyDeletehttp://bayshorechryslerjeep.com/#2902 xanax bars no markings - xanax drug anxiety
ReplyDeleteYοur own write-uρ provides establishеd necessary tο me.
ReplyDeleteIt’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
What's up to every one, as I am genuinely eager of reading this weblog's post to be updated on a regular
ReplyDeletebasis. It consists of good information.
My web blog; calories walking calculator
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 ;)
ReplyDeleteAlso visit my site how to lose weight fast
Your repoгt features еstablishеd beneficial to us.
ReplyDeleteIt’ѕ г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
I blog frequently and I genuinely thank you for your content.
ReplyDeleteThe 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
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
ReplyDeletethank you. Because you have solved.
ReplyDeletethank you. Because you have solved.
ReplyDeleteHTTP://WWW.ATLAS-DAMAM.COM
ReplyDeleteشركة نقل اثاث بالدمام
شركة نقل عفش بالدمام
شركة نقل اثاث بالخبر
شركة نقل عفش بالخبر
شركة نقل عفش بالاحساء
شركة نقل اثاث بالاحساء
شركة نقل عفش بالجبيل
شركة نقل اثاث بالجبيل
شركة تخزين عفش بالدمام
شركة تخزين اثاث بالدمام
شركة تخزين عفش بالخبر
شركة تخزين اثاث بالخبر
شركة تخزين اثاث بالجبيل
شركة تخزين عفش بالجبيل
شركة تخزين عفش بالاحساء
شركة تخزين اثاث بالاحساء
شركة تخزين عفش بالقطيف
شركة تخزين اثاث بالقطيف
Sustanon 250 for Sale USA
ReplyDeleteIf 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.