1 Reply Latest reply: Oct 23, 2012 1:01 PM by JustinCave RSS

    Granting user access to role with limited exposure

    970239
      I have a role in my Oracle 10g instance like below:

      GRANT ALTER USER TO <role_name> WITH ADMIN OPTION;

      And this works fine for any user who has:

      GRANT <role_name> TO <user>;


      What I need is to limit <user> to only have the ALTER USER privilege to a set of users. Preferrably where the set of users are identified by a column value in a table, something like:

      WHERE PeopleTable.InList = "YES"

      Or maybe where set of users are defined by their membership in another role.

      GRANT ALTER USER TO <role_name> FOR USERS IN MEMBERS_LIST_ROLE;

      I am sorry if my explanation uses statements with syntax errors. Thank you in advance for any help you can provide...
        • 1. Re: Granting user access to role with limited exposure
          JustinCave
          Normally, when you want to allow a user to do something that Oracle's privileges are not sufficiently fine-grained to handle, you would

          - Create a stored procedure that is owned by a privileged user (someone that has the ALTER USER privilege)
          - In this stored procedure, implement whatever checks you want (i.e. that the username passed in is a user that has the specified role or that is in a particular table)
          - Grant the user you want to have limited privileges EXECUTE access on this stored procedure

          The less-privileged user will now be able to alter a subset of users by executing the stored procedure but will not be able to alter other users.

          Justin