Home » Developer & Programmer » Forms » Procedure on alter user [MERGED]
Procedure on alter user [MERGED] [message #286003] Thu, 06 December 2007 04:59 Go to next message
vjay
Messages: 16
Registered: December 2007
Location: chennai
Junior Member
hi
i am using the below procedure to alter user password.In my application while inserting and modify it is working for main schema(abcd). in that schema i created nearly 30 users.entering thruogh one of the user and try to insert or modify from application.its not saving and its not showing any error.any option to give required grants to users or independent of grants procedure execution.

CREATE OR REPLACE PROCEDURE Alter_It (
kind IN VARCHAR2,
NAME IN VARCHAR2,
pw IN VARCHAR2
)
AUTHID CURRENT_USER
AS
v_sql VARCHAR2 (100);
BEGIN
v_sql := 'ALTER ' || kind || ' ' || NAME || ' ' || 'IDENTIFIED BY ' || pw;
DBMS_OUTPUT.put_line (v_sql);

EXECUTE IMMEDIATE v_sql;
END;
Re: procedure on alter user password [message #286019 is a reply to message #286003] Thu, 06 December 2007 05:29 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

just out of curiosity why do you want to use DYNAMIC SQL for this purpose?you won't be creating a user or changing their password regularly.it's a one time activity.


regards,
Re: procedure on alter user password [message #286024 is a reply to message #286019] Thu, 06 December 2007 05:37 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:

you won't be (...) changing their password regularly.it's a one time activity.

I wouldn't agree with that; we DO change passwords regularly and I believe it should be done that way.
Re: procedure on alter user password [message #286029 is a reply to message #286024] Thu, 06 December 2007 05:46 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

ok.but ,then i couldn't understand the reason for the use of var 'KIND'.

Quote:

v_sql := 'ALTER ' || kind || ' ' || NAME || ' ' || 'IDENTIFIED BY ' || pw;



v_sql :='ALTER USER '||p_name||' IDENTIFIED BY '||p_pwd;


regards,
Re: procedure on alter user password [message #286032 is a reply to message #286024] Thu, 06 December 2007 05:57 Go to previous messageGo to next message
vjay
Messages: 16
Registered: December 2007
Location: chennai
Junior Member
but in my application will works on the users. i.e i created 30 users in my database .every one access my application by using their user name and password .so while creating user name default password i given as their user name.so they want to change their password themselves,
so for that requirement i design one screen in forms6i. and calling that procedure in form level.
Re: procedure on alter user password [message #286034 is a reply to message #286032] Thu, 06 December 2007 06:06 Go to previous message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

so what happens?doesn't the procedure change the user password.i hope EXECUTE privs has been granted to those users.


regards,
Previous Topic: ORA-01841
Next Topic: run a procedure made in the DB
Goto Forum:
  


Current Time: Sun Dec 04 00:43:10 CST 2016

Total time taken to generate the page: 0.07592 seconds