Re: Turning on SQL authorization results in loss of table's ownership and permissions

Thu, 13 Mar 2014 11:59:22 -0700 

Hi Wojciech,
I am not able to reproduce your results. I am including a script which tries to capture your experiment. This script works for me both on the development trunk and on 10.9.1.0. 

I have a couple questions:

1) Does this script work for you?


2) What is the output of the following query on the database where you 
see the problem:


select schemaName, authorizationID from sys.sysschemas
order by schemaName;

Here is the script:

connect 'jdbc:derby:memory:db;create=true;user=sa';

-- turn on authentication

call syscs_util.syscs_set_database_property( 
'derby.connection.requireAuthentication', 'true' );


-- create users

call syscs_util.syscs_set_database_property( 'derby.user.normal', 
'normalpassword' );
call syscs_util.syscs_set_database_property( 'derby.user.sa', 
'sapassword' );


-- enable coarse-grained authorization limits

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' );


-- load some data
create table t( a int );
insert into t values ( 1 );

-- bounce the database in order to enable the property settings
connect 'jdbc:derby:memory:db;shutdown=true';

-- log in the read-only user
connect 'jdbc:derby:memory:db;user=normal;password=normalpassword';

-- works fine
select * from sa.t;

-- this user is not allowed to create tables
create table s( a int );

-- log in the dbo
connect 'jdbc:derby:memory:db;user=sa;password=sapassword';

-- works fine
select * from t;

-- turn on sql authorization

call syscs_util.syscs_set_database_property( 
'derby.database.sqlAuthorization', 'true' );

connect 'jdbc:derby:memory:db;shutdown=true;user=sa;password=sapassword';

-- verify that the dbo still has the expected permissions
connect 'jdbc:derby:memory:db;user=sa;password=sapassword';
select * from t;

select schemaName, authorizationID from sys.sysschemas
order by schemaName;


-- with sql authorization enabled, this user cannot select from a table 
owned by the dbo

connect 'jdbc:derby:memory:db;user=normal;password=normalpassword';
select * from sa.t;


Thanks,
-Rick



On 3/13/14 3:21 AM, Wojciech Barej wrote:

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.

























					Reply via email to