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=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"' ) ;
VALUES SYSCS_UTIL.SYSCS_GET_USER_ACCESS( 'user2' ) ;
DISCONNECT;
CONNECT
'jdbc:derby:test_db1;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_db1;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 ;
================
With this result:
================
ij version 10.5
ij> CONNECT
'jdbc:derby:test_db1;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> 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=mamliba...@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=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>
================
Regards,
Gautam Satpathy
On Tue, Oct 27, 2009 at 12:08 AM, Daniel John Debrunner <[email protected]> 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.
>
