replicating subsets of data in a2k 1

[uscitizen]

i don't know if the simplicity of the subject heading really does the thread i'm introducing any justice....

i have a database with a host of interrelated tables. at the simplest level, we have a patient registration table which has a medical record (MR) number. other tables may follow the patient over time or record previous or concurrent medications and so on and so forth, but in every table is the ubiquitous MR number.

for reasons that might not have anything to do with this, what we're aiming to do is somehow create an a2k mdb file with a subset of the data found in the 'native' tables, minus the confidential properties of the patients.

at first it seemed as if creating a blank database and linking every table in the parent to it was the approach to follow, however it occurs that this one would bring with it the MR numbers (unless it doesn't which would probably help a bit).

having never replicated a database, it occurred to me that perhaps that approach would be worth pursuing if it would result in subsets of data that denied the observer info about the MR.

perhaps there are other approaches..

has anybody met this problem?

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[jksmi]

Hi,

if you are using workgroup security on your primary
database (or data repository) you could create views in your primary database (i.e. without MR stuff) and then create views in your front end database, i.e.

SELECT *
FROM VW_DATA_NO_MR
IN 'C:\PRIMARY_DATABASE.MDB';

HTH, Jamie
FAQ219-2884

 

[uscitizen]

hi jamie,

currently the primary database is not FE/BE-ended (as in split) nor does my client want (as in feel it's absolutely necessary) that, so....

nor am i using 'official' ms a2k security; i applied a 3rd party vendor's security (LASsie from Peter's Software,

http://www.peterssoftware.com)

instead which is probably sufficient.

now about creating views in your FE and BE via

SELECT *
FROM VW_DATA_NO_MR
IN 'C:\PRIMARY_DATABASE.MDB';


would you mind clearing up what 'views' are in ms a2k and how my own app'n might deploy them in this case?

thanks in any event...

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[jksmi]

Hi,

A view is what you might refer to as a query... someone will correct me if I'm wrong here, but SQL (and therefore most dbms) would define a query as the question that you ask of a database and a view is the stored SQL used to answer that question. From what I've seen this is fairly standard and only Access and MSQuery use 'query' to refer to the stored SQL, I guess because they use a graphical 'query builder' (so I probably should have said query since this is an Access forum!).

You can still write SQL directly in Access or you can see what SQL query builder has written for you, in query design just go to View>SQL View.

Back to topic - replication can filter rows but I dont think it can filter columns but presumably each copy of the database will have to be updated with the latest data at some point?

If your security app is like the standard access security it can prevent users from opening tables on the master db (or repository, the one where they get the latest data from) but allow them to open views (queries) based upon those tables but without the fields relating to MR, i.e. you dont give them privilages to view the tables but you do give them privilage to open views. To update your client copies of the database you can run stored procedures that point to those views, thereby hiding the MR stuff from them.

HTH, Jamie
FAQ219-2884

 

[uscitizen]

hi,

i figured a view = query in a2k. no problemo. i am aware of the sql view of graphical queries so ditto.

to your suggestion....are you proposing that i use the 'replication manager' or 'wizard' if that is what it's called to replicate the a2k database.

my security (LASsie) app is similar to a2k stuff (i'm not an expert in either) and will let you create administrator defined security groups which have differing levels of privileges. w/in those that have edit/update privileges, there is yet the possibility to freeze from viewing forms and or controls w/in forms. it doesn't go so far as to control the tables themselves, however, alhough, since my a2k db is menu driven and i don't grant the user access to the tables, just the forms that access them, perhaps that's a non-issue.

perhaps where your suggestion breaks down so to speak comes from the fact that the (let's call it) secondary database is going to be used as a basis for an oracle tie-in and no data entry will be down with it -- it's going to be strictly exploited passively, as a sort of pass-through vehicle -- by oracle software as i understand. forms and such are not relevant: just the tables so i guess my question is where would this get me.

the other constraint is that one database (the primary one with forms, queries, reports, etc etc) resides on a firewall protected network server. the secondary one, which ideally would have its data linked in real time to the primary, must live on another server outside the firewall.

gnarly, eh?

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[jksmi]

Hi,

my heads spinning a bit - I think I may have been missing the point a bit actually as I though that your original database (with MR numbers and confidential stuff in it) was Access!

This is a long one I'm afraid, bear with me, I meant to just clarify what I understand of the situation but seemed to carry on writing, so I'm sorry if I'm still missiing the point!

a) You have a database with all the information required stored in it - this will not be used for data entry, tables are mostly related by MR number, some of this data (including MR number) is confidential, it is likely to be oracle based and sits on a firewall protected server. (i.e. server database)

b) There is a need for some users to access the non-confidential data, via forms, on workstations outside of the firewall. (i.e. client database)

c) You want the client database to query the data held in the server database rather than store data in itself (i.e. Frontend-backend or client-server style)

d) The client database is not used for data entry.

The views, queries, required for the functionality of the forms can be stored on the server, oracle, database. In oracle you can create a user profile and grant that profile read-only access to these views - and only these views. You can then use this user profile to access these views and prevent access to confidential data.

In your client, Access, database you can either use pass through queries to these views (on the server database) or link to the views using odbc (File>Get External Data>Link Tables>Files of type ODBC). The idea here is that you dont access any tables directly and the views give you an added level of security whilst letting you see all the information that you need to see (this is why I like the idea of calling it a view, as it gives you a window into the information stored in the database but the architect, you, design how big that window is).

Now your client database (frontend access app on client workstation) contains only views of data that is non-confidential, your forms, macros and reports.

I would go for embedding the connection string into a pass through query as linking via ODBC can be a pain (DSN needs to be set up, a little more open to abuse, etc...) and if you use security to prevent users from viewing/ editing the query in access they will find it harder to get the database connection string, username, etc... converting the access database to an mde will prevent them from damaging your code and, again, make it a bit harder for them to access connection string etc. On the other hand, even if the do get the connection string they wont be able to do any damage with that user (read only access on those views if you remember) and they would have to find a valid user name and password to get into anything other than those views. I dont believe that you can make a replica an .mde file...

The advantage of using replication here is for design changes, i.e. to change the design of a form in the design master replicated copies can be updated to reflect your changes. There are other ways of doing this of course. Replication doesnt really hold any advantages for hiding columns.

If your client database is used for data entry then you could use a similar solution - it would just need to be set up differently.

HTH, Jamie
FAQ219-2884

 

[uscitizen]

i'll try to intersperse my comments and such between the lines

my heads spinning a bit - I think I may have been missing the point a bit actually as I though that your original database (with MR numbers and confidential stuff in it) was Access!

mia culpa, mia culpa....i must've given ya' the impression that my parent db is not in a2k which it is.

This is a long one I'm afraid, bear with me, I meant to just clarify what I understand of the situation but seemed to carry on writing, so I'm sorry if I'm still missiing the point!

it probably owes itself to my inexpert describing

a) You have a database with all the information required stored in it - this will not be used for data entry, tables are mostly related by MR number, some of this data (including MR number) is confidential, it is likely to be oracle based and sits on a firewall protected server. (i.e. server database)

just the contrary, the a2k db will sit on a network server (the "I" drive) in a folder which people with network login names (usernames) can get to to do updates, edits,adding of records, generation of configured reports and what have you that is available to them via a menu driven interface i cooked up. right on about the interrelations amongst the tables and whathaveyou via MR number (sometimes visit number or another field gets involved, but MR number - which is the sensitive bit is always there; should be mentioned that on the "Registration" form per patient, there's a field that could be used in queries as a surrogate for MR number and which has no confidential meaning outside of the database, similar to an AutoNumber field --- it's unique to each patient enrolled


b) There is a need for some users to access the non-confidential data, via forms, on workstations outside of the firewall. (i.e. client database)

[d] right on! those users would be using oracle and need to get their grimy hands on these data[/b]

c) You want the client database to query the data held in the server database rather than store data in itself (i.e. Frontend-backend or client-server style)

what i'd want is for the client db to have an 'anonymized' view of the server based db's data, one which would prevent any users of the client db from getting hold of the MR nubmers--i guess the bottom line's 'yes'

d) The client database is not used for data entry.

right again! the client db is there to be exploited by oracle folks -- in the sense that they have other data which could be organized to correlate/link with the server's clinical data via the non-sensitive equivalent of the MR number

about the following, my head's now spinning and maybe i should delay trying to wrap my diminutive mind around this until you've had an opp'y to review the infor'n above?

The views, queries, required for the functionality of the forms can be stored on the server, oracle, database. In oracle you can create a user profile and grant that profile read-only access to these views - and only these views. You can then use this user profile to access these views and prevent access to confidential data.

In your client, Access, database you can either use pass through queries to these views (on the server database) or link to the views using odbc (File>Get External Data>Link Tables>Files of type ODBC). The idea here is that you dont access any tables directly and the views give you an added level of security whilst letting you see all the information that you need to see (this is why I like the idea of calling it a view, as it gives you a window into the information stored in the database but the architect, you, design how big that window is).

Now your client database (frontend access app on client workstation) contains only views of data that is non-confidential, your forms, macros and reports.

I would go for embedding the connection string into a pass through query as linking via ODBC can be a pain (DSN needs to be set up, a little more open to abuse, etc...) and if you use security to prevent users from viewing/ editing the query in access they will find it harder to get the database connection string, username, etc... converting the access database to an mde will prevent them from damaging your code and, again, make it a bit harder for them to access connection string etc. On the other hand, even if the do get the connection string they wont be able to do any damage with that user (read only access on those views if you remember) and they would have to find a valid user name and password to get into anything other than those views. I dont believe that you can make a replica an .mde file...

The advantage of using replication here is for design changes, i.e. to change the design of a form in the design master replicated copies can be updated to reflect your changes. There are other ways of doing this of course. Replication doesnt really hold any advantages for hiding columns.

If your client database is used for data entry then you could use a similar solution - it would just need to be set up differently

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[jksmi]

Hiya,

I think I'm getting there now - if so then the using views and read only user profile principle is just the same, its just the dbms's that I got the wrong way round!

An Access (2k) database is on the server and is all singing, all dancing for those users that are supposed to use it and there is no problem with them seeing the confidential information. I'm assuming that there is a password (for LASsie?) that they must enter to open the database.

Another group of, Oracle, users want access to the information but should only be allowed to see non-confidential information. They aren't allowed to change, add or delete any info just see it?

Is this new user group expecting to be able to query the access database in an adhoc fashion or do they have a specific set of requirements?

If adhoc, how many tables are joined using the MR number?

The make or break questions, really, are about LASsie as I've not come across it before - does LASsie support the creation of a user profile with read only privilages to stored views (or queries) and no privilages to any other database objects? (Access security does so if they're similar then it should) and can oracle actually get into the Access database through LASsie using ODBC?

HTH, Jamie
FAQ219-2884

 

[uscitizen]

Hey!

i'm going to reprise my interspersity technique

An Access (2k) database is on the server and is all singing, all dancing for those users that are supposed to use it and there is no problem with them seeing the confidential information. I'm assuming that there is a password (for LASsie?) that they must enter to open the database.

righto!

Another group of, Oracle, users want access to the information but should only be allowed to see non-confidential information. They aren't allowed to change, add or delete any info just see it?

battin' 1000

Is this new user group expecting to be able to query the access database in an adhoc fashion or do they have a specific set of requirements?

this is where nothing's been sop-ed down in stone....i think if i were on their side, i'd want to have a seamless way of getting at that data, and the reason to believe that at any point in time, it was the current version of the data (i.e. the equivalent of working w/ 'linked' a2k tables); i know they want to get the relationship map of the a2k db to recreate the table structure in their version of oracle...i think they're hoping to get batch extracts

If adhoc, how many tables are joined using the MR number?

quite a few, i'm going to hazard forty but that's not an exact

The make or break questions, really, are about LASsie as I've not come across it before - does LASsie support the creation of a user profile with read only privilages to stored views (or queries) and no privilages to any other database objects? (Access security does so if they're similar then it should) and can oracle actually get into the Access database through LASsie using ODBC?

correctamundo! LASsie supports user profiles with ro privs but not queries...rather to forms and/or reports. what could be cobbled into the menu could be a button which opened a form with command buttons each of which could launch a query of its own. if (in your paradigm) oracle side users had access to the a2k menu, then they could be prevented from using any of the forms/reports available to users on the network ("I" drive) side.

moving on to ODBC, this is uncharted territory for yours truly. i know what it stands for and they're available from somewhere or other, but how would that play into this scenario?


HTH USCITIZEN


“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[jksmi]

Hi,

Sorry, but I'm not sure that you can do what I'm suggesting in ms access with the set up you have. Using views to restrict access to user groups is something I've seen used lots before when the server database is Oracle, teradata, sql server, etc. and the client is access but not the other way around! (great for this situation, a one-off set up exercise and hey presto live data).

The only way I can see to make it secure is run some code to update tables in another database that they do have access to (ms acess or other) and dont give them any permissions to even open your access database - they can only get daily updated data though... (you could schedule the update to happen overnight for example).

or

upsize your database...

HTH, Jamie
FAQ219-2884

 

[uscitizen]

howdy,

interspersing.....

Sorry, but I'm not sure that you can do what I'm suggesting in ms access with the set up you have. Using views to restrict access to user groups is something I've seen used lots before when the server database is Oracle, teradata, sql server, etc. and the client is access but not the other way around! (great for this situation, a one-off set up exercise and hey presto live data).

???? not really all that clear what you're saying up there

The only way I can see to make it secure is run some code to update tables in another database that they do have access to (ms acess or other) and dont give them any permissions to even open your access database - they can only get daily updated data though... (you could schedule the update to happen overnight for example).

would this be doable over a VPN someone mentioned...is it your suggestion we might develop queries that created/updated 'anonymized' versions of the parent a2k's database in a2k sitting on a server on another network while at the same time preventing those with access to that a2k database from getting into the parent a2k database on our own server?

upsize your database...

that involves sql server does it not?

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[jksmi]

Hiya,

just quick answer I'm afraid as I'm off down the pub...

first bit I'm just saying that if the situation was reversed, i.e. you wanted to query an oracle database with ms access it would be a easy to secure. querying ms access with oracle isn't.

I know what a VPL is but I dont know about VPN? Otherwise yes - thats pretty much what I'm saying... you could either leave them the autonumber to join related views or create denormalised views so that they dont need to do any joins (normalisation is more about data intetgrity and since the data is coming from a normalised database I dont reckon that will be problem)

Upsizing your database does mean to SQL server, but you can have ms access as a front end to pretty much any dbms using odbc...

Have a good weekend...

HTH, Jamie
FAQ219-2884

 

[uscitizen]

i guess you're on a 4dy/wk sort of thing--it's still very much a thursday o'er here... have fun.

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln