db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dyre Tjeldvoll <Dyre.Tjeldv...@oracle.com>
Subject Re: Turning on SQL authorization results in loss of table's ownership and permissions
Date Mon, 17 Mar 2014 08:35:53 GMT
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

Mime
View raw message