Re: SQLAuthorisation and role permissions
here is the script which can be executed to reproduce the problem
-- create an embedded database that is encrypted and
-- specify user 'derby' when initially creating the database so this user
-- will be or can become the database owner
connect 'jdbc:derby:SecuredDB;create=true;
dataEncryption=true;bootPassword=encryption;user=derby';
-- create a table without granting permissions on it to anyone so that
-- when security set-up is in place only the
-- database owner should be able to query this table
CREATE SCHEMA RTE;
-- no grant seems to be required in derby to allow schema access
CREATE TABLE RTE.SecuredTable(
ColumnA integer NOT NULL,
ColumnB varchar(10) NOT NULL,
ColumnC varchar(60) NOT NULL,
ColumnD date,
CONSTRAINT PK_SecuredTable PRIMARY KEY (ColumnA));
-- insert some sample data into the table while being logged in
-- as database owner
INSERT INTO RTE.SecuredTable (ColumnA, ColumnB, ColumnC)
VALUES (1, 'aaa', 'bbb');
INSERT INTO RTE.SecuredTable (ColumnA, ColumnB, ColumnC)
VALUES (2, '111', '222');
-- create a view on which authorisations will be granted (or not)
CREATE VIEW RTE.SecureView AS
SELECT ColumnA, ColumnB FROM RTE.SecuredTable;
-- set-up security mechanisms (authentication, SQL authorisation)
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(
'derby.connection.requireAuthentication', 'true');
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(
'derby.authentication.provider', 'BUILTIN');
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.user.derby', 'derby');
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(
'derby.database.fullAccessUsers', 'derby');
-- this property is derby specific and should not be used when standard SQL
-- authorisation is used
--CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(
--'derby.database.defaultConnectionMode', 'noAccess');
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(
'derby.database.sqlAuthorization', 'true');
-- prevent ability to overwrite security settings made
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(
'derby.database.propertiesOnly', 'true');
-- shutdown data base for the static settings to be activated on next start-up
connect 'jdbc:derby:SecuredDB;bootPassword=encryption;
user=derby;password=derby;shutdown=true;';
-- set-up (application specific) roles and SQL authorisations
-- login again using data base owner login and continue with granting
-- permissions
connect 'jdbc:derby:SecuredDB;bootPassword=encryption;
user=derby;password=derby';
CREATE ROLE reader;
-- this should also grant select permissions on the underlying base table
-- for the columns included in the view
GRANT SELECT ON RTE.SecureView TO reader;
-- maintain user information
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(
'derby.user.tho...@xy.de', 'th');
GRANT reader TO tho...@xy.de;
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(
'derby.user.b...@xy.com', 'b');
disconnect;
-- trying to connect to the database with an undefined user should not be
-- possible and return an error
connect 'jdbc:derby:SecuredDB;bootPassword=encryption;
user=a...@xy.com;password=x';
-- connect to database with a user that is allowed to connect
connect 'jdbc:derby:SecuredDB;bootPassword=encryption;
user=b...@xy.com;password=b';
-- try selecting data from a view on which user has no permission
-- (no data to be returned)
select * from RTE.SecureView;
disconnect;
-- connect to the database with user holding read permission on the view
-- via his role assignment
connect 'jdbc:derby:SecuredDB;bootPassword=encryption;
user=tho...@xy.de;password=th';
-- data should be returned
select * from RTE.SecureView;
-- no data returned which is strange and possibly a bug
disconnect;
-- explicitely grant select permissions to user (rather than role)
connect 'jdbc:derby:SecuredDB;bootPassword=encryption;
user=derby;password=derby';
GRANT SELECT ON RTE.SecureView TO tho...@xy.de;
disconnect;
connect 'jdbc:derby:SecuredDB;bootPassword=encryption;
user=tho...@xy.de;password=th';
-- only after the explicite grant to the user data is returned
select * from RTE.SecureView;
disconnect;
exit;
Re: can varchar for bit data size limit be exceeded?
On Wed, Apr 21, 2010 at 4:00 PM, Knut Anders Hatlen knut.hat...@sun.com wrote: On 04/21/10 10:17 AM, George H wrote: Thanks for the extra info. I guess I am still stuck. I have then another question, how does one insert large BLOBs (ie., 1MB size) into derby just from the IJ console? I know we shouldn't compare derby to mysql but with mysql I can insert bytes into a BLOB column by forming a very very very long SQL insert statement and just place the bytes in there or put them in hex format. Doesn't derby have a facility to do something like this? or is a prepared statement the only way to go about this? I don't think there is a way to do this from IJ. Except, perhaps, if you could live with reading the contents of the BLOB from a file with the import procedures (http://db.apache.org/derby/docs/10.5/tools/ctoolsimport16245.html). -- Knut Anders OK, I guess I could look into that and see what I can make of it. Thanks for helping me out. -- George H george@gmail.com
Re: SQLAuthorisation and role permissions
On 04/21/10 11:06 PM, Thomas wrote: Hi, I have trouble understanding why the following doesn't work as expected: I have secured an embedded data base by requiring authenification and using SQL Authorisation. Authentification is working as I expected; SQL Authorisation not or only partially. When loggin in as database owner and granting select permission on a view to a user X, then loggin off and loggin back in as user X = data can be selected from the view as expected. However when creating a role, granting select permission to that role (for all columns on the view, so no colum list specified) and then granting the role to user X - then, when logging in a user X, I am getting an error that user X doesn't have select permission on the first column in the view. My expectation is that user X via having been granted the role should be able to select data from the view Hi Thomas, The user also needs to assume the role, using the SET ROLE statement, in order to get the privileges granted to the role. http://db.apache.org/derby/docs/10.5/ref/rrefsetrole.html -- Knut Anders
Re: SQLAuthorisation and role permissions
Thanks for providing the script! It is very clear. As Knut Anders suggested, if I add 'set role reader;' at line 96 of the script, user Thomas is then able to select from SecureView. thanks, bryan
Derby: ODBC: Access Problem
Hi, This is my first mail within Derby Community. And need some help. Trying to, Use Derby 10.5.3. tru ODBC access (unixODBC is the driver manager) Using IBM CLIdriver When I invoked the tool to test the connectivity, isql -v TEST, It is throwing below error, [08001][unixODBC][IBM][CLI Driver] SQL1013N The database alias name or database name TEST could not be found. SQLSTATE=42705 Can anyone please suggest possible workarounds or insights into this problem. .odbc.ini file, [TEST] Driver = /opt/driver/db2/lib/libdb2.so Server=localhost:1527 Database=test dbalias=test DBNAME=test Database Name=test Protocol= tcpip Hostname= localhost SERVER = 127.0.0.1 PORT= 1527 SysSchema = firstdb SchemaList = 'firstdb' UsageCount = 5 CurrentSchema=firstdb Thanks, Satish.
performance: Derby vs H2
Is it really true that H2 is faster than Derby?? http://en.wikipedia.org/wiki/Apache_Derby A year ago, I tried to remove the section that says that H2 is faster, but someone always added it back into the article. And besides me, seems like no one really care about the Comparison to other embedded SQL Java databases section. http://en.wikipedia.org/wiki/Talk:Apache_Derby#Benchmarks Is it a well-known fact that H2 is always faster?? And there is also H2's benchmark page: http://www.h2database.com/html/performance.html Is it a fair comparsion?? Rayson
Re: performance: Derby vs H2
It is a unconstructive question to ask. It depends on many requirements and situations. Is a car faster than an airplane ? Well if I have to go to the next corner shopping mall to pick up some milk your forgot, this is most likely the case. So try it out - and if you think one is faster than the other for your particular situation - go for it. For me the maturity, support, proven deployments and scalability together with features available are the key driving forces. B-) On Thu, Apr 22, 2010 at 11:47 AM, Rayson Ho raysonlo...@gmail.com wrote: Is it really true that H2 is faster than Derby?? http://en.wikipedia.org/wiki/Apache_Derby A year ago, I tried to remove the section that says that H2 is faster, but someone always added it back into the article. And besides me, seems like no one really care about the Comparison to other embedded SQL Java databases section. http://en.wikipedia.org/wiki/Talk:Apache_Derby#Benchmarks Is it a well-known fact that H2 is always faster?? And there is also H2's benchmark page: http://www.h2database.com/html/performance.html Is it a fair comparsion?? Rayson
Re: performance: Derby vs H2
Rayson, some/most of us are looking for best performance AND best stability/scalability/tools/etc. When I look for performance I usually go with Berkeley DB JE ;-) On Thu, Apr 22, 2010 at 7:14 PM, Rayson Ho raysonlo...@gmail.com wrote: On Thu, Apr 22, 2010 at 12:06 PM, bruehlicke bruehli...@gmail.com wrote: It is a unconstructive question to ask. It depends on many requirements and situations. I guess I just got an unconstructive answer from you :-D I am trying to find out whether the benchmarks they used are fair, and whether the Derby developers know that H2 is faster. Even with unfair benchmarks that make H2 really shine, can't Derby learn from H2?? And no, I don't have an application that I need to choose between Derby or H2. Rayson Is a car faster than an airplane ? Well if I have to go to the next corner shopping mall to pick up some milk your forgot, this is most likely the case. So try it out - and if you think one is faster than the other for your particular situation - go for it. For me the maturity, support, proven deployments and scalability together with features available are the key driving forces. B-) On Thu, Apr 22, 2010 at 11:47 AM, Rayson Ho raysonlo...@gmail.com wrote: Is it really true that H2 is faster than Derby?? http://en.wikipedia.org/wiki/Apache_Derby A year ago, I tried to remove the section that says that H2 is faster, but someone always added it back into the article. And besides me, seems like no one really care about the Comparison to other embedded SQL Java databases section. http://en.wikipedia.org/wiki/Talk:Apache_Derby#Benchmarks Is it a well-known fact that H2 is always faster?? And there is also H2's benchmark page: http://www.h2database.com/html/performance.html Is it a fair comparsion?? Rayson
Re: performance: Derby vs H2
On Thu, Apr 22, 2010 at 12:24 PM, Peter Ondruška peter.ondru...@gmail.com wrote: Rayson, some/most of us are looking for best performance AND best stability/scalability/tools/etc. Thanks for the reply, Peter. By stability, you mean ACID kind of stability?? Rayson When I look for performance I usually go with Berkeley DB JE ;-) On Thu, Apr 22, 2010 at 7:14 PM, Rayson Ho raysonlo...@gmail.com wrote: On Thu, Apr 22, 2010 at 12:06 PM, bruehlicke bruehli...@gmail.com wrote: It is a unconstructive question to ask. It depends on many requirements and situations. I guess I just got an unconstructive answer from you :-D I am trying to find out whether the benchmarks they used are fair, and whether the Derby developers know that H2 is faster. Even with unfair benchmarks that make H2 really shine, can't Derby learn from H2?? And no, I don't have an application that I need to choose between Derby or H2. Rayson Is a car faster than an airplane ? Well if I have to go to the next corner shopping mall to pick up some milk your forgot, this is most likely the case. So try it out - and if you think one is faster than the other for your particular situation - go for it. For me the maturity, support, proven deployments and scalability together with features available are the key driving forces. B-) On Thu, Apr 22, 2010 at 11:47 AM, Rayson Ho raysonlo...@gmail.com wrote: Is it really true that H2 is faster than Derby?? http://en.wikipedia.org/wiki/Apache_Derby A year ago, I tried to remove the section that says that H2 is faster, but someone always added it back into the article. And besides me, seems like no one really care about the Comparison to other embedded SQL Java databases section. http://en.wikipedia.org/wiki/Talk:Apache_Derby#Benchmarks Is it a well-known fact that H2 is always faster?? And there is also H2's benchmark page: http://www.h2database.com/html/performance.html Is it a fair comparsion?? Rayson
Re: performance: Derby vs H2
Not really. By stability I mean being happy Derby user since its Cloudscape ages.. On Thu, Apr 22, 2010 at 7:45 PM, Rayson Ho raysonlo...@gmail.com wrote: On Thu, Apr 22, 2010 at 12:24 PM, Peter Ondruška peter.ondru...@gmail.com wrote: Rayson, some/most of us are looking for best performance AND best stability/scalability/tools/etc. Thanks for the reply, Peter. By stability, you mean ACID kind of stability?? Rayson When I look for performance I usually go with Berkeley DB JE ;-) On Thu, Apr 22, 2010 at 7:14 PM, Rayson Ho raysonlo...@gmail.com wrote: On Thu, Apr 22, 2010 at 12:06 PM, bruehlicke bruehli...@gmail.com wrote: It is a unconstructive question to ask. It depends on many requirements and situations. I guess I just got an unconstructive answer from you :-D I am trying to find out whether the benchmarks they used are fair, and whether the Derby developers know that H2 is faster. Even with unfair benchmarks that make H2 really shine, can't Derby learn from H2?? And no, I don't have an application that I need to choose between Derby or H2. Rayson Is a car faster than an airplane ? Well if I have to go to the next corner shopping mall to pick up some milk your forgot, this is most likely the case. So try it out - and if you think one is faster than the other for your particular situation - go for it. For me the maturity, support, proven deployments and scalability together with features available are the key driving forces. B-) On Thu, Apr 22, 2010 at 11:47 AM, Rayson Ho raysonlo...@gmail.com wrote: Is it really true that H2 is faster than Derby?? http://en.wikipedia.org/wiki/Apache_Derby A year ago, I tried to remove the section that says that H2 is faster, but someone always added it back into the article. And besides me, seems like no one really care about the Comparison to other embedded SQL Java databases section. http://en.wikipedia.org/wiki/Talk:Apache_Derby#Benchmarks Is it a well-known fact that H2 is always faster?? And there is also H2's benchmark page: http://www.h2database.com/html/performance.html Is it a fair comparsion?? Rayson
Stopping NetworkServerControl
Sorry if this is obvious, but I can't seem to figure it out. I know how to start a network listener for an embedded database: new NetworkServerControl().start(writer); but how do you stop it? There is no stop method on NetworkServerControl... Thanks! David -- David W. Van Couvering http://www.linkedin.com/in/davidvc http://davidvancouvering.blogspot.com http://twitter.com/dcouvering
Re: performance: Derby vs H2
I think the best authority on this is Thomas Mueller (father of h2) who has actually done some performance comparisons between databases. I remember reading some debate about this on some discussion groups a year or two ago. If you want peer reviewed scientific knowledge then you probably should run those tests yourself. I have seen sometimes Sun employees referring on this list to some sort of benchmarks for derby. But I have not found anything public from google. I think the countless benchmarks published by different database companies have proven that performance is such a complex issue that any type of result can be manufactured. However when done well and extensively they can give some overall understanding. Because H2 has only one developer the code is a bit more straightforward and simpler than Derby that has a rich and long history as a codebase. Therefore I think that H2 is a tad cleaner and faster than Derby in basic operation. Both databases have queries that are very slow. One could call them bugs. - rami On 22.4.2010 19:47, Rayson Ho wrote: Is it really true that H2 is faster than Derby?? http://en.wikipedia.org/wiki/Apache_Derby A year ago, I tried to remove the section that says that H2 is faster, but someone always added it back into the article. And besides me, seems like no one really care about the Comparison to other embedded SQL Java databases section. http://en.wikipedia.org/wiki/Talk:Apache_Derby#Benchmarks Is it a well-known fact that H2 is always faster?? And there is also H2's benchmark page: http://www.h2database.com/html/performance.html Is it a fair comparsion?? Rayson
Re: Stopping NetworkServerControl
On 4/22/2010 12:37 PM, David Van Couvering wrote: Sorry if this is obvious, but I can't seem to figure it out. I know how to start a network listener for an embedded database: new NetworkServerControl().start(writer); but how do you stop it? There is no stop method on NetworkServerControl... The method is shutdown() rather than stop
Re: Stopping NetworkServerControl
You have to use something like this:
if(embeddedConn != null)
embeddedConn.close();
try
{
// shutdown Derby Network Server
*DriverManager.getConnection(jdbc:derby:;shutdown=true);*
}
catch(SQLException se)
{
//ignore se
}
Just create a connection with a jdbc:derby:;shutdown=true url
Maximo
On Thu, Apr 22, 2010 at 4:37 PM, David Van Couvering da...@vancouvering.com
wrote:
Sorry if this is obvious, but I can't seem to figure it out. I know how to
start a network listener for an embedded database:
new NetworkServerControl().start(writer);
but how do you stop it? There is no stop method on NetworkServerControl...
Thanks!
David
--
David W. Van Couvering
http://www.linkedin.com/in/davidvc
http://davidvancouvering.blogspot.com
http://twitter.com/dcouvering
Re: SQLAuthorisation and role permissions
Thanks a lot for the help. Now everything is working fine. Regards Thomas
Re: performance: Derby vs H2
Speed is important, but so is stability. H2 does not have anywhere near the track record of Derby. There are hundreds of thousands of Derby installations running 24/7. I have used Derby for years, as have my customers, in mission critical applications and I have never experienced a corruption or failure. Unless you are doing something truly advanced Derby is fast enough. For those other cases, H2 would also be unsuitable. You would need something like PostgreSQL or Oracle. Brett Sent from my iPhone On Apr 23, 2010, at 4:44, Rami Ojares rami.oja...@pdf-comics.com wrote: I think the best authority on this is Thomas Mueller (father of h2) who has actually done some performance comparisons between databases. I remember reading some debate about this on some discussion groups a year or two ago. If you want peer reviewed scientific knowledge then you probably should run those tests yourself. I have seen sometimes Sun employees referring on this list to some sort of benchmarks for derby. But I have not found anything public from google. I think the countless benchmarks published by different database companies have proven that performance is such a complex issue that any type of result can be manufactured. However when done well and extensively they can give some overall understanding. Because H2 has only one developer the code is a bit more straightforward and simpler than Derby that has a rich and long history as a codebase. Therefore I think that H2 is a tad cleaner and faster than Derby in basic operation. Both databases have queries that are very slow. One could call them bugs. - rami On 22.4.2010 19:47, Rayson Ho wrote: Is it really true that H2 is faster than Derby?? http://en.wikipedia.org/wiki/Apache_Derby A year ago, I tried to remove the section that says that H2 is faster, but someone always added it back into the article. And besides me, seems like no one really care about the Comparison to other embedded SQL Java databases section. http://en.wikipedia.org/wiki/Talk:Apache_Derby#Benchmarks Is it a well-known fact that H2 is always faster?? And there is also H2's benchmark page: http://www.h2database.com/html/performance.html Is it a fair comparsion?? Rayson
Re: Stopping NetworkServerControl
Hm, I don't want to shut down the database, it may be in use. I just want
to stop the network listener. Is that not possible?
David
2010/4/22 Máximo Castillo maximo.j.casti...@gmail.com
You have to use something like this:
if(embeddedConn != null)
embeddedConn.close();
try
{
// shutdown Derby Network Server
*DriverManager.getConnection(jdbc:derby:;shutdown=true);*
}
catch(SQLException se)
{
//ignore se
}
Just create a connection with a jdbc:derby:;shutdown=true url
Maximo
On Thu, Apr 22, 2010 at 4:37 PM, David Van Couvering
da...@vancouvering.com wrote:
Sorry if this is obvious, but I can't seem to figure it out. I know how
to start a network listener for an embedded database:
new NetworkServerControl().start(writer);
but how do you stop it? There is no stop method on
NetworkServerControl...
Thanks!
David
--
David W. Van Couvering
http://www.linkedin.com/in/davidvc
http://davidvancouvering.blogspot.com
http://twitter.com/dcouvering
--
David W. Van Couvering
http://www.linkedin.com/in/davidvc
http://davidvancouvering.blogspot.com
http://twitter.com/dcouvering
Re: Stopping NetworkServerControl
On 04/23/10 01:08, David Van Couvering wrote: Hm, I don't want to shut down the database, it may be in use. I just want to stop the network listener. Is that not possible? Hi David, NetworkServerControl.shutdown() only shuts down the network server. The databases will still be booted. There is one exception: If you started the network server from the command line, it will shut down the engine too. But in that case we know that the network server process is going to terminate, so no one will be able to access the databases in that process anyway. -- Knut Anders
