db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Sunitha Kambhampati <ksunitha...@gmail.com>
Subject Re: security question
Date Fri, 12 May 2006 22:45:38 GMT
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.

Mime
View raw message