db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: Strange Behaviour of Set User Access APIs
Date Mon, 26 Oct 2009 16:46:09 GMT
Gautam Satpathy <gautam.satpathy@gmail.com> writes:

> 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"!!!

Hi,

I think what you're experiencing here is that Derby handles the user
names differently in different contexts. The user names specified in the
connection URL or in the derby.user.* properties are converted to upper
case. So the following connection URLs mean the same thing:

  jdbc:derby:db;user=test
  jdbc:derby:db;user=Test
  jdbc:derby:db;user=TeSt

They all connect with the user id TEST. If you actually want to connect
with the user id test (lower case), you can quote the name in a similar
way to how you quote an SQL identifier. For example, you could use this
URL and property name:

  jdbc:derby:db;user="test"
  derby.user."test"

The SYSCS_SET_USER_ACCESS and SYSCS_GET_USER_ACCESS procedures, on the
other hand, don't convert the user name to upper case, so you'll have to
specify the user names exactly like Derby stores them internally. So
with the connection URL 'jdbc:derby:db;user=test', the correct way to
ask for the connection access permissions for that user is:

  VALUES SYSCS_UTIL.SYSCS_GET_USER_ACCESS('TEST')

Hope this helps,

-- 
Knut Anders

Mime
View raw message