Re: SQLAuthorisation and role permissions

[Thomas]

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;