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 authorization:
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',
'readOnlyAccess');
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 - 10.9.1.0 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.
