Strange Behaviour of Set User Access APIs
|
View:
New views
4 Messages
—
Rating Filter:
Alert me
|
 |
Strange Behaviour of Set User Access APIs
by Gautam Satpathy
::
Rate this Message:
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 |
|
|
Re: Strange Behaviour of Set User Access APIs
by Knut Anders Hatlen
::
Rate this Message:
Gautam Satpathy <gautam.satpathy@...> 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 |
|
|
Re: Strange Behaviour of Set User Access APIs
by Daniel John Debrunner-2
::
Rate this Message:
Knut Anders Hatlen wrote:
> I think what you're experiencing here is that Derby handles the user > names differently in different contexts. FYI http://wiki.apache.org/db-derby/UserIdentifiers Dan. |
|
|
Re: Strange Behaviour of Set User Access APIs
by Gautam Satpathy
::
Rate this Message:
Thanks Knut & Dan. That clears up the confusion I had.
I tested this out with a modified test script: ================ CONNECT 'jdbc:derby:test_db1;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"' ) ; VALUES SYSCS_UTIL.SYSCS_GET_USER_ACCESS( 'user2' ) ; DISCONNECT; CONNECT 'jdbc:derby:test_db1;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_db1;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 ; ================ With this result: ================ ij version 10.5 ij> CONNECT 'jdbc:derby:test_db1;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> VALUES SYSCS_UTIL.SYSCS_GET_USER_ACCESS( 'user2' ) ; 1 -------------------------------------------------------------------------------------------------------------------------------- NOACCESS 1 row selected ij> DISCONNECT; ij> CONNECT 'jdbc:derby:test_db1;create=false;dataEncryption=true;user=USER2;password=mamlibablu@1999;bootPassword=rajagunu1998;'; ij> CREATE TABLE FIRSTTABLE (ID INT PRIMARY KEY, NAME VARCHAR(12)) ; 0 rows inserted/updated/deleted ij> INSERT INTO FIRSTTABLE VALUES (10,'TEN'),(20,'TWENTY'),(30,'THIRTY') ; 3 rows inserted/updated/deleted ij> SELECT * FROM firsttable ; ID |NAME ------------------------ 10 |TEN 20 |TWENTY 30 |THIRTY 3 rows selected ij> DISCONNECT; ij> CONNECT 'jdbc:derby:test_db1;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> ================ Regards, Gautam Satpathy On Tue, Oct 27, 2009 at 12:08 AM, Daniel John Debrunner <djd@...> wrote: > Knut Anders Hatlen wrote: > >> I think what you're experiencing here is that Derby handles the user >> names differently in different contexts. > > FYI > > http://wiki.apache.org/db-derby/UserIdentifiers > > Dan. > |
| Free embeddable forum powered by Nabble | Forum Help |
