Question Database access control

[BeanTee]

Hi, our developers are granted the access to query database. However, once the access is granted, they can also amend the data in database. Just wonder whether there is any control to restrict the access to read only permission.

 

[BeanTee]

I notice that we can set the permission under Admin > Security > Edit Data Security.
However, there are a lot of tables to be updated. Is there any way we can change the security setting using .df file?

 

[RealHeavyDude]

In theory yes. In practice I would say no. Plus I bet that the effort to prepare such a .df is bigger than writing an ABL procedure that updates the corresponding fields in the _File meta schema table accordingly.



Heavy Regards, RealHeavyDude.

 

[KMoody]

Is it safe to change the _File table to control data access permission? Is it possible that changes to meta-schema databases won't carry over to future releases of Progress?

(Sorry for reviving an old thread, but we're currently working on database security and this caught my eye.)

 

[RealHeavyDude]

I do that constantly, write onto the _Can-* fields of the _File to change permissions on individual tables. In fact it is automated - we import the entitlements from a system which handles all corporate entitlements, be it a network resource like a shared a drive or a specific authorization in an individual application.

Never did I encounter any problem and never did I lose any of those settings.

Heavy Regards, RealHeavyDude.

 

[KMoody]

Oh, I see. I was thinking back to this thread: http://www.progresstalk.com/threads/_file-and-_field-tables.121950/#post-379746

But now that I've read that again, it looks like you were advising against changing the schema, not the values of _File and _Field records. My mistake!

 

[Rob Fitzpatrick]

There are certain fields in the meta-schema that you are meant to be able to change, provided you use the appropriate tools (data dictionary/data administration). Altering the fields directly via code is not something I would recommend for the uninitiated.

 

[RealHeavyDude]

Starting the data administration GUI to change the _Can-* fields whenever you have to modify data security is not feasible in an enterprise. Full Stop. Therefore you need to roll your own since Progress does not provide an API in the ABL -like GRANT and REVOKE in the SQL world. Especially when you have the entitlements delivered to you via file transfer from the entitlements system containing thousands of records. That does not mean that this is recommended by Progress in any way to do so - it's just a pure necessity because Progress fails to deliver an API in the ABL that one could use.

Having said that, I am in the process of migrating all our Progress applications to single sign on via a smartcard and utilizing the client principal. Still that does not lift the need to have the permission set on table level. One of the issues you might face with the Progress OpenEdge RDBMS is that its philosophy is contrary to the rest of the SQL competition. Therefore corporate security policies and auditors, most of the times even unaware of the fact that there are such databases in their scope, insist on having security designed at table level in the database. That's the way it has to work. Full stop.

If you fancy horror stories when it comes to auditors and regulators I am more than happy to dive into gory details with anyone - even at Progress.

Heavy Regards, RealHeavyDude.

 

[Rob Fitzpatrick]

I don't include you in "the uninitiated", RHD.

But your point about the need for a security API is well taken. I hope you can get to Dusseldorf and bend the ears of some PSC folk at PUG Challenge.

 

[RealHeavyDude]

I am planning to attend the PUG Challenge in Düsseldorf and will try some ear bending on the PSC stuff that will be there.

Heavy Regards, RealHeavyDude.

 

[Cringer]

If you can face it then it might be a good idea to raise it as an idea over on the community. https://community.progress.com/busi..._-_tell_us_what_youd_like_to_see/default.aspx