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

[rsmon]

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



--
View this message in context: 
http://apache-database.10148.n7.nabble.com/lose-ownership-on-date-base-after-set-derby-database-sqlAuthorization-to-true-tp146429p146440.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

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

[Bryan Pendleton]

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-12e9eb35TESTEURAPP



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

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

[Rick Hillegas]

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-12e9eb35  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
   1Dupont Jean
   2Leroux 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.

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-12e9eb35  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

  NUMCLINOMCLI
  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.

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

[Rick Hillegas]

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


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

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'.



--
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.

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

[Bryan Pendleton]

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