db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Gautam Satpathy <gautam.satpa...@gmail.com>
Subject Strange Behaviour of Set User Access APIs
Date Mon, 26 Oct 2009 15:52:35 GMT
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=mamlibablu@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=mamlibablu@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=mamlibablu@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=mamlibablu@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=mamlibablu@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=mamlibablu@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

Mime
View raw message