db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Wojciech Barej <w.ba...@outlook.com>
Subject Turning on SQL authorization results in loss of table's ownership and permissions
Date Thu, 13 Mar 2014 10:21:09 GMT
Dear All,
I have a following problem with SQL authorization:
The database created in Java DB (Derby) was set-up as follows to allow authentication and
    CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.connection.requireAuthentication','true');
    CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.user.normal', 'normal');        CALL
SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.fullAccessUsers', 'sa');    CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.readOnlyAccessUsers',
'normal');    CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.defaultConnectionMode',
The "sa" username was created during database creation so it is the owner of the database.
And this works as intended. I can log in as "sa" user and have full access. Or log in as "normal"
users and be restricted to read only access.

Now, I want to use SQL authorization to grant specific permissions to specific users.To do
this I have to switch on SQL authorization first by executing following command:
    CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.sqlAuthorization', 'true');
Problem is, that after login in again under "sa" the system reports that I have no rights
for SELECT and other statements. Moreover I loose complete ownership on the database.
Why Derby suddenly denies access to any user including the owner after executing the statement
that switches on the SQL authorization?
P.S. I use Apache Derby Network Server - which was a part of Java EE 7 installation
for NetBeans 7.3
P.S 2. When after SQL authorization is set to true I try to use GRANT statement I receive
following SQL error code:
    SQL state 42506: User 'SA' is not the owner of Table/View 'SA'.'DOCTYPES'.
Even though the whole database was created using this username.
View raw message