Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467

    Unanswered: alter my own user fails in package

    Setup: 9i Forms, 9iDB

    as a user in SQLPLUS I can execute the following successfully:
    ALTER USER id IDENTIFIED BY pwd;

    However if I call a package.procedure that attempts to
    PROCEDURE new_password(id VARCHAR2,
    password VARCHAR2) IS
    sql_string VARCHAR2(256);
    BEGIN

    sql_string := 'ALTER USER '||id||' IDENTIFIED BY '||password;

    DBMS_OUTPUT.PUT_LINE(sql_string);

    EXECUTE IMMEDIATE sql_string;

    END new_password;

    I received an "insufficient privileges" error. I think it has something to do with what user a package gets executed as but I am not certain. Does anyone know what is going on? Any one know how to make it work?
    NOTE: Please disregard the label "Senior Member".

  2. #2
    Join Date
    Jan 2004
    Posts
    370
    If the procedure owner has the alter user privilege assigned to them via a role then they will not have that right when the procedure is run. They must have any privileges required by the procedure to be assigned to them directly. This is because when a procedure is run with definer rights, all roles are disabled. (Unless the procedure runs with invoker rights).

  3. #3
    Join Date
    Jan 2004
    Posts
    370
    Oops.

    Looks like this has been answered in a duplicate thread.

  4. #4
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Skywriter: What you said makes sense ... if only I could understand it. :-)

    Let me see if I can explain this back to you.

    the definer of the package has the DBA role, but since they do not explicitly have a "alter user" privilege the package fails.

    I tried using the authid (invoker) method but because I am doing alter user (non-schema) alterations that is not allowed. I tried granting "all privileges" to the definerand then "alter user" to the definer but that still didn't work. Then I realized that the privileges are "installed" at definition time not run time. I recreated the package and as you would expect it worked.

    Thanks.
    PS - this issue was not answered in another thread (of mine anyway).
    NOTE: Please disregard the label "Senior Member".

Posting Permissions

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