Upgrading secured backend database

[TinkerMan]

Hi

(A2K format, but running on XP, etc)

I have used Microsoft’s Security FAQ and recommendations on this forum and build a split database; secured with one worgroup file for development and one for customers: what is the most appropriate way to upgrade the backend database content (tables mostly) (as the front-end database can just be replaced) at the customers site?

At the customer there is no development workgroup file, so no permissions to change database objects. I would really like to run sql updates from the new FE database. The solution I currently have devised to go around this problem, involves a lot of manual data moving code, to take care of referential integrity etc. However this approach has a higher risk of creating errors and also takes a lot longer time.

Is there something I have misunderstood of the Security FAQ or recommendation on this forum?

Thanx

 

[Robert88]

Hi TinkerMan,

I gather your currently in A2K format. What format do you want to move it too and is it neccessary dependant on version you are moving it too?

Access 2003 runs A2K by default.

Look forward to your response.

Robert88

 

[TinkerMan]

Hi Robert88

Thanx for your reply.

Sorry for not specifying more clearly: I'm not upgrading Access, but my own database; My scenario is that i have a version out at customers. I then make a newer version with more functionality and I need to deploy it. FE I can just replace, but the BE has to be changed somehow. What is the best way of upgrading my old BE to a new BE, when the security is in place?

My current (planned) solution is to manually to export data from existing BE to a new one, into import tables exactly like the old tables. I then go through (in the new BE) migrating the old data into the new tables, keeping the integrity. It will require a lot of work and a risk of problems. That's why I would prefer to run some SQL update statements to change the tables instead.

Cheers

 

[Robert88]

Hi TinkerMan,

For SQL, you would be looking to use the "ALTER TABLE"

Robert88

 

[TinkerMan]

Hi Robert88

Sorry for being so inaccurate: As you mention ALTER TABLE statements is what I want to use. However, my problem is that my database is secured, so I do not have access to change the table object, as I'm not the owner (as described in my initial post). Because this upgrade happens at the customer, I will be running with the customer workgroupfile, instead of the development one. All this is done as suggested in this forum and the famous security FAQ from Microsoft.

My ponder is therefore; surely I'm not the only one running with a secured database, how are other people handling this? Is there something I have missed or something I have not thought about?

Cheers

 

[Robert88]

Hi Tinkerman,

Can you not get hold of an account from the secured database that will permit the appropriate changes? It would seem a lot easier.

If it is secured usually access to an account with admin rights will do the trick. By moving it to another file it might neglect the security?

Robert88

 

[TinkerMan]

Thanks Robert88

Can you not get hold of an account from the secured database that will permit the appropriate changes? It would seem a lot easier.

Yes, that would definately make things easier Also I would not have to comprismise security by changing the ownership of the tables, which one of the core elements of the security.

So what you're suggesting is to make a user in the customer workgroup that has permissions to change the tables? Can that be done without changing the owner (as it will be "uknown" at the customers site, because the admin in my development workgroupfile owns them)? Or would "modify design" permissions be enough on the table?

If it is secured usually access to an account with admin rights will do the trick. By moving it to another file it might neglect the security?

But admin rights is exactly what you want to avoid at the customers site, as it can be misused. Is not that exactly why the owner is the admin in the development workgroup file?

Moving everything to a new database will reset owners to admin at the customer, leaving me completely out of the loop....

 

[gemma-the-husky]

Can you use the release of the fe database to test the backend, and then run alter table sql commands on the be.

The only problem i have with this is that some datatypes dont seem to create properly. y/n types from memory

 

[TinkerMan]

Hi gemma-the-husky

Can you use the release of the fe database to test the backend, and then run alter table sql commands on the be.

Since I can only replace the FE, I was hoping to run all updates from from the FE. The problem as I see it on upgrading from the BE is that you need to have the code for upgrading available in the BE. That means that if you bring a new version of the BE with the upgrade code, there will be no data, and then you will have to import all the data from the old BE. Alternatively I could import the upgrade code from text file (or perhaps the FE), for then to run the upgrade, but it seems a bit messy from a users perspective. Therefore if I can run this from the FE it would be the best. Do you see any problems with this?

The only problem i have with this is that some datatypes dont seem to create properly. y/n types from memory

Yes, I think I remember having issues with that from SQL, even running on the BE. I had to use the modify design screen to get it properly..

Thanx