Is there a way to create a View in one of the WFA DBs? I know I can put it in the playground DB but I need it in the regular DBs.
No but let me explain here.
In WFA there are 2 kinds of DB ( scheme) available
1. For Data Source Acquisition : This scheme is to be populated with data ONLY using the WFA Data Source acquisition method. The wfa built-in user 'wfa' has only READ-ONLY permission on it.
2. For 'Other' purposes: This scheme is available where data can be added/deleted/modified using any SQL queries. NO datasource acquisition is allowed on them. The wfa built-in user 'wfa' has all privileges on this scheme including creating views. 'Playground' is one such scheme given by default.
If you don't want all your views from multiple databases into 'Playground', just create separate corresponding view databases for each of them.
Ex: For creating views of scheme 'cm_storage', go to WFA->Designer-> Data Source Design -> Scheme and add a new scheme like 'cm_storage_views' of type 'Other' and save it. Do 'Reset scheme' on this.
Now you have your scheme ready for adding views. An example view
create view cm_storage_views.my_clusters AS select cluster.name, cluster.primary_address from cm_storage.cluster where cluster.name like 'my%';
Add as many views as you want.
Also, is there a way to setup a different database users to have read only access to this view besides the standard wfa user?
WFA has only provided a single non-root user 'wfa' who doesn't have CREATE USER privilege.
NOTE: In the upcoming WFA release you should be able to do both i.e add views on all the schemes and create new database users with grants on schemes. Its round the corner.
If this post resolved your issue, help others by selecting ACCEPT AS SOLUTION or adding a KUDO.