Results 1 to 5 of 5
  1. #1
    Join Date
    May 2004
    Posts
    35

    Unanswered: Changing permissions on a VIEW?

    How can I change permissions on a VIEW so that other users can see the data?

    For instance if I log in to the Oracle server using SQLPlus Worksheet I can create a view using ..

    CREATE VIEW MyUserName.MyView As SELECT * FROM Schema.MyTable

    View Created.


    Then I can view the results..

    SELECT * FROM MyUserName.MyView

    But I want for other users to be able to see this data. How can I set permissions to allow others to see the data in my view?


    Also another side question, is it possible to create a view on tables in a different schema, and then give users access to the view but don't give them access to the tables in the other schema, and they would be able to see all of the data in the tables without having access to the actual tables?

    Thanks for any help! I'm new to Oracle management but definitely not new to programming! Any help is appreciated!

  2. #2
    Join Date
    Apr 2004
    Location
    USA
    Posts
    33
    Take a look at the 'GRANT' command. This will help you to grant 'Select' access to users.

  3. #3
    Join Date
    May 2004
    Posts
    35
    Thanks for the tip!

    Unfortunately whenever I try a

    GRANT SELECT ON MyUser.MyView TO User1, User2

    I get this error message:

    ORA-01720: grant option does not exist for 'SchemaName.TableName'

    And the start character is under the first letter of the name of the view (it would be the 'V' here: Schema.View)

  4. #4
    Join Date
    Apr 2004
    Location
    USA
    Posts
    33
    Are you logged in as a user who has the grant privileges? You need the "GRANT ANY ROLE" privilege.

  5. #5
    Join Date
    Mar 2004
    Location
    California
    Posts
    58
    When granting select on the view are you logged in to the schema that created the view? If so just try "Grant select on <view name> to User1". You shouldnt need special privileges to grant users access to your own object.
    As for your question
    "Also another side question, is it possible to create a view on tables in a different schema, and then give users access to the view but don't give them access to the tables in the other schema, and they would be able to see all of the data in the tables without having access to the actual tables?"
    --the answer is such: If you need to create a view in another schema, you need to have the system privilege Create Any View. And yes once you have created these views you can give select privileges to users on the view and no privilege on the original table. Infact this is one of the purposes of having views.

Posting Permissions

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