Create MySQL View and Users in WFA DB

[ninja]

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.

Also, is there a way to setup a different database users to have read only access to this view besides the standard wfa user?

[sundarea]

Hi Ninja,

WFA doesn't support this.

Regards,

Sundar

[ninja]

It should allow both. There is a need as you begin to integrate WFA with other platforms which need the data found in WFA. The same with OCUM and OCI.

[sinhaa · Accepted solution]

@ninja

With referance to WFA4.1

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.