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.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.