Hello Derby Gurus,
I am new to Apache Derby and am trying to use it in a security
sensitive Java desktop application which will be used by multiple
people with periodic data synch across user installs. For this I am
trying to use the BUILTIN Authentication with an Encrypted database.
I am seeing some strange behavior when I try to create multiple users
on my database. I first saw this in my Java code and thought to try
with "ij". To demonstrate I executed the following SQL statements
using "ij.bat":-
Command Used:- ij.bat ..\test_01.sql > test_01_out.txt
SQL in test_01.sql:-
-----------------------------
CONNECT
'jdbc:derby:test_db;create=true;dataEncryption=true;user=user1;password=mamliba...@1999;bootPassword=rajagunu1998;';
CALL
SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.connection.requireAuthentication',
'true');
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.authentication.provider',
'BUILTIN' );
CALL
SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.defaultConnectionMode',
'noAccess' ) ;
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.user.user1', 'welcome123') ;
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.user.user2', 'welcome123') ;
CALL SYSCS_UTIL.SYSCS_SET_USER_ACCESS( 'user1', 'FULLACCESS');
CALL SYSCS_UTIL.SYSCS_SET_USER_ACCESS( 'user2', 'FULLACCESS');
VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.database.fullAccessUsers');
VALUES SYSCS_UTIL.SYSCS_GET_USER_ACCESS( 'user2' ) ;
VALUES SYSCS_UTIL.SYSCS_GET_USER_ACCESS( '"user2"' ) ;
DISCONNECT;
CONNECT
'jdbc:derby:test_db;create=false;dataEncryption=true;user=user2;password=mamliba...@1999;bootPassword=rajagunu1998;';
CREATE TABLE FIRSTTABLE (ID INT PRIMARY KEY, NAME VARCHAR(12)) ;
INSERT INTO FIRSTTABLE VALUES (10,'TEN'),(20,'TWENTY'),(30,'THIRTY') ;
SELECT * FROM firsttable ;
DISCONNECT;
CONNECT
'jdbc:derby:test_db;create=false;dataEncryption=true;user="user2";password=mamliba...@1999;bootPassword=rajagunu1998;';
CREATE TABLE SECONDTABLE (ID INT PRIMARY KEY, NAME VARCHAR(12)) ;
INSERT INTO SECONDTABLE VALUES (10,'TEN'),(20,'TWENTY'),(30,'THIRTY') ;
SELECT * FROM secondtable ;
Output:
-----------
======================================================
ij version 10.5
ij> CONNECT
'jdbc:derby:test_db;create=true;dataEncryption=true;user=user1;password=mamliba...@1999;bootPassword=rajagunu1998;';
ij> CALL
SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.connection.requireAuthentication',
'true');
0 rows inserted/updated/deleted
ij> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.authentication.provider',
'BUILTIN' );
0 rows inserted/updated/deleted
ij> CALL
SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.defaultConnectionMode',
'noAccess' ) ;
0 rows inserted/updated/deleted
ij> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.user.user1',
'welcome123') ;
0 rows inserted/updated/deleted
ij> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.user.user2',
'welcome123') ;
0 rows inserted/updated/deleted
ij> CALL SYSCS_UTIL.SYSCS_SET_USER_ACCESS( 'user1', 'FULLACCESS');
0 rows inserted/updated/deleted
ij> CALL SYSCS_UTIL.SYSCS_SET_USER_ACCESS( 'user2', 'FULLACCESS');
0 rows inserted/updated/deleted
ij> VALUES
SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.database.fullAccessUsers');
1
--------------------------------------------------------------------------------------------------------------------------------
"user1","user2"
1 row selected
ij> VALUES SYSCS_UTIL.SYSCS_GET_USER_ACCESS( 'user2' ) ;
1
--------------------------------------------------------------------------------------------------------------------------------
FULLACCESS
1 row selected
ij> VALUES SYSCS_UTIL.SYSCS_GET_USER_ACCESS( '"user2"' ) ;
1
--------------------------------------------------------------------------------------------------------------------------------
NOACCESS
1 row selected
ij> DISCONNECT;
ij> CONNECT
'jdbc:derby:test_db;create=false;dataEncryption=true;user=user2;password=mamliba...@1999;bootPassword=rajagunu1998;';
ERROR 08004: Database connection refused.
ij> CREATE TABLE FIRSTTABLE (ID INT PRIMARY KEY, NAME VARCHAR(12)) ;
IJ ERROR: Unable to establish connection
ij> INSERT INTO FIRSTTABLE VALUES (10,'TEN'),(20,'TWENTY'),(30,'THIRTY') ;
IJ ERROR: Unable to establish connection
ij> SELECT * FROM firsttable ;
IJ ERROR: Unable to establish connection
ij> DISCONNECT;
IJ ERROR: Unable to establish connection
ij> CONNECT
'jdbc:derby:test_db;create=false;dataEncryption=true;user="user2";password=mamliba...@1999;bootPassword=rajagunu1998;';
ij> CREATE TABLE SECONDTABLE (ID INT PRIMARY KEY, NAME VARCHAR(12)) ;
0 rows inserted/updated/deleted
ij> INSERT INTO SECONDTABLE VALUES (10,'TEN'),(20,'TWENTY'),(30,'THIRTY') ;
3 rows inserted/updated/deleted
ij> SELECT * FROM secondtable ;
ID |NAME
------------------------
10 |TEN
20 |TWENTY
30 |THIRTY
3 rows selected
ij>
======================================================
Note that I am calling "SYSCS_SET_USER_ACCESS" twice for the 2 user I
create (user1 & user2). When I call
SYSCS_GET_DATABASE_PROPERTY('derby.database.fullAccessUsers') I get
"user1","user2" instead of user1,user2
Now comes the real strangeness!
SYSCS_GET_USER_ACCESS( 'user2' ) == FULLACCESS and
SYSCS_GET_USER_ACCESS( ''user2'' ) == NOACCESS
Finally I am NOT able to connect using user=user2 BUT I am able to
connect to the database with "user2"!!!
What is going on here? Am I doing something stupid? I won't be
surprised if I am. I downloaded Derby two nights ago :-)
>From "derby.log":
2009-10-26 15:29:42.703 GMT:
Booting Derby version The Apache Software Foundation - Apache Derby -
10.5.3.0 - (802917): instance a816c00e-0124-9177-9073-0000003fe150
on database directory D:\Work\Projects\oracle_hr\data\test_db
Regards,
Gautam Satpathy
