SQL Backend - Capture users details

Get help on databases - MySQL, Oracle, Access, etc.
User avatar
rustypup
Registered User
Posts: 8872
Joined: 13 Dec 2004, 02:00
Location: nullus pixius demonica
Contact:

Re: SQL Backend - Capture users details

Post by rustypup »

my point is, input validation should not happen in the table - that's generally far too late - it should be happening in a front-end...

a tool like log rescue will allow you to traipse throught the activity logs and present the offender with any amount of proof you require, but it's not going to resovle the underlying issue - your data is exposed to abuse.

are you controlling access to the database through these forms, or access to some other system?
Most people would sooner die than think; in fact, they do so - Bertrand Russel
User avatar
Tribble
Registered User
Posts: 88465
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: SQL Backend - Capture users details

Post by Tribble »

This is an interim program (well that is what it was supposed to be). The whole database was purely Access. The network was too slow so we moved it to a SQL backend - Access front end. There was only one user who was allowed to make changes. Legal and Compliance have now become involved and they have two people they want to have add and change data. I guess it is a case of a mouse becoming a giant. It was never supposed to be this relevant. It was merely a reporting tool and now it is being used to determine who can sell policies and who cannot. Those without enough credits - will be "let go".

They have no access to SQL tables except through the Access forms and reports. I guess I am controlling them through the forms. I have hidden the database window and they have restricted access to certain forms and all reports. They cannot view or create queries but can run them.
Image
User avatar
Firestrm_ZA
Deliciously Moist Cake
Posts: 16644
Joined: 13 Mar 2008, 02:00
Processor: AMD Ryzen 7 5950X
Motherboard: ROG Strix B550-F Gaming
Graphics card: EVGA 3080Ti XC3 Ultra
Memory: 32GB 3600 G-Skill Trident Z RGB

Re: SQL Backend - Capture users details

Post by Firestrm_ZA »

ok so i have to spam her thread here..

"you wanna do what? with whose backend?" :lol: :lol: :lol: :lol: :lol:
Image
https://carbonite.co.za - CUD central 8)
User avatar
Tribble
Registered User
Posts: 88465
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: SQL Backend - Capture users details

Post by Tribble »

Firestrm, my backend :twisted: And you don't need to know what - just that it will be fantastic!

Now go and spam some other thread :twisted:
Image
User avatar
Ron2K
Forum Technical Administrator
Posts: 9050
Joined: 04 Jul 2006, 16:45
Location: Upper Hutt, New Zealand
Contact:

Re: SQL Backend - Capture users details

Post by Ron2K »

Tribble wrote:rustypup, I really have no idea what you mean by a coded front end.
Here's an example - this forum is a coded front end for the database that sits behind it. :wink:
Kia kaha, Kia māia, Kia manawanui.
User avatar
Tribble
Registered User
Posts: 88465
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: SQL Backend - Capture users details

Post by Tribble »

Ah - but we can only edit those posts we make. Now these people need to edit all data - not just the ones they are the capturer for. If I had known it would get this big and important - I would have done many things differently.
Image
User avatar
rustypup
Registered User
Posts: 8872
Joined: 13 Dec 2004, 02:00
Location: nullus pixius demonica
Contact:

Re: SQL Backend - Capture users details

Post by rustypup »

Tribble wrote:I am controlling them through the forms.
access forms are simply plugged directly into the underlying table...
Tribble wrote:we can only edit those posts we make.
precisely... not managed via SQL but through the front end, (in this case, a split-model front end, but the logic remains the same).

look at it this way - in a sane world, you wouldn't waste time and effort finding the person responsible for wandering into the tuckshop and pilfering the coffee... you'd put a lock on the front door and control who has the key, forcing everyone else to interface with the shop via the counter..

how complicated is this process?..

could we not cobble something together for you to present as an alternative?
Most people would sooner die than think; in fact, they do so - Bertrand Russel
User avatar
Tribble
Registered User
Posts: 88465
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: SQL Backend - Capture users details

Post by Tribble »

That would be nice. Basically we need to capture all the details of each agent. There is a lot of information we need about each broker. We also need all their educational qualifications. Those that have been approved by the Sita have credits allocated to them. Each agent needs a minimum no of credits before he can sell a product. Those products have now been broken down into 19 sub categories. Each subcategory has differing examination and supervision requirements. Not only that - there are two different types of agents. Each of these types have different supervision requirements for each subcategory. Based on each Agent's inservice date - is their deadline date. They are required to accumulate activity points too - and this is subject to a list of criteria.

We need to track all qualifications - approved, submitted for approval and those not yet approved. Those not yet approved have to be actioned and submitted to the insurance sita for approval. The all have SAQA codes and each code is based on the course and the training institution. Not all institutions have had their qualifications approved. Points are permitted for all qualifications obtained if the agent was employed before the end of last year. Those employed this year will only get credits for those approved qualifications.

I have tried to cut down the need for user input as I use a lot of feeder tables and combo boxes. The only place I really have trouble with is the Personal Details of each agent. This is where some of the information was removed. Not entire records - just random fields. These are also not required fields as not all people belong to Provinces, Regions or Branches. Granted - the data that was removed was not critical but it is worrying that someone would remove it and then deny it. It brings into question the integrity of all the other data that this person has been working on.

If you can suggest a way that we can prevent this without having to redesign the entire database and front end - I would be eternally grateful. They are working on the permanent solution - which was to have been ready at the end of this year. It doesn't seem as it will be - so my interim measure will be running for at least another two years.
Image
User avatar
rustypup
Registered User
Posts: 8872
Joined: 13 Dec 2004, 02:00
Location: nullus pixius demonica
Contact:

Re: SQL Backend - Capture users details

Post by rustypup »

Tribble wrote:my interim measure will be running for at least another two years.
:shock: that's a variation on "interrim" i've never met before :lol: ..

at this point, i'd look at a VBA solution, using the onchange/beforechange events to validate each field before comitting... :|

you should still extract the necessary detail from the logs and present them to the "problem"...
Most people would sooner die than think; in fact, they do so - Bertrand Russel
User avatar
Tribble
Registered User
Posts: 88465
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: SQL Backend - Capture users details

Post by Tribble »

I shall do that - we have a meeting tomorrow afternoon. I need to show then who is at fault.

And thanks - I thought VBA might be the answer *Cat goes to the shelf and extracts her VBA for Idiots, Accesss 200 VBA Handbook, and VBA 6 books.* I am sure I shall find the solution before the meeting.
Image
SBSP
Registered User
Posts: 3124
Joined: 09 May 2006, 02:00
Location: Centurion

Re: SQL Backend - Capture users details

Post by SBSP »

rustypup wrote:just to toss my dodgy penny into the pot... when users are deleting critical data, this says "design flaw" - time to relook the input approach.

User Level Security - i'm no access fundi, but you should be able to prevent unauthorised deletion ...

after a brief scan, it would appear that user logging and user object references are only available once ULS is installed, not before then.
Not Really.
Users with same access can delete one another's orders in the ERP system that we use.
One user would create a Dummy or "Standing" order to allocate stock or commit stock.

A different user with same access in the same sales channel is allowed to release stock, But if there is like 5 of them you can
never know who deleted the "standing order" the AuditUser field in the header of the order whoulnt show cus the whole order isnt there anymore..

I have the same problem tribbles.

At this point if we find who it is we will issue a written warning.
Post Reply