Re: Errors executing password change procedure

On 11/28/18 11:56 AM, Sandra Becker wrote:
> Oracle Enterprise version 12.1.0.2
>
> We have a new requirement to allow users to change their passwords,
> even if expired and/or account is locked.  Per the requirements, I
> have created the new user (not allowed DBA privs) that will connect
> through a GUI and execute a password change procedure in another
> schema that has the necessary privileges.  This new user has been
> granted execute privileges on the procedure.  However, I'm getting an
> "ORA-01031: insufficient privileges" error when I try to execute the
> procedure as the new user.

Hi Sandra!

You can create the procedure belonging to the user SYSTEM and grant an execute rights to your users. The default is so called "definer's rights" procedure, and that is what your security concerns are about.  The "definer's rights procedure" can access any object that its owner can access. Personally, I would create the procedure to unlock/change password for users not containing the string 'SYS'. An alternative would be to create a role LUSER and only allow the operations if the username to process is a member of the role LUSER. If you create another user, call it ORAPHB, you can grant the execute privilege on the SYSTEM.CHANGE_LUSER_PASSWORD procedure and that would be it. The procedure can access anything that the user SYSTEM can access.

Regards

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217

--
http://www.freelists.org/webpage/oracle-l