RE: Authentication and passwords in derby.properties
Hi Knut, Thanks for your response. I tried setting the properties with derby completely lowercase and still don't seem to be having much luck with what I am trying to do. I have tried this a few different ways. Let me try to step through what I'm doing (and trying to do) and maybe you'll catch where I am going wrong: Here's what my derby.properties looks like (with auth disabled and the system level user disabled). #derby.connection.requireAuthentication=true #derby.authentication.provider=BUILTIN #derby.user.adminuser=adminpass derby.drda.portNumber=9947 derby.drda.host=0.0.0.0 derby.infolog.append=true derby.stream.error.file=C:/logs/javaserver_derby.log We have Derby running inside of a Tomcat process/service. I use RazorSQL as my interface to Derby. So today I tried: 1. Create database/user via the connection string: jdbc:derby://localhost:9947/mdb;create=true;user=mdb;password=mdb without specifying a username/password in the RazorSQL connection profile. This works. 2. Modify the profile to use the mdb user/pass and remove the connection string parameters and connect. This works. 3. Issue the following to Derby while logged in as MasterDB: CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.propertiesOnly','true'); CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.connection.requireAuthentication', 'true'); CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.authentication.provider', 'BUILTIN'); This works, and I can still log in. *However* what I want at this point is for no one else to be able to log in access this database or issue commands to Derby besides the "mdb" user that I created. My thought was to I enable authentication in derby.properties. However this then disallows access to the "mdb" user (I assume because the settings in derby.properies supercede the database level properties). So either I am doing something wrong, or what I want to do is not possible in Derby. Can I have a single database user that can log in and act against a single database schema without having a user declared in derby.properties in plain text. And can I deny access to Derby/schema to everyone else? If this is possible, can someone walk me through the steps to accomplish it. Thanks, Rob DiPietro -Original Message- From: Knut Anders Hatlen [mailto:knut.hat...@oracle.com] Sent: Saturday, January 14, 2012 7:37 AM To: Derby Discussion Subject: Re: Authentication and passwords in derby.properties Robert DiPietro writes: > Hello! > > I have been deving against a Derby DB on which requireAuthentication > is true, the provider is built-in, and all users have been defined in > the derby.properties file (so they are all system level users). This > has been extremely convenient in development, however there are > obvious concerns about having the password stored in plaintext in a > file. > > Is it possible to require authentication in built-in mode, but have no > system level users defined in derby.properties, rather only defined at > database level (stored internally on the database)? > > For example, I have tried disabling requireAuthentication, logging in > to the system, creating a db, and then creating a user against it > using these commands: > > CALL > SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('Derby.database.propertiesOnly' > ,'true'); CALL > SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('Derby.connection.requireAuthen > tication', 'true'); CALL > SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('Derby.authentication.provider' > , 'BUILTIN'); CALL > SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('Derby.user.username', > 'password'); > > Which seems to work, but then if I enable requireAuthentication in > derby.properties, I can no longer log in. Hi Robert, The property names should start with 'derby' (all lowercase), not 'Derby'. Does it work as expected if you change the case of the initial letter? -- Knut Anders
RE: Shutting down in-memory Derby gracefully
Hello, all can someone please also shed light on the following: In addition to the problems described in the previous email (below), I also get intermittent " Database 'memory:test' not found" errors while shutting down Derby: Error while shutting down: java.sql.SQLException: Database 'memory:test' not found. at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source) at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source) at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source) at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.newSQLException(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.handleDBNotFound(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection30.(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection40.(Unknown Source) at org.apache.derby.jdbc.Driver40.getNewEmbedConnection(Unknown Source) at org.apache.derby.jdbc.InternalDriver.connect(Unknown Source) at org.apache.derby.jdbc.AutoloadedDriver.connect(Unknown Source) at java.sql.DriverManager.getConnection(DriverManager.java:582) at java.sql.DriverManager.getConnection(DriverManager.java:207) Caused by: java.sql.SQLException: Database 'memory:test' not found. at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source) at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source) ... 21 more This error doesn't happen all the time, it's intermittent and I suspect that it might have be somehow connected to other problems I've been having trying to shutdown in-memory Derby DB gracefully. Can someone please help me understand what's causing this particular problem? My code that's shutting down Derby is quite simple, and oddly it works most of the times, but sometimes it throws this " Database 'memory:test' not found " error! Here is the excerpt of my shutdown code: private static final String DB_DROP_URL = "jdbc:derby:memory:%DB_NAME%;drop=true"; private static final String DB_SHUTDOWN_URL = "jdbc:derby:;shutdown=true"; private static class ShutdownAction { final private String m_url; final private int m_errorCode; final private String m_sqlState; private ShutdownAction(final String url, final int errorCode, final String sqlState) { m_url = url; m_errorCode = errorCode; m_sqlState = sqlState; } private void doAction(final String dbName) { final String url = m_url.replace("%DB_NAME%", dbName); logger.info("Executing Derby Shutdown Command: " + url); try { DriverManager.getConnection(url); } catch (SQLException e) { if ((e.getErrorCode() != m_errorCode) || (!m_sqlState.equals(e.getSQLState( { logger.error("Error while shutting down:", e); } } } } private static final ShutdownAction[] ACTIONS = { new ShutdownAction(DB_DROP_URL, 45000, "08006"), new ShutdownAction(DB_SHUTDOWN_URL, 5, "XJ015") }; private String m_dbName; private String m_dbCreateURL; private NetworkServerControlPinger m_derbyServerControl; @Override final public void shutdown() { for (final ShutdownAction action : ACTIONS) { action.doAction(m_dbName); } m_derbyServerControl.shutdown(); } -Original Message- From: Pavel Bortnovskiy [mailto:pbortnovs...@jefferies.com] Sent: Friday, January 13, 2012 10:01 AM To: Derby Discussion Subject: RE: Shutting down in-memory Derby gracefully Hello, Bryan: Thank you for your prompt response. I will report it as per guidelines. Would you be able to suggest anything that I could do in the meantime? We have watchdog processes monitoring error files and when the error files get populated with these messages, the watchdog raises all kinds of alarms. This is more than just a nuisance, as it's causing havoc in some cases. Thank you, Pavel. -Original Message- From: Bryan Pendleton [mailto:bpendleton.de...@gmail.com] Sent: Friday, January 13, 2012 9:58 AM To: Derby Discussion Subject: Re: Shutting down in-memory Derby gracefully > While our application is shutting down, it is possible that some of > its components may continue execution or even begin execution of the > queries on the in-memory Derby DB. In such situation, Derby starts throwing > all kinds of exceptions, including a Null Pointer Exception (pls see excerpt > from our log below). That looks like a bug to me, and worthy of reporting to th