You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by "Stefan R." <el...@gmail.com> on 2012/11/26 20:31:40 UTC
Backup using SYSCS_UTIL.SYSCS_BACKUP_DATABASE with required authentication
Hi,
in one of our web apps we activated derbys builtin user authentication for
the databases. We are using Derby 10.8.2.2
The app is triggering a backup task, which executes
SYSCS_UTIL.SYSCS_BACKUP_DATABASE over a JDBC connection. This throws an
exception saying: the connection could not be authorized.
The following statements have been used to activate authentication:
--------------------
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.user.DBUSER1',
'password);
CALL
SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.defaultConnectionMode',
'readOnlyAccess');
CALL
SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.fullAccessUsers',
'DBUSER1);
CALL
SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.propertiesOnly','false');
--------------------
So, DBUSER1 should have full access.
Are there any restrictions to SYSCS_UTIL functions for fullAccessUsers? How
to authorize these?
Thanks for your input,
Stefan
Re: Backup using SYSCS_UTIL.SYSCS_BACKUP_DATABASE with required authentication
Posted by "Stefan R." <el...@gmail.com>.
Ok. Thanks for your input. I double checked our code and voilá there was a
bug while retrieving the password from our entitymanager.
Self produced problem solved... :/
2012/11/26 Rick Hillegas <ri...@oracle.com>
> Hi Stefan,
>
> I am not able to reproduce the behavior you are seeing. The following
> script works fine for me when I use Derby 10.8.2.2:
>
> connect 'jdbc:derby:memory:db;create=**true';
>
>
> 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.user.DBUSER1',
> 'password');
>
> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_**PROPERTY('derby.database.**defaultConnectionMode',
> 'readOnlyAccess');
> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_**PROPERTY('derby.database.**fullAccessUsers',
> 'DBUSER1');
>
>
> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_**PROPERTY('derby.database.**
> propertiesOnly','false');
>
>
> connect 'jdbc:derby:memory:db;**shutdown=true';
>
> -- fails because authentication is on but no password is supplied
> connect 'jdbc:derby:memory:db;user=**DBUSER1';
>
> -- succeeds
> connect 'jdbc:derby:memory:db;user=**DBUSER1;password=password';
>
> -- succeeds
> call SYSCS_UTIL.SYSCS_BACKUP_**DATABASE( 'backups' ) ;
>
> A further comment inline...
>
>
> On 11/26/12 11:31 AM, Stefan R. wrote:
>
>> Hi,
>> in one of our web apps we activated derbys builtin user authentication
>> for the databases. We are using Derby 10.8.2.2
>>
>> The app is triggering a backup task, which executes
>> SYSCS_UTIL.SYSCS_BACKUP_**DATABASE over a JDBC connection. This throws
>> an exception saying: the connection could not be authorized.
>>
> This suggests that the app couldn't even establish a connection. The app
> never got to the step of attempting a backup. I would recommend
> double-checking the credentials.
>
> Hope this helps,
> -Rick
>
>
>> The following statements have been used to activate authentication:
>>
>> --------------------
>> 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.user.DBUSER1',
>> 'password);
>> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_**PROPERTY('derby.database.**defaultConnectionMode',
>> 'readOnlyAccess');
>> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_**PROPERTY('derby.database.**fullAccessUsers',
>> 'DBUSER1);
>>
>> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_**PROPERTY('derby.database.**
>> propertiesOnly','false');
>> --------------------
>>
>> So, DBUSER1 should have full access.
>>
>> Are there any restrictions to SYSCS_UTIL functions for fullAccessUsers?
>> How to authorize these?
>>
>> Thanks for your input,
>> Stefan
>>
>>
>
Re: Backup using SYSCS_UTIL.SYSCS_BACKUP_DATABASE with required authentication
Posted by Rick Hillegas <ri...@oracle.com>.
Hi Stefan,
I am not able to reproduce the behavior you are seeing. The following
script works fine for me when I use Derby 10.8.2.2:
connect 'jdbc:derby:memory:db;create=true';
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.user.DBUSER1',
'password');
CALL
SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.defaultConnectionMode',
'readOnlyAccess');
CALL
SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.fullAccessUsers',
'DBUSER1');
CALL
SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.propertiesOnly','false');
connect 'jdbc:derby:memory:db;shutdown=true';
-- fails because authentication is on but no password is supplied
connect 'jdbc:derby:memory:db;user=DBUSER1';
-- succeeds
connect 'jdbc:derby:memory:db;user=DBUSER1;password=password';
-- succeeds
call SYSCS_UTIL.SYSCS_BACKUP_DATABASE( 'backups' ) ;
A further comment inline...
On 11/26/12 11:31 AM, Stefan R. wrote:
> Hi,
> in one of our web apps we activated derbys builtin user authentication
> for the databases. We are using Derby 10.8.2.2
>
> The app is triggering a backup task, which executes
> SYSCS_UTIL.SYSCS_BACKUP_DATABASE over a JDBC connection. This throws
> an exception saying: the connection could not be authorized.
This suggests that the app couldn't even establish a connection. The app
never got to the step of attempting a backup. I would recommend
double-checking the credentials.
Hope this helps,
-Rick
>
> The following statements have been used to activate authentication:
>
> --------------------
> 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.user.DBUSER1',
> 'password);
> CALL
> SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.defaultConnectionMode',
> 'readOnlyAccess');
> CALL
> SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.fullAccessUsers',
> 'DBUSER1);
>
> CALL
> SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.propertiesOnly','false');
> --------------------
>
> So, DBUSER1 should have full access.
>
> Are there any restrictions to SYSCS_UTIL functions for
> fullAccessUsers? How to authorize these?
>
> Thanks for your input,
> Stefan
>