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
