Quantcast

lose ownership on date base after set derby.database.sqlAuthorization to true

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

lose ownership on date base after set derby.database.sqlAuthorization to true

rsmon
Hi,
I have lose ownership on database after i have passed the command:  
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.sqlAuthorization', 'true');
I have restart the data base, and  then connect  on same user( TESTEUR)  i have created a table but i can't pass any command ( select or grant) i received the message:

Error code -1, SQL state 42506: L'utilisateur 'TESTEUR' n'est pas propriétaire de Table/View 'TESTEUR'.'CLIENT'.

Someone can help me?
Thank you.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: lose ownership on date base after set derby.database.sqlAuthorization to true

Bryan Pendleton-3
On 7/11/2016 6:07 AM, Régis Monfilliette wrote:

> |Hi,|
>
> |I have lose ownership on database after i have passed the command: |
>
> |CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.sqlAuthorization', 'true');|
>
> I have restart the data base, and  then connect  on same user( TESTEUR)  i have created a table but i can't pass any command ( select or grant) i received the message:
>
> Error code -1, SQL state 42506: L'utilisateur 'TESTEUR' n'est pas propriétaire de Table/View 'TESTEUR'.'CLIENT'.
>
> Someone can help me?
> Thank you.

Perhaps user TESTEUR wasn't actually the database owner?

https://db.apache.org/derby/docs/10.12/security/cseccsecuredbowner.html

Perhapps user APP is actually the database owner?

bryan

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: lose ownership on date base after set derby.database.sqlAuthorization to true

rsmon
Thank you, but i have created data database via Netbeans and i have created with username testeur
and be connected like shows this picture
 

if i pass  select * from TESTEUR.CLIENT
i obtain
Error code -1, SQL state 42502: L'utilisateur 'TESTEUR' ne dispose pas de droits SELECT sur la colonne 'NUMCLI' de la table 'TESTEUR'.'CLIENT'.

if i pass Grant select on TESTEUR.CLIENT to TESTEUR I obtain:

Error code -1, SQL state 42506: L'utilisateur 'TESTEUR' n'est pas propriétaire de Table/View 'TESTEUR'.'CLIENT'.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: lose ownership on date base after set derby.database.sqlAuthorization to true

Rick Hillegas-3
On 7/11/16 6:07 AM, rsmon wrote:

> Thank you, but i have created data database via Netbeans and i have created
> with username testeur
> and be connected like shows this picture
> <http://apache-database.10148.n7.nabble.com/file/n146431/BaseTest.png>
>
> if i pass  select * from TESTEUR.CLIENT
> i obtain
> Error code -1, SQL state 42502: L'utilisateur 'TESTEUR' ne dispose pas de
> droits SELECT sur la colonne 'NUMCLI' de la table 'TESTEUR'.'CLIENT'.
>
> if i pass Grant select on TESTEUR.CLIENT to TESTEUR I obtain:
>
> Error code -1, SQL state 42506: L'utilisateur 'TESTEUR' n'est pas
> propriétaire de Table/View 'TESTEUR'.'CLIENT'.
>
>
>
> --
> View this message in context: http://apache-database.10148.n7.nabble.com/lose-ownership-on-date-base-after-set-derby-database-sqlAuthorization-to-true-tp146429p146431.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>
Hi Régis,

What do you see when you issue this query:

   select * from sys.sysschemas where schemaName = 'TESTEUR';

Thanks,
-Rick
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: lose ownership on date base after set derby.database.sqlAuthorization to true

rsmon
Hi Ricks,

Thank you for your response.

Effectively when i execute select * from sys.sysschemas where schemaName = 'TESTEUR';
i obtain: 278b81cd-0155-d9ce-d86a-000012e9eb35 TESTEUR APP
The authorized user for the schema is not Testeur.
Yet, via netbeans services wizard, i have created the database BaseTestDroits with user name Testeur and a password  and the result is a database with shema Testeur ( an also App) but  the schema Testeur have app for authorized user. How can i connect as owner of database App?

I have succeded like this

1) create a database with the netbeans wizard BddTest1 without user but a passwd ( admin )
2) connect this data base with Netbeans services->databases->drivers-javadb(Network)->connect using
    set only the passwd admin

3)
    CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.user.uSCH', 'y');
    create schema SCH authorization uSCH;
    CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.sqlAuthorization','true');

4) RESTART  SGBD SERVER and  Connexion  uSCH/y"

5)  create table SCH.Client( numcli int primary key, nomcli varchar(30));
     insert into  SCH.Client values(1,'Dupont Jean');
     insert into  SCH.Client values(2,'Leroux Martin');
     grant select on SCH.Client to pierre;
 
6) Connect to database  without user but password admin

7) CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.user.pierre', 'z');

8) Connect with pierre/z

9) select * from SCH.CLIENT

      NUMCLI NOMCLI
          1 Dupont Jean
          2 Leroux Martin

 Regards
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: lose ownership on date base after set derby.database.sqlAuthorization to true

Rick Hillegas-3
Hi Régis,

More responses inline...

On 7/12/16 12:23 AM, rsmon wrote:
> Hi Ricks,
>
> Thank you for your response.
>
> Effectively when i execute select * from sys.sysschemas where schemaName =
> 'TESTEUR';
> i obtain: 278b81cd-0155-d9ce-d86a-000012e9eb35 TESTEUR APP
As you note, this indicates that the database is owned by APP. So you
need to connect as user APP in order to fix your permissions issues.

> The authorized user for the schema is not Testeur.
> Yet, via netbeans services wizard, i have created the database
> BaseTestDroits with user name Testeur and a password  and the result is a
> database with shema Testeur ( an also App) but  the schema Testeur have app
> for authorized user. How can i connect as owner of database App?
>
> I have succeded like this
>
> 1) create a database with the netbeans wizard BddTest1 without user but a
> passwd ( admin )
> 2) connect this data base with Netbeans
> services->databases->drivers-javadb(Network)->connect using
>      set only the passwd admin
>
> 3)
>      CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.user.uSCH', 'y');
>      create schema SCH authorization uSCH;
This indicates that you are using the BUILTIN authorization scheme,
which is administered via properties. We don't recommend BUILTIN
authentication any more because it can be subverted easily (as you are
about to see). Instead, we recommend using NATIVE authentication.

I don't know what password Netbeans established for the APP account.
Maybe Netbeans documents this password somewhere. If not, you may be
able to override the password with a system property, that is, one which
you set on the boot command line like this...

java -Dderby.user.APP=foo ...

Hope this helps,
-Rick

>      CALL
> SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.sqlAuthorization','true');
>
> 4) RESTART  SGBD SERVER and  Connexion  uSCH/y"
>
> 5)  create table SCH.Client( numcli int primary key, nomcli varchar(30));
>       insert into  SCH.Client values(1,'Dupont Jean');
>       insert into  SCH.Client values(2,'Leroux Martin');
>       grant select on SCH.Client to pierre;
>
> 6) Connect to database  without user but password admin
>
> 7) CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.user.pierre', 'z');
>
> 8) Connect with pierre/z
>
> 9) select * from SCH.CLIENT
>
>        NUMCLI NOMCLI
>            1 Dupont Jean
>            2 Leroux Martin
>
>   Regards
>
>
>
>
> --
> View this message in context: http://apache-database.10148.n7.nabble.com/lose-ownership-on-date-base-after-set-derby-database-sqlAuthorization-to-true-tp146429p146433.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: lose ownership on date base after set derby.database.sqlAuthorization to true

Bryan Pendleton-3
On 7/12/2016 7:01 PM, Rick Hillegas wrote:

>> Effectively when i execute select * from sys.sysschemas where schemaName ='TESTEUR';
>> i obtain: 278b81cd-0155-d9ce-d86a-000012e9eb35    TESTEUR    APP

> As you note, this indicates that the database is owned by APP.

Hi Rick,

In your opinion, is this a reliable way to ascertain the identity
of the database owner, at least in the scenarios in which you
can successfully connect to the database as *some* user?

If so, I wonder if we should add a suggestion about this to:

        https://db.apache.org/derby/docs/10.12/security/cseccsecuredbowner.html

This might make it easier for people to understand how their
database ownership behavior is arising?

thanks,

bryan



Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: lose ownership on date base after set derby.database.sqlAuthorization to true

rsmon
In reply to this post by Rick Hillegas-3
Hi Rick and Brian,

Thank you for your answers that  have help me to resolve my problem with database ownership.
The source of the problem is that the wizard of Netbeans for  create a database always set owner of database APP even if we give a user different user name .
In Pj a note ( sorry i have written it in in french ), perhaps useful for persons who will strike the same problem.


DerbyMiseEnPlaceDroitsDAcces.pdf
Regards
Regis
Loading...