Search code examples
javadatabasederby

Derby NATIVE authentication user creation


I create DB with NATIVE authentication. When i create user with temporary password, users can't login to DB with their temporary passwords. Here is my code;

Creating User;

String uName = userNameTextField.getText();
String pass = new String (passTextField.getPassword());

stmt2.execute("CALL SYSCS_UTIL.SYSCS_CREATE_USER('"+"\""+uName+"\""+"', '"+"\""+pass+"\""+"')");

Connection Attempt;

Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
String connectionUrl = "jdbc:derby://10.90.232.2:1527/myDB"+";create=false;" + "user=" +"\""+ unameTextField.getText() +"\""+ ";" + "password=" +"\""+ new String (passwordPasswordField.getPassword()) +"\""+ ";"; 
Connection con = DriverManager.getConnection(connectionUrl);

SQLException;

ERROR 08004: DERBY SQL error: ERRORCODE: 40000, SQLSTATE: 08004, SQLERRMC: Database connection refused.

If i try create same user, derby turn username already exist error. So this is show me actually user creation is done. But i don't understand why password not accepted.

--EDIT--

Also i have checked SYS.SYSUSERS System Table which stores NATIVE users. I see created user in there.

QUERY;

String query = "select USERNAME,HASHINGSCHEME,LASTMODIFIED from SYS.SYSUSERS";
stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(query);
ResultSetMetaData rsmd = rs.getMetaData();  
int columnsNumber = rsmd.getColumnCount();

while (rs.next()) {
   for (int i = 1; i <= columnsNumber; i++) {
      if (i > 1) System.out.print(",  ");
      String columnValue = rs.getString(i);
      System.out.print(columnValue);
   }
   System.out.println("");
}

OUTPUT;

owner,  3b62:53f094d5ab95f45fd351aca5856e0c06:1000:SHA-256,  2016-01-03 10:41:45.008
testuser,  3b62:bb98971e23c129fcd6e4cef37e9ac01d:1000:SHA-256,  2016-01-03 10:55:10.969

P.S.: Also SYS.SYSUSERS System table has PASSWORD Column. But when we try fetch it in query, derby turns java.sql.SQLSyntaxErrorException: No one can view the 'SYSUSERS'.'PASSWORD' column. exception.


Solution

  • --SOLVED--

    If Database created with;

    s.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
                    "'derby.database.defaultConnectionMode', 'noAccess')");
    

    Users permissions overriden. And they can't login to system. It must be fullAccess.

    And we must specified;

    s.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
                    "'derby.connection.requireAuthentication', 'true')");
    
    s.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
                    "'derby.database.sqlAuthorization', 'true')");
    

    For restrict reach to system unauthenticated and unauthorized users. We can use GRANT and REVOKE for which users can reach which database objects.