I want a user who can view workflow history of any user on site. Is there anyway to provide access or permission to the user to view all the workflows of site/other users?
There is no permission for that, and Alfresco - in response to a whining Enterprise customer - implemented a very narrow / restricted view of other peoples workflows / tasks in Alfresco 4.1.5 (which has remained constant since then). You'd have to replace the existing workflow security component with a completely custom implementation to be able to get a whole picture.
With regards to the actual querying, the WorkflowService API in Java already provides the means to query for active / historic tasks that are assigned to a specific user or have been completed by that user, to construct the history of involvement. One problem remains though: when users assign the same task to one another and back without resolving it, the intermediary assignments are not stored in a queryable way within the Activiti tables, so this kind of history cannot easily be reconstructed.
Retrieving data ...