Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2011
    Posts
    85

    Question Unanswered: Oracle grants for a View

    Hello Experts..

    I have a problem of user privileges for a View in oracle..

    There are 2 Schema in DB

    Lets Say S1 and S2

    S1 Have a View call V1

    S2 Have a View that Creates using the V1 let's name it as V2

    Now comes the problem..

    Another user available Call User_Test

    Now this user can access the V1 view but not V2 view..

    I have given the rights to that user, but no result..

    Plz guide me to over come this situation...

    Step by Step guide wold be so much appreciated...

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    It would help if you posted your SQL*Plus session that shows what you did and how you did that. Someone might know the answer, but I don't. I *suspect* that WITH GRANT OPTION clause might be missing, but - maybe it is not. Can't tell.

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by ai_zaviour View Post
    I have given the rights to that user, but no result..
    According to the Oracle behaviour (by the way what "cannot access V2 view" exactly means?) you probably did not. All necessary ones are stated in SQL Reference book, which is available with other Oracle documentation e.g. online on http://tahiti.oracle.com/
    For 11gR2 (the latest Oracle version; it would be nice if you posted your), it is stated in SELECT statement prerequisites: http://download.oracle.com/docs/cd/E...2.htm#i2065646
    For you to select data from a table or materialized view, the table or materialized view must be in your own schema or you must have the SELECT privilege on the table or materialized view.

    For you to select rows from the base tables of a view:
    * You must have the SELECT privilege on the view, and
    * Whoever owns the schema containing the view must have the SELECT privilege on the base tables.

    The SELECT ANY TABLE system privilege also allows you to select data from any table or any materialized view or the base table of any view.
    Quote Originally Posted by ai_zaviour View Post
    Step by Step guide wold be so much appreciated...
    You already posted it so it would be useless to repeat it.
    If you suppose it to be less vague, what about starting being more concrete in your posts firstly?

  4. #4
    Join Date
    Sep 2011
    Posts
    85
    Sorry about the less information provided

    I have a Oracle 9i database.

    What i explain in accessing V2 means..

    Using my USER_TEST I can do

    Select * from V1 - This will gives me results..

    But select * from V2 will give me a error of insufficient privileges..

    I have grant Select privilege for both schema for USER_TEST

  5. #5
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by ai_zaviour View Post
    I have grant Select privilege for both schema for USER_TEST
    Insufficient, as stated in the quote I posted:
    For you to select rows from the base tables of a view:
    * You must have the SELECT privilege on the view, and
    * Whoever owns the schema containing the view must have the SELECT privilege on the base tables.
    So, does also S2 have SELECT privilege on S1.V1 (and all tables/views from different schemas, which are used in V2)? According to the sentence in bold, it should.

  6. #6
    Join Date
    Sep 2011
    Posts
    85
    Thanx

    I'll try that one..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •