New Topic

Category Administration

Product Version 9.0

Is this stored procedure still valid for v9?

Dean Flinter shared this question ago

Answered

Hi,

I want to give all admins access to private reports

I came across this article: https://community.yellowfinbi.com/knowledge-base/article/a-stored-procedure-that-allow-an-administrator-to-view-all-private-reports which has a stored procedure that does just that

Just wondering if this is stil valid for v9 or has this option been added elsewhere in v9?

Thanks

Dean

The same question

Replies (1)

Mike Sheehan ●

Hi Dean,

Thanks for reaching out. This should still work in V9. If you look at the actual Stored Procedure SQL you can see the values it updates are the following:

/aaef07f9301cb0bbf931eab19a3ee58a

This would be the same method by which you'd give yourself access via the back-end regardless of version (at least as far back as 7.2, which is as far back as I can personally verify off-hand). This stored procedure just makes that process considerably easier by only making the user simply input a PersonId.

Please let me know if you have any further questions on this.

Regards,

Mike

Reply URL

Dean Flinter ●

Thanks Mike

I don't have access to the config DB myself so wasn't sure if there was any changes to the schema since that article was posted

On a related note, is there an ERD or any documentation on the config db?

Thanks

Dean

URL

Dean Flinter ●

One more question if you don't mind

Can you tell me where in the config db I can get the list of users and their corresponding person ID? For use in the stored procedure

Thanks

Dean

URL

Mike Sheehan ●

Hi Dean,

Unfortunately, there is no ERD or much documentation in general regarding the config db, but if you have any specific questions I'm happy to field them. It is not desirable for users to look through these relations and make changes to the back-end themselves as this can cause irrevocable data loss. This should only be done with Support guidance.

PersonId refers to the IpPerson value in the Person table.

Please let me know if you have any further questions.

Regards,

Mike

URL

Dean Flinter ●

Thanks Mike!

We are using PostgreSQL v10 for our DB so I have edited the script in the above article. Only version 11+ supports stored procedures so I had to change it to a function.

I've attached it here in case others could find it helpful. Note we have yet to run it but my testing in DBFiddle looks good

Just note, that after creating the function, you need to call it by running

SELECT sp_Add_PersonId_to_private_report (<insert your PersonId here>)

Thanks

Dean

Files:

URL

Mike Sheehan ●

Awesome! Thanks Dean. Thanks for the info and sharing your specific solution. I appreciate it.

Please let me know if you need anything else here or if we're okay to close this out for now.

Regards,

Mike

URL

Dean Flinter ●

You can close this out for now

Thanks again!

URL

Mike Sheehan ●

Hi Dean,

Sounds good. You're welcome!

Regards,

Mike

URL

Dean Flinter ●

Sorry Mike to revive an older thread but I realised the function above will insert values even if the user has access to the report already.

I'm not sure what kind of keys might be in the table and if dupes can be inserted. I'm not sure what dupes would do to the application so I have updated it to only insert when it is a private report that the user doesn't already have access to

Again, buyer beware because we have yet to run this but will do so soon

Thanks

Dean

Files:

URL