db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: Turning on SQL authorization results in loss of table's ownership and permissions
Date Thu, 13 Mar 2014 18:57:27 GMT
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.
>


Mime
View raw message