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 Re: Strange Behaviour of Set User Access APIs
Date Tue, 27 Oct 2009 03:50:03 GMT
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@apache.org> 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.
>

Mime
View raw message