Execute Immediate Alter User Bind Variable

Question

The following procedure:

create or replace
PROCEDURE ChangePassword
(
    p_Name      VARCHAR2,
    p_Password  VARCHAR2
)
AS
  EXECUTE IMMEDIATE 'ALTER USER :a IDENTIFIED BY :b' USING p_Name, p_Password;
END;

compiles successfuly, but when executed:

exec ChangePassword('TestUser', 'newPassword');

results in error:

01935. 00000 -  "missing user or role name"
*Cause:    A user or role name was expected.
*Action:   Specify a user or role name.

Why?

AFAIK, you can't use bind for DDL statements, can you?

Florin Ghita
– Florin Ghita

2012-04-25 13:32:58 +00:00
Commented Apr 25, 2012 at 13:32 — Florin Ghita
– Florin Ghita, Commented Apr 25, 2012 at 13:32

Dave Costa · Accepted Answer · 2012-04-25 12:10:18Z

5

You can't use bind variables in place of identifiers, such as the user name in this statement. The value of the identifier needs to be known when the statement is parsed, whereas a bind value is incorporated after parsing, before execution.

answered Apr 25, 2012 at 12:10

Dave Costa

48.3k8 gold badges61 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bob Jarvis - Слава Україні · Accepted Answer · 2012-04-25 12:13:26Z

4

I think that the following would work

create or replace PROCEDURE ChangePassword(p_Name         IN VARCHAR2,
                                           p_Old_password IN VARCHAR2,
                                           p_New_password IN VARCHAR2)
AS
  EXECUTE IMMEDIATE 'ALTER USER ' || p_name ||
                    ' IDENTIFIED BY ' || p_New_password ||
                    ' REPLACE ' || p_Old_password;
END;

Share and enjoy.

answered Apr 25, 2012 at 12:13

Bob Jarvis - Слава Україні

50.2k10 gold badges81 silver badges119 bronze badges

2 Comments

Atif Over a year ago

That do work, I tried. The problem is, its VERY open to sql injection. Is there some secure way to accomplish the task?

Brian Camire Over a year ago

You might use the DBMS_ASSERT package to avoid SQL injection. See link. For example, you might use DBMS_ASSERT.ENQUOTE_NAME to make sure the user name and old and new password are properly treated as names, and DBMS_ASSERT.SCHEMA_NAME to assert that the user name is a valid one.

Collectives™ on Stack Overflow

Execute Immediate Alter User Bind Variable

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related