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.
