How to create a derby user

Question

I tried to set the DB schema name by schema.xml but it caused a schema name duplication in the generated SQL statement for ID generators. (Duplicate schema name in sequece generation)

I read the schema is defined by the passed user at connection time. Now I would like to set the schema by this way.

But I don't know how can I create a new Derby user and link it with the desired schema. Can somebody help me?

Environment: NetBeans, Glassfish, Derby

I have found this:

CALL SYSCS_UTIL.SYSCS_CREATE_USER('username', 'password')

But Derby answers:

Error code -1, SQL state 42Y03: 'SYSCS_UTIL.SYSCS_CREATE_USER' is not recognized as a function or procedure.

Why? I have connected to the db as the default admin user.

Or if I try to dispatch this command from a GUI tool, Derby says:

[Error Code: 0, SQL State: 42Y07] : Schema 'SYSCS_UTIL' does not exist

Answer 1

here is a solution (heavily commented):

String setProperty = "CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(";
String getProperty = "VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY(";
String requireAuth = "'derby.connection.requireAuthentication'";

String sqlAuthorization = "'derby.database.sqlAuthorization'";
String defaultConnMode  = "'derby.database.defaultConnectionMode'";
String fullAccessUsers  = "'derby.database.fullAccessUsers'";

String readOnlyAccessUsers = "'derby.database.readOnlyAccessUsers'";
String provider            = "'derby.authentication.provider'";
String propertiesOnly      = "'derby.database.propertiesOnly'";

System.out.println("Turning on authentication and SQL authorization.");
Statement s = conn.createStatement();

// Set requireAuthentication
s.executeUpdate(setProperty + requireAuth + ", 'true')");

//CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.connection.requireAuthentication','true'
// Set sqlAuthorization

s.executeUpdate(setProperty + sqlAuthorization + ", 'true')");

//CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.sqlAuthentication','true'
// Retrieve and display property values

ResultSet rs = s.executeQuery(getProperty + requireAuth + ")");
rs.next();
System.out.println("Value of requireAuthentication is " + rs.getString(1));

rs = s.executeQuery(getProperty + sqlAuthorization + ")");
rs.next();
System.out.println("Value of sqlAuthorization is " + rs.getString(1));

// Set authentication scheme to Derby builtin
s.executeUpdate(setProperty + provider + ", 'BUILTIN')");

// Create some sample users
s.executeUpdate(setProperty + "'derby.user." + txtUname.getText() + "', '" + txtPw1.getText() + "')" );

// Define noAccess as default connection mode
s.executeUpdate(setProperty + defaultConnMode + ", 'noAccess')");

// Confirm default connection mode
rs = s.executeQuery(getProperty + defaultConnMode + ")");
rs.next();
System.out.println("Value of defaultConnectionMode is " + rs.getString(1));

// Define read-write users
s.executeUpdate(setProperty + fullAccessUsers + ", '" + txtUname.getText() + "')");

// Define read-only user
// s.executeUpdate(setProperty + readOnlyAccessUsers + ", 'guest')");

// Confirm full-access users
rs = s.executeQuery(getProperty + fullAccessUsers + ")");
rs.next();
System.out.println("Value of fullAccessUsers is " + rs.getString(1));

// Confirm read-only users
rs = s.executeQuery(getProperty + readOnlyAccessUsers + ")");
rs.next();
System.out.println("Value of readOnlyAccessUsers is " + rs.getString(1));

// We would set the following property to TRUE only when we were
// ready to deploy. Setting it to FALSE means that we can always
// override using system properties if we accidentally paint
// ourselves into a corner.

s.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + "'derby.database.propertiesOnly', 'false')");
s.close();

Let me know if that works (or not) for you.

Answer 2

To Create user in Derby : I am using Command Line Interface and I have already set my System environment variable to Derby. Otherwise you can write these command into your CLI

java -jar %DERBY_HOME%\lib\derbyrun.jar ij

and hit Enter key to run ij tool, and you will see a prompt like this:

ij>

Now type the following command to create a user (replace sam by the desired username and sampass by the desired password):

CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.user.sam','sampass');

Now hit enter. This should give a message like this:

0 rows inserted/updated/deleted.