How do I unlock an Oracle user's account from Java?

I'm trying to figure out why my application is unable to unlock a user's Oracle account successfully. Here's a snippet from my code:

OracleDataSource ods = new oracle.jdbc.pool.OracleDataSource();

Properties props = new Properties();
props.put("user", "sys");
开发者_如何学JAVAprops.put("password", "sys");
props.put("internal_logon", "sysdba");

ods.setConnectionProperties(props);
ods.setURL("jdbc:oracle:thin:@localhost:1523:TEST_DB");

Connection conn = ods.getConnection();
Statement stmt = conn.createStatement();
stmt.execute("ALTER USER SCOTT ACCOUNT UNLOCK");
stmt.close();

At no point does it raise an SQLException or report any problems, but the user's account doesn't actually get unlocked. Am I missing something obvious here or is there some cunning way of getting this to work?

---

I'd be very nervous about having an application connect as sysdba like this.

I would prefer to have a privileged user with permission to ALTER USER, remove the CREATE SESSION privilege from it, and have this schema contain an account_unlock pl/sql procedure for which execute privilege is granted to the user that your application connects with. ALTER USER is powerful stuff, and you probably want to restrict the attributes and even the individual users that your application can alter.

And that might also solve this problem.

---

try using executeUpdate rather than just execute. e.g.

stmt.executeUpdate("ALTER USER SCOTT ACCOUNT UNLOCK")

---

It is not a "commit" issue since this statement does not need a commit. Things to consider:

Does the statement unlock the account when you run it in SQLPLUS? Are you logging onto the correct database from java? Is there some process trying to logon into the account whith the wrong credentials resulting in a locked account?

---

I would be curious to see if this resolves the issue:

stmt.execute("BEGIN EXECUTE IMMEDIATE 'ALTER USER SCOTT ACCOUNT UNLOCK'; END;");

---

Try to set 'autocommit' property to 'true' for your jdbc connection. May be it's just set to 'false' by default and your sql is not committed.

---

The good news is that your code should work - I just did a quick test of it and the target account was unlocked. Two questions come to mind:

• How are you determining the account is still locked? Are you using SELECT username, account_status FROM DBA_USERS?

• Is there an Oracle profile in place that locks the account for failed logon attempts? Perhaps there is another process trying and failing to connect with an incorrect password that is re-locking the account.

Sorry to ask such basic questions, but again, your code does work.

---

Perhaps you are running into a case-sensitivity issue with Oracle 11?

---

It would be safer doing it through a callable statement which executes dynamic sql inside a package. That way you grant execute on a package eliminating the possibility of a SQL injection attack