Using A Read Only, 'Archived' Database

Post date: Mar 03, 2015 4:25:3 PM

Periodically it's important to purge data and keep your system fairly trimmed down.  So you might want to dump the current data and set it up as a separate 'Read Only', 'Archived' database that people can go back and check, but to which they can't make changes.  Once you've set up a database (call it 'smarchive'), you can accomplish that like this:

This is one (experimental - it should work, but there may be a few limitations) way to make your database mostly read only.  You won't be able to run the truck schedule or the AR aging, but most other things should work - you should be able to change permissions and save and modify queries, and add/modify/delete users.

Assuming your archived database name is 'smarchive', your database server is the local server ('localhost'), and you will use 'smarchive' as the db in your link, go into MySQL and run these commands:

This will add a new record into the smcpcontrols database for the new user and database:

INSERT INTO smcpcontrols.companydatacredentials (

sdatabasename

, sdatabaseurl

, sdatabaseport

, sdatabaseuser

, sdatabaseuserpw

, sdatabaseid

, scomment

) VALUES (

'smarchive'

, 'localhost'

, '3306'

, 'smarchiveuser'

, 'kJ26D3G9bvK8'

, 'smarchive'

, 'Read Only Archived Database'

)

These commands give the necessary permissions to that user so the program can modify permissions, modify saved queries, and modify users:

CREATE USER 'smarchiveuser'@'%' IDENTIFIED BY 'kJ26D3G9bvK8';

GRANT SELECT on smarchive.* to 'smarchiveuser'@'%';

GRANT CREATE TEMPORARY TABLES on smarchive.* to 'smarchiveuser'@'%';

GRANT ALL on smarchive.systemlog to 'smarchiveuser'@'%';

GRANT ALL on smarchive.securityfunctions to 'smarchiveuser'@'%';

GRANT ALL on smarchive.securitygroupfunctions to 'smarchiveuser'@'%';

GRANT ALL on smarchive.securitygroups to 'smarchiveuser'@'%';

GRANT ALL on smarchive.securityusergroups to 'smarchiveuser'@'%';

GRANT ALL on smarchive.savedqueries to 'smarchiveuser'@'%';

GRANT ALL on smarchive.users to 'smarchiveuser'@'%';