Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2003
    Posts
    81

    Question Unanswered: Grant "create view" on a table to a role

    I want to grant rights to create a view based on a specific table. It seems that it is only possible to grant the rights to a user directly, but not to a role.

    Example:

    grant role1 to user2;
    connect user1/user1@vds;
    create table table1 (col1 number);
    grant select on table1 to role1;
    connect user2/user2@vds;
    create view view1 as select * from user1.table1;

    I get the message:
    ORA-01031: Insufficient privileges

    But if I do this:

    connect user1/user1@vds;
    grant select on table1 to user2;
    connect user2/user2@vds;
    create view view1 as select * from user1.table1;

    The view is created without any problem

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Did you, perhaps, forget to
    Code:
    GRANT role1 TO user2;

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Are you actually using the ROLE as user2. Simply giving someone a role, doesn't mean that they will use it. Try the following command as user2.

    SET ROLE ROLE1;

    SET ROLE ALL; would tell the session to use ALL the roles granted to the user.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Nov 2007
    Location
    Connecticut
    Posts
    40
    In order to create a view in a schema, that schema must have the privileges necessary to either select, insert, update, or delete rows from all the tables or views on which the view is based. The view owner must be granted these privileges directly, rather than through a role.

  5. #5
    Join Date
    Nov 2007
    Posts
    13
    do you try to grant option to user2 from tables that you want to build your view?I think you'l need it.

Posting Permissions

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