db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Thomas <Thomas.K.H...@t-online.de>
Subject SQLAuthorisation and role permissions
Date Wed, 21 Apr 2010 21:06:03 GMT

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


View raw message