Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2005

    Question Unanswered: ALTER SESSION / SET ROLE problem


    we are currently using 8.1.6 SE One and use a passwd protected role to protect our app data.

    Following (unsolveable?) problem appears :

    We are setting the role (SET ROLE FOO ...) in the app, and run a "ALTER SESSION SET CURRENT_SCHEMA=myschema".

    When I try to access a view using package functions, I get "ORA-00904--Invalid Column name". The role has got select privileges on the view.

    I have to grant all inner objects of the view to the user, and *whoops* ... it works (but I definitely dont want this).

    If I grant the view to the role without altering schema and session, it works (?) by adressing the view via schema prefix.

    What can I do? I have a layer of external views which shall be published to the role. I cannot afford opening all inner objects to the role by granting them (and is a direct conflict to a view-abstraction model).

    Please help!

  2. #2
    Join Date
    Sep 2002
    Provided Answers: 1
    You need to grant privileges on the objects to the package owner, because role privileges are not available to packages.

  3. #3
    Join Date
    Jan 2005

    package owner = schema owner = all objects-owner.

    The packages and views are created by FOO. The selection privilige on the views of the external layer are granted to the role BAR.

    What do I need to grant (maybe I misunderstand You) ?

    thanks ;-)

Posting Permissions

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