Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2013
    Posts
    39
    Provided Answers: 2

    Unanswered: grant role to user

    Hi,

    i am trying to create a role which will have the grant on all tables of user1. and this role will be granted to user2...when i am connecting as user2 i can able to query the tables of user1. but i am not able to execute the package which i make using user1 tables neither i am able to select the views which i make using user1 tables. Please suggest...

    create role role_select;

    ------------testing.sql-----
    spool C:\role_select.sql;
    SELECT 'grant select on user1.'||table_name||' to role_select;' from dba_tables where owner='USER1';
    ----------------------------------
    @testing.sql;
    @role_select.sql;
    grant role_select to user2;

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    privileges acquired via ROLE do NOT apply within named PL/SQL procedures.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    ... which means that you'll have to grant those privileges directly from USER1 to USER2 (i.e. pretend that role doesn't even exist).

  4. #4
    Join Date
    Feb 2013
    Posts
    39
    Provided Answers: 2
    what about the view. i am able to create the view but not able to select the view. getting the error insufficient privileges. there should be a work around.
    user1 have more than 500 tables.

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    So what? You already have TESTING.SQL - don't grant to ROLE_SELECT, but to USER2. Run the script, and you have it all.

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    don't forget a view or a procedure is a seperate object and you have to grant select on the view and execute on the procedure to the other user.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Feb 2013
    Posts
    39
    Provided Answers: 2
    if i give direct grant to the table to which i am creating the view. its working even though the role has that grant for the table already...thanks for all your time. Appreciated.

Posting Permissions

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