thanks everone for your responses and help. It seems to be working now using the authenticaiton code. Much appreciated.
regards
Moeed

 
On 5/13/06, Sunitha Kambhampati <ksunithaghm@gmail.com> wrote:
Souciance Eqdam Rashti wrote:

> Hello Everyone
>
> I have a rather annoying problem. I was wondering if anyone could
> provide sample code as to how I can restrict a user to a single
> database or basically a certain number of database. So for example
> user fred would  only be allowed to access accounting and not sales.
>
> It seems to be that once a user gets database access he can login into
> any database but I want to restrict a user to a certain database. Thanks.

One way to do this in derby is as follows:

Note: All the properties mentioned here are in the tuning guide
http://db.apache.org/derby/docs/dev/tuning/ctunproper22250.html

1) Enable user authentication using
derby.connection.requireAuthentication=true

2) You can set which users have what access to the database.
derby.database.fullAccessUsers
http://db.apache.org/derby/docs/dev/tuning/rtunproper25025.html
derby.database.readOnlyAccessUsers

3)You can set these properties at a database level using
CALL
SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(' derby.database.fullAccessUsers',
'alice');

4) Make sure that these properties wont be overriden by system level
properties, you can set the following property.
CALL
SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(' derby.database.propertiesOnly','true');

5) Tune the default connection level of the database.
derby.database.defaultConnectionMode
http://db.apache.org/derby/docs/dev/tuning/rtunproper24846.html
CALL
SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.defaultConnectionMode','noAccess');

Below is a example:

C:\TESTING>java org.apache.derby.tools.ij ex.sql
ij version 10.2
ij> connect 'jdbc:derby:accountingdb;create=true';
ij> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.user.fred',
'pwd8xyz');
0 rows inserted/updated/deleted
ij> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.user.alice',
'hobbes8');
0 rows inserted/updated/deleted
ij> CALL
SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.fullAccessUsers',
'fred');
0 rows inserted/updated/deleted
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.database.propertiesOnly','true');
0 rows inserted/updated/deleted
ij> CALL
SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(' derby.database.defaultConnectionMode','noAccess');
0 rows inserted/updated/deleted
ij> create table fredt1(i1 int);
0 rows inserted/updated/deleted
ij> connect 'jdbc:derby:salesdb;create=true';
ij(CONNECTION1)> CALL
SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.user.alice', 'hobbes8');
0 rows inserted/updated/deleted
ij(CONNECTION1)> create table alice_t1(i1 int);
0 rows inserted/updated/deleted
ij(CONNECTION1)> CALL
SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.fullAccessUsers',
'alice');
0 rows inserted/updated/deleted
ij(CONNECTION1)> CALL
SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.connection.requireAuthentication ','true');
0 rows inserted/updated/deleted
ij(CONNECTION1)> CALL
SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.propertiesOnly','true');
0 rows inserted/updated/deleted
ij(CONNECTION1)> exit;

C:\TESTING>java org.apache.derby.tools.ij
ij version 10.2
ij> connect 'jdbc:derby:accountingdb;user=alice;password=hobbes8';
ERROR 04501: Database connection refused.
-----------------------> alice cannot access the database accountingdb.
ij> connect 'jdbc:derby:salesdb;user=alice;password=hobbes8';
ij> connect 'jdbc:derby:accountingdb;user=fred;password=pwd8xyz';
ij(CONNECTION1)>

Please make sure that you dont forget the password of the user that has
access to the database else you will lock yourself out.

Hope this helps,
Sunitha.