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 rsmon <re...@wanadoo.fr> on 2016/07/11 13:07:21 UTC
Re: lose ownership on date base after set
derby.database.sqlAuthorization to true
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.
Re: lose ownership on date base after set
derby.database.sqlAuthorization to true
Posted by rsmon <re...@wanadoo.fr>.
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
<http://apache-database.10148.n7.nabble.com/file/n146440/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
Posted by Bryan Pendleton <bp...@gmail.com>.
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
Re: lose ownership on date base after set derby.database.sqlAuthorization
to true
Posted by Rick Hillegas <ri...@gmail.com>.
Hi Rgis,
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.
>
Re: lose ownership on date base after set
derby.database.sqlAuthorization to true
Posted by rsmon <re...@wanadoo.fr>.
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
--
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
Posted by Rick Hillegas <ri...@gmail.com>.
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