You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Wojciech Barej <w....@outlook.com> on 2014/03/13 11:21:09 UTC
Turning on SQL authorization results in loss of table's ownership
and permissions
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.
RE: Turning on SQL authorization results in loss of table's
ownership and permissions
Posted by Wojciech Barej <w....@outlook.com>.
Hi Rick,
I have run your script from within Netbeans (using built in SQL utilities) and it didn't work i.e. all schemas got set up to 'APP' authorizationID.I tried with and without password. No difference. I was informed, as previously, that I have not got permissions to run SELECT statement.
However when I created the database using ij as earlier on and then connected to that database from NetBeans, everything worked OK.I really suspect that somehow NetBeans sets its authorizationID to 'APP' regardless if you supply user name or not.
The only test I have left is trying to run CREATE SCHEMA in NetBeans after database creation and see if NetBeans works fine with SQL authorization then.
As for now I can only benefit from SQL authorization in JavaDB (Derby) if I create the database in ij and not NetBeans.
Kind regards,Wojciech
> Date: Fri, 14 Mar 2014 05:33:50 -0700
> From: rick.hillegas@oracle.com
> To: derby-user@db.apache.org
> Subject: Re: Turning on SQL authorization results in loss of table's ownership and permissions
>
> Hi Wojciech,
>
> Some comments inline...
>
> On 3/13/14 12:50 PM, Wojciech Barej wrote:
> > Hello Rick,
> >
> > Thank you for replying to my issue. It drives me crazy.
> >
> > I have done what you asked of me.
> >
> > Results:
> >
> > 1)
> >
> > SCHEMANAMEAUTHORIZATIONID
> >
> > APPAPP
> > NULLIDAPP
> > SAAPP
> > SQLJAPP
> > SYSAPP
> > SYSCATAPP
> > SYSCS_DIAGAPP
> > SYSCS_UTILAPP
> > SYSFUNAPP
> > SYSIBMAPP
> > SYSPROCAPP
> > SYSSTATAPP
> >
> > I can clearly see that the authorizationid is incorrect for my schema
> > 'SA'. Why?
> This tells us that the database was created in one of two ways. Either
>
> i) user was explicitly set to app
>
> ii) or no value was supplied for the user attribute and the default
> (app) was taken by Derby
> >
> > 2)
> >
> > I have successfully run your script from within ij with the results as
> > follows:
> >
> > SCHEMANAMEAUTHORIZATIONID
> >
> > APPAPP
> > NULLIDSA
> > SASA
> > SQLJSA
> > SYSSA
> > SYSCATSA
> > SYSCS_DIAGSA
> > SYSCS_UTILSA
> > SYSFUNSA
> > SYSIBMSA
> > SYSPROCSA
> > SYSSTATSA
> >
> > Here I can see a proper authorizationid values.
> > Also the SQL authorization worked properly here. The owner retained
> > its ownership and I could use SELECT statement.
> > Everything worked as supposed to.
> >
> > What is interesting here is that when I create a new database from
> > within NetBeans 7.3 and run a check:
> > select schemaName, authorizationID from sys.sysschemas
> > order by schemaName;
> >
> > I always have APP as an authorizationid value for every schema even
> > one created by me.
> >
> > I checked your script and the only difference I can spot at the moment
> > is that you didn't supply password when creating the database for
> > user 'sa' whereas I do specify it in NetBeans.
> >
> > I also think I run a check before from ij as well and I executed the
> > statement like this:
> >
> > connect 'jdbc:derby:memory:db;create=true;user=sa;password=sa';
> >
> > and I also had problems with the SQL authorization.
> >
> > Is it possible that supplying password during the database creation
> > before the authentication (derby.connection.requireAuthentication) is
> > switched on makes Derby assigning incorrect authorization ids?
> I added a password to the creation url. That does not affect the
> results. The database is still owned by sa. My suspicion is that the
> database is not being created when you think it is. Since this only
> happens under Netbeans, my guess would be that Netbeans is proactively
> creating the database without supplying a user name. If I were tackling
> this problem, I would look for a way to tell Netbeans not to do that.
>
> Maybe we will get some comments from someone who understands Netbeans
> better than I do.
>
> Hope this helps,
> -Rick
> >
> > Thanks,
> > Wojciech
> >
> >
> >
> >
> >
> > > Date: Thu, 13 Mar 2014 11:57:27 -0700
> > > From: rick.hillegas@oracle.com
> > > To: derby-user@db.apache.org
> > > Subject: Re: Turning on SQL authorization results in loss of table's
> > ownership and permissions
> > >
> > > 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.
> > > >
> > >
>
Re: Turning on SQL authorization results in loss of table's ownership
and permissions
Posted by Dyre Tjeldvoll <Dy...@oracle.com>.
Hi guys, some comments inline:
On 03/14/2014 01:33 PM, Rick Hillegas wrote:
> Hi Wojciech,
>
> Some comments inline...
>
> On 3/13/14 12:50 PM, Wojciech Barej wrote:
>> Hello Rick,
>>
>> Thank you for replying to my issue. It drives me crazy.
>>
>> I have done what you asked of me.
>>
>> Results:
>>
>> 1)
>>
>> SCHEMANAMEAUTHORIZATIONID
>>
>> APPAPP
>> NULLIDAPP
>> SAAPP
>> SQLJAPP
>> SYSAPP
>> SYSCATAPP
>> SYSCS_DIAGAPP
>> SYSCS_UTILAPP
>> SYSFUNAPP
>> SYSIBMAPP
>> SYSPROCAPP
>> SYSSTATAPP
>>
>> I can clearly see that the authorizationid is incorrect for my schema
>> 'SA'. Why?
> This tells us that the database was created in one of two ways. Either
>
> i) user was explicitly set to app
>
> ii) or no value was supplied for the user attribute and the default
> (app) was taken by Derby
>>
>> 2)
>>
>> I have successfully run your script from within ij with the results as
>> follows:
>>
>> SCHEMANAMEAUTHORIZATIONID
>>
>> APPAPP
>> NULLIDSA
>> SASA
>> SQLJSA
>> SYSSA
>> SYSCATSA
>> SYSCS_DIAGSA
>> SYSCS_UTILSA
>> SYSFUNSA
>> SYSIBMSA
>> SYSPROCSA
>> SYSSTATSA
>>
>> Here I can see a proper authorizationid values.
>> Also the SQL authorization worked properly here. The owner retained
>> its ownership and I could use SELECT statement.
>> Everything worked as supposed to.
>>
>> What is interesting here is that when I create a new database from
>> within NetBeans 7.3 and run a check:
>> select schemaName, authorizationID from sys.sysschemas
>> order by schemaName;
>>
>> I always have APP as an authorizationid value for every schema even
>> one created by me.
>>
>> I checked your script and the only difference I can spot at the moment
>> is that you didn't supply password when creating the database for
>> user 'sa' whereas I do specify it in NetBeans.
>>
>> I also think I run a check before from ij as well and I executed the
>> statement like this:
>>
>> connect 'jdbc:derby:memory:db;create=true;user=sa;password=sa';
>>
>> and I also had problems with the SQL authorization.
>>
>> Is it possible that supplying password during the database creation
>> before the authentication (derby.connection.requireAuthentication) is
>> switched on makes Derby assigning incorrect authorization ids?
> I added a password to the creation url. That does not affect the
> results. The database is still owned by sa. My suspicion is that the
> database is not being created when you think it is. Since this only
> happens under Netbeans, my guess would be that Netbeans is proactively
> creating the database without supplying a user name. If I were tackling
> this problem, I would look for a way to tell Netbeans not to do that.
>
> Maybe we will get some comments from someone who understands Netbeans
> better than I do.
I tried creating a database in Netbeans. What I see is that
1) It comes with a default database ("sample") with authid APP
2) When trying to create a database it defaults to the network driver
3) You can expand the "Drivers" node and left-click "Java DB
(Embedded)", and select "Connect using". Then you get a wizard where you
enter username and password, and there is a button for setting
properties. You then have to add connect=true to the set of properties
to create the database.
When pressing "Next" you are prompted for the schema to use. But the
schema corresponding to the new username you chose is not yet available
- presumably because it is created on demand, and APP is the default
instead.
If you then choose a different schema, like "SYSIBM", and run a create
table command using that connection, you can expand the "Other schemas"
node and see a schema with same name as the user you connected as.
If you now create a new connection (by left-clicking on the "Java DB
(Embedded Driver)" node), and repeat the steps above (except that the
create property is no longer needed) you now get the schema
corresponding to the user name as the default. Moreover, by expanding
the node this schema you can see the table you created using the first
connection, and there is no new table in SYSIBM.
I suspect that the Netbeans wizard was written for a number of different
databases and drivers, many of which probably do this differently. As a
result there is a certain impedance mismatch between the wizard
interface and what actually happens inside Derby.
<snip>
--
Regards,
Dyre
Re: Turning on SQL authorization results in loss of table's ownership
and permissions
Posted by Rick Hillegas <ri...@oracle.com>.
Hi Wojciech,
Some comments inline...
On 3/13/14 12:50 PM, Wojciech Barej wrote:
> Hello Rick,
>
> Thank you for replying to my issue. It drives me crazy.
>
> I have done what you asked of me.
>
> Results:
>
> 1)
>
> SCHEMANAMEAUTHORIZATIONID
>
> APPAPP
> NULLIDAPP
> SAAPP
> SQLJAPP
> SYSAPP
> SYSCATAPP
> SYSCS_DIAGAPP
> SYSCS_UTILAPP
> SYSFUNAPP
> SYSIBMAPP
> SYSPROCAPP
> SYSSTATAPP
>
> I can clearly see that the authorizationid is incorrect for my schema
> 'SA'. Why?
This tells us that the database was created in one of two ways. Either
i) user was explicitly set to app
ii) or no value was supplied for the user attribute and the default
(app) was taken by Derby
>
> 2)
>
> I have successfully run your script from within ij with the results as
> follows:
>
> SCHEMANAMEAUTHORIZATIONID
>
> APPAPP
> NULLIDSA
> SASA
> SQLJSA
> SYSSA
> SYSCATSA
> SYSCS_DIAGSA
> SYSCS_UTILSA
> SYSFUNSA
> SYSIBMSA
> SYSPROCSA
> SYSSTATSA
>
> Here I can see a proper authorizationid values.
> Also the SQL authorization worked properly here. The owner retained
> its ownership and I could use SELECT statement.
> Everything worked as supposed to.
>
> What is interesting here is that when I create a new database from
> within NetBeans 7.3 and run a check:
> select schemaName, authorizationID from sys.sysschemas
> order by schemaName;
>
> I always have APP as an authorizationid value for every schema even
> one created by me.
>
> I checked your script and the only difference I can spot at the moment
> is that you didn't supply password when creating the database for
> user 'sa' whereas I do specify it in NetBeans.
>
> I also think I run a check before from ij as well and I executed the
> statement like this:
>
> connect 'jdbc:derby:memory:db;create=true;user=sa;password=sa';
>
> and I also had problems with the SQL authorization.
>
> Is it possible that supplying password during the database creation
> before the authentication (derby.connection.requireAuthentication) is
> switched on makes Derby assigning incorrect authorization ids?
I added a password to the creation url. That does not affect the
results. The database is still owned by sa. My suspicion is that the
database is not being created when you think it is. Since this only
happens under Netbeans, my guess would be that Netbeans is proactively
creating the database without supplying a user name. If I were tackling
this problem, I would look for a way to tell Netbeans not to do that.
Maybe we will get some comments from someone who understands Netbeans
better than I do.
Hope this helps,
-Rick
>
> Thanks,
> Wojciech
>
>
>
>
>
> > Date: Thu, 13 Mar 2014 11:57:27 -0700
> > From: rick.hillegas@oracle.com
> > To: derby-user@db.apache.org
> > Subject: Re: Turning on SQL authorization results in loss of table's
> ownership and permissions
> >
> > 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.
> > >
> >
RE: Turning on SQL authorization results in loss of table's
ownership and permissions
Posted by Wojciech Barej <w....@outlook.com>.
Hello Rick,
Thank you for replying to my issue. It drives me crazy.
I have done what you asked of me.
Results:
1)
SCHEMANAME AUTHORIZATIONID
APP APPNULLID APPSA APPSQLJ APPSYS APPSYSCAT APPSYSCS_DIAG APPSYSCS_UTIL APPSYSFUN APPSYSIBM APPSYSPROC APPSYSSTAT APP
I can clearly see that the authorizationid is incorrect for my schema 'SA'. Why?
2)
I have successfully run your script from within ij with the results as follows:
SCHEMANAME AUTHORIZATIONID
APP APPNULLID SASA SASQLJ SASYS SASYSCAT SASYSCS_DIAG SASYSCS_UTIL SASYSFUN SASYSIBM SASYSPROC SASYSSTAT SA
Here I can see a proper authorizationid values. Also the SQL authorization worked properly here. The owner retained its ownership and I could use SELECT statement.Everything worked as supposed to.
What is interesting here is that when I create a new database from within NetBeans 7.3 and run a check:select schemaName, authorizationID from sys.sysschemas
order by schemaName;
I always have APP as an authorizationid value for every schema even one created by me.
I checked your script and the only difference I can spot at the moment is that you didn't supply password when creating the database for user 'sa' whereas I do specify it in NetBeans.
I also think I run a check before from ij as well and I executed the statement like this:
connect 'jdbc:derby:memory:db;create=true;user=sa;password=sa';
and I also had problems with the SQL authorization.
Is it possible that supplying password during the database creation before the authentication (derby.connection.requireAuthentication) is switched on makes Derby assigning incorrect authorization ids?
Thanks,Wojciech
> Date: Thu, 13 Mar 2014 11:57:27 -0700
> From: rick.hillegas@oracle.com
> To: derby-user@db.apache.org
> Subject: Re: Turning on SQL authorization results in loss of table's ownership and permissions
>
> 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.
> >
>
Re: Turning on SQL authorization results in loss of table's ownership
and permissions
Posted by Rick Hillegas <ri...@oracle.com>.
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.
>