SQLAuthorisation and role permissions

[Thomas <Th...@t-online.de>]

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.

Can anyone please advice what I am doing wrong or what I am misunderstanding?

If needed, I could post all SQL statements to create sample objects, users,
roles, permissions, ....

Note: I have also checked system table SYS.SYSTABLEPERMS and found an
entry for the role defined to hold the permission granted on that view.

When explicitely granting select permission to the user, then I can
select data as expected - but obviously I want to avoid the overhead
of needing to grant permissions on objects to individual users, but only
to roles and then maintain user rights via role assignments.

Thanks
Thomas

Re: SQLAuthorisation and role permissions

[Bryan Pendleton <bp...@gmail.com>]

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

Re: SQLAuthorisation and role permissions

[Thomas <Th...@t-online.de>]

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."Thomas@xy.de"', 'th');
GRANT reader TO "Thomas@xy.de";
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(
'derby.user."Bob@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="aaa@xy.com";password=x';

-- connect to database with a user that is allowed to connect
connect 'jdbc:derby:SecuredDB;bootPassword=encryption;
user="Bob@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="Thomas@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 "Thomas@xy.de";
disconnect;

connect 'jdbc:derby:SecuredDB;bootPassword=encryption;
user="Thomas@xy.de";password=th';

-- only after the explicite grant to the user data is returned
select * from RTE."SecureView";


disconnect;

exit;

Re: SQLAuthorisation and role permissions

[Bryan Pendleton <bp...@gmail.com>]

> view. My expectation is that user X via having been granted the role should
> be able to select data from the view.

Seems that way to me, too.

Could be a bug.

It seems worth it for you to post a complete example, so others can try it
and see if we can replicate your results.

thanks,

bryan

Re: SQLAuthorisation and role permissions

[Thomas <Th...@t-online.de>]

Thanks a lot for the help. Now everything is working fine.

Regards
Thomas

Re: SQLAuthorisation and role permissions

[Knut Anders Hatlen <Kn...@Sun.COM>]

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