Delete column from dyname temp-table

[Kladkul]

Is there a way to delete a column from a dynamic temp-table? I need to do this if the user doesn't have the appropriate access level and am having trouble finding a way to do this.

 

[jongpau]

Nope, once a dynamic temp-table is "created" that's it. But, call me silly, if it is a dynamic temp-table, then why do you create the column in the first place? Can't you determine the access level of the user before creating the temp-table and skip all disallowed columns? Also, care must be taken that you do not exclude columns that are required for the proper operation of your application, but I am sure you will have that bit under control already.

Another option of course is to make the column invisible in the client (whatever that client is) based on access rights, but that does require you to have control over the client side of things and that is very often no longer the case.

 

[palthe]

Another option of course is to make the column invisible in the client (whatever that client is) based on access rights, but that does require you to have control over the client side of things and that is very often no longer the case.

Hmm I think that is exactly the way to go. You said it yourself; why not go through authorization logic BEFORE creating the temp-table.
Well, why not go through authorization before creating, for example, the dynamic browse. You only ADD-LIKE-COLUMN for the authorized columns in the temp-table. This should be rather easy to implement in the software, shouldn't it? This is how most applications work, the tables are there but what you can see is dependent on what kind of rights you have.
So I think the suggestion for making the column invisible is exactly the way to control things. 2 cents.

 

[tamhas]

The safe thing to do is to eliminate the column in building the temp-table or at least to blank the contents, even if the column still exists. That way, if you change UI technology or repurpose to send data to some other kind of agent which you don't control, the data simply won't be there. This centralizes the control.

 

[jongpau]

The safe thing to do is to eliminate the column in building the temp-table or at least to blank the contents, even if the column still exists. That way, if you change UI technology or repurpose to send data to some other kind of agent which you don't control, the data simply won't be there. This centralizes the control.

But when you blank the contents, there is always the worry that blank data may be offered back to you to store in your database. This would make saving data back into the database a lot trickier and you would have to apply the security while saving and do so on a column by column basis... If the column simply isn't there at all you should theoretically not be overwriting any database contents. You would however have to ensure once (before actually saving) that the "user" has proper access rights for each column in the temp-table/dataset that is to be applied to your database.

 

[tamhas]

To me, having the column appear and disappear tends to make the coding problem at the consuming end much more difficult and error prone. As for overwriting with blank data, that seems to me just a part of the same security test that blanked the data in the first place. If the user isn't authorized to see it and it is entered rather than computed data, then the user isn't allowed to update it either. Putting this logic in the data source in one place means that no one anywhere with any kind of client can get data they shouldn't or impact data they shouldn't. I think there is a lot of value to having a uniform interface with the outside world.

 

[palthe]

The safe thing to do is to eliminate the column in building the temp-table or at least to blank the contents, even if the column still exists. That way, if you change UI technology or repurpose to send data to some other kind of agent which you don't control, the data simply won't be there. This centralizes the control.

Ok, I can see the logic in that. However, if you are building business logic against a normal database with static tables, isn't it just the easiest and most used way to just eliminate the column from the UI? So why would this be any different with temp-tables? I think the easiest way to build your app is to have the same data for all clients at all times, and in the ui-layer you decide what kind of information is shown to the actual final client. But hey, maybe I'm just lazy; I would surely never proclaim the above to be the best way

But anyway, if you are going to work with the dynamic temp-table, just define the procedure to create it, and based on some logic on your authentication, you re-run the procedure, actually rebuilding the temp-table, every time you want to use it.
Be sure you clean up the existing temp-table first, with a IF VALID-HANLDE(temptablehandle) THEN DELETE OBJECT temptablehandle.

 

[tamhas]

Actually, the lazy way is to control the security in the data source. That way you only have to do it in one place and one time. In the UI you might have to do it multiple times ... and you have the possibility of someone doing something different on the client and violating your security. Control it at the source and you can be confident.

 

[palthe]

Actually, the lazy way is to control the security in the data source. That way you only have to do it in one place and one time. In the UI you might have to do it multiple times ... and you have the possibility of someone doing something different on the client and violating your security. Control it at the source and you can be confident.

Just for the sake of discussion; in what way is it a benefit to control it at the data source? I mean, you have to build the temp-table multiple times as well, just like any UI on that particular temp-table; there has to be some sort of authentication layer. Why is it better to use it to create the temp-table and not the view on it?

 

[tamhas]

Why create the same temp-table in multiple places. Encapsulate the logic in a data source and put all logic about security control, updates, relational integrity, etc. there. Any place that needs it, accesses that one source.

Done any reading on OERA? This sort of layering is inherent in the approach. You don't want it in the UI especially since this year's ABL UI might be next year's WUI or you might be sending the data off to some other service or providing it via a web service or sending it to a Java client. If you have put all of the build and update logic in one place, then you can do all of that without touching the data source. Put it in the UI and you have to do it all over again for a different UI and you have the risk that someone will access the data outside of the expected channel and see what it is they shouldn't see.

 

[palthe]

Why create the same temp-table in multiple places. Encapsulate the logic in a data source and put all logic about security control, updates, relational integrity, etc. there. Any place that needs it, accesses that one source.

Done any reading on OERA? This sort of layering is inherent in the approach. You don't want it in the UI especially since this year's ABL UI might be next year's WUI or you might be sending the data off to some other service or providing it via a web service or sending it to a Java client. If you have put all of the build and update logic in one place, then you can do all of that without touching the data source. Put it in the UI and you have to do it all over again for a different UI and you have the risk that someone will access the data outside of the expected channel and see what it is they shouldn't see.

Hm ok, but then you see the temp-table totally apart of the final UI. I mean, the data is already there, you prepare it with a set of logic and finally access it with a UI. In that way you create a data-layer, a business logic layer and a UI-layer.
Ok that kind of layering I'm familiar with, I just took the temp-table as a final input set for the UI (browse for that matter) where you put it in the business logic as a prepared dataset which at that time can be accessed. I guess you are totally right, with referral to layering, I just had a different mindset there. Thanks for your reply!

 

[tamhas]

Remember that in a modern architecture, the UI and the preparation of the temp table will typically be on totally different systems. In fact, ideally, one would build the temp-table in the data layer and then serialize it to XML for transmission to the client. That way, the client can be ABL or not and the server side logic can remain the same.

 

[Kladkul]

For this particular situation, no data was updated it was simply for viewing. I ended up just hiding the column when the user didn't have the appropriate permissions.

If it was for updating, I would think not selecting that data all together would work best.

Thanks for the help everyone!