Pass user data to report and permission [#2213119]

Hi David,
Again congrats for this module. It has become a corner stone of my production site.

I would like to know how you would do to pass a user profile field value to a report such that the report runs for this user only. On top of that I'd like to restrict access to the report to the user only.

Any hints would be appreciated even if it's for 6.x !

Thanks

Matt

Comments

Comment #1

metzlerd CreditAttribution: metzlerd commented 7 March 2014 at 23:56

There are a couple of different options:

1. Write a custom php function that is registerd for your data provider to check security based on this profile field. This would involve custom module development.

2. You could nest the inner data block in an outer one that verifies that the user has the particular profile field.

Here's some sample SQL code for getting the profile data in drupal 6.

--ACCESS=access content
select f.fid, f.name, v.uid, v.value  from profile_fields f join profile_values v ON f.fid = v.fid
  where f.name='my_field' and f.uid = :current_user and v.value='some specific value you want to test'

The current user parameter above should be prefilled with the uid of the current user in the default forena installation.

I realize that this may be a bit much to take in and there may be some nuances in getting the parameters passed through correctly if you wrap one block inside of another, but this should basically work for you if I understand your goal correctly.

Comment #2

yokotom CreditAttribution: yokotom commented 10 March 2014 at 14:43

Hi David,

Yes I think you understand my goal correctly. I have the following report running from an external psql DB:

--ACCESS=access administration pages
SELECT a.type, ROUND(sum(a.cost), 2) as cost, ROUND(sum(a.rawcost), 2) as rawcost, a
round(Sum(a.rawcost) - Sum(a.cost),2) as Discount
FROM actmgr.cost_activity a
WHERE a.crdate=:crdate and a.account=:account
GROUP BY by a.type

Some user with a specific role (pi) need to have access to their own data where :account should be populated by their v.account for example. Do I need modify the setting.php file that is in data_blocks/FolderWhereSQLare or access to drupal DB is enabled by default ?

Comment #3

metzlerd CreditAttribution: metzlerd commented 10 March 2014 at 23:51

A small custom module would probably be the best long term solution given drupal 6. The idea is that you write a small php function that queries the drupal database and returns the account from the profile field. Then when you define your pgsql external db connection you indicate specify the 'user callback' for your postgres connection to refer to the function that you created in your custom module. If you do this :current_user will be populated by the php function that you wrote in your custom module. You can then specify a.account=:current_user in any of your data blocks and have it limit to the data allowed by the current user.

At minimum you need a .info file with core=7.x and name=yourmodulename in it and a .module file with the function just described in it.

I'm assuming that your forena db connection is defined in your settings.php file?

Am I making any sense here? Feel free to ask questions.