Active IQ Unified Manager Discussions

Create MySQL View and Users in WFA DB

ninja
3,606 Views

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?

1 ACCEPTED SOLUTION

sinhaa
3,515 Views

@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.

 

If this post resolved your issue, help others by selecting ACCEPT AS SOLUTION or adding a KUDO.

View solution in original post

3 REPLIES 3

sundarea
3,545 Views

Hi Ninja,

 

WFA doesn't support this.

 

Regards,

Sundar

ninja
3,534 Views

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
3,516 Views

@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.

 

If this post resolved your issue, help others by selecting ACCEPT AS SOLUTION or adding a KUDO.
Public