Hiding query definitions 1

[MBorofsky]

I am looking for a way to hide query definitions from the user. We want them to be able to run reports off of these queries but not see the SQL/design view of the query. We also want to prevent them from viewing the reports in design-view. Using user-level security I can prevent the user from modifying queries but that isn't really what we want.

We want to eventually move everything to SQL-server anyway because some of the end users will have millions of records (we already have a backend there but translating the queries will take longer than we have). A solution that involved moving the queries into Microsoft SQL-server first and then encrypting them there would be fine as well.

At this point the only other thing I could think of was writing the query definitions in VB-code and saving the results in tables. It runs quickly enough but the backend would grow quickly, we would have to compact-repair fairly frequently.

 

[MoLaker]

First, you can create stored procedures in SQL Server to perform your queries and call them from Access using a pass-through query. You could not easily edit or add data this way, but that does not seem to be your issue. Access users could not view the query structure of the stored procedures, only the Access query which would only be an Execute statement calling the stored procedure.

That said...

You could make the query definitions hidden objects then not allow the user the ability to change their options and view the queries. There certainly are ways around this restriction, but only by savvy users. Even access by the savvy users can be reduced further by making the DB an MDE and disabling Startup by-pass feature (holding down shift key when stating). You need to make sure you have a good backup before starting such an effort because you can lock yourself out if you mess up.

Another approach would be to place your SQL in code within a module. Then make the DB an MDE. The code cannot be viewed in an MDE as it is compiled.

As for storing the query definitions in a table, don't do that. Store the SQL instead. That will have very little impact on DB size or bloat.

User-level security can be used to prevent viewing the design of reports.

 

[VBslammer]

Create a user group for those users and deny the appropriate permissions:
[tt]
List: ( )Users (x)Groups Object Type: Query

Permissions

( )Read Design (x)Read Data
( )Modify Design (x)Update Data
( )Administer (x)Insert Data
(x)Delete Data
[/tt]

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[MoLaker]

VBslammer,

Won't work for what MBorofsky wants. Try it, I thought the same thing. But, if you set the permissions so a user cannot read the design of a query, the user cannot read data from the query either. Turn on Read Data and Read Design becomes checked automatically.

 

[VBslammer]

Forgot about that...

In order to hide your SQL, you have to create queries using the "WITH OWNERACCESS OPTION" attribute.

[ol][li]Create the actual query, then deny the user/group permissions to it (all permissions unchecked).[/li][li]Create a second query that includes * fields or selected fields from the real query, and give it the "WITH OWNERACCESS OPTION" clause, and give the user/group permissions to Read Design, Read Data, Update Data, Insert Data, Delete Data as needed.[/li][/ol]

Now the user can't run or view the original query at all, but they can run the second query and read its design. The catch is that they will only see "SELECT * FROM qryOriginal WITH OWNERACCESS OPTION" in the design window.

Setting the original queries' "Hidden" property will keep them out of the database window. It doubles the amount of queries needed, but that's the only way I know of to keep users out of the SQL when you don't limit them to the runtime version.

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[MBorofsky]

Thanks for your input. I am looking into redoing the Access security, for some reason when I have everything unchecked the 'User' still has access to the queries - they just can't save changes. I can't use the Access wizards because they all crash, so I am attempting to click around in the security section.....

If I were to use compiled SQL-code, what would I do about multi-tiered queries? (I mean where query 3 calls query 2 calls query 1 calls table 1) Would compiled SQL-Code in Access be able to handle 100,000 records relatively efficiently if the back-end tables were in SQL?

We are planning on limiting them to runtime. I pretty much thought that just prevented the user from seeing any VB-Code though.

 

[LittleSmudge]

If you don't need the users to be able to design new reports based on the existing queries then :-

Take a report in design view.
Copy the SQL string in the query that it is bound to.
PASTE the SQL string into the Report's RecordSource instead of the name of the query.

Repeat for all reports

DELETE all queries ( you no longer need them because the SQL is in the Report )
Then set the security on the reports and it's jub done.



'ope-that-'elps.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[MBorofsky]

The queries aren't simple queries though. That would work if the queries pulled directly from tables, unfortunately they call other queries. Most of them have between 3 and 7 queries all used to put the data together the way the report needs. For example:

Query1 takes a primary key and the minimum of a date from table 1 where a specific set of criteria are met.

Query2 joins the primary key and minimum date from query 1 to table 1 and takes a data field.

The report would then use the data from Query 2.

How would I put the SQL for multiple queries in 1 SQL statement? The example above is DRASTICALLY simpler than anything used in the program.

 

[LittleSmudge]

Use Sub queries

SELECT field1, field2, etc 
FROM table1 
INNER JOIN 
      [SELECT DISTINCT fieldX, fieldY 
       FROM tableZ 
       WHERE fieldY Like '%SDF%']. As SubQ 
ON table1.field1 = SubQ.fieldX

Yes it can get a bit messy - but it is what Jet is doing in pulling in the results for you anyway -

And if it fixes your security requirement .. ..




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[MBorofsky]

Would that work in Access for a large number of records assuming the back-end tables are linked to SQL-Server? Access has an upper limit.

That sounds like the best solution yet, especially if I can write a script to automatically take each report and modify its SQL code to follow that pattern as opposed to the original method we were using. That way we could design them in the standard (easy) view and then export them to the user's version which would be far too complex to really understand. Is there a maximum limit on the size of an SQL statement?

 

[MoLaker]

We want to eventually move everything to SQL-server anyway because some of the end users will have millions of records (we already have a backend there but translating the queries will take longer than we have). A solution that involved moving the queries into Microsoft SQL-server first and then encrypting them there would be fine as well.

I may have made an incorrect assumption based on your statement above. Is your data stored in a SQL Server database or not?

If I were to use compiled SQL-code, what would I do about multi-tiered queries? (I mean where query 3 calls query 2 calls query 1 calls table 1) Would compiled SQL-Code in Access be able to handle 100,000 records relatively efficiently if the back-end tables were in SQL?

Once again, assuming your data is stored in a SQL Server database, I know of no reason why you could not build a complex query as you indicated within a SQL Server stored procedure. Also, the stored procedure would be located within SQL Server, not Access. Access would only be calling it. So, it would be SQL Server performing the processing and it can certainly handle 100,000 records.


On the other hand, if you were to decide to stay entirely with Access and use coded SQL complied in a module, complex queries can be written there as well. It might get a little testy if you get extremely complex, but, SQL is what is behind all query definitions anyway.

However, VBslammer's suggestion of using "WITH OWNERACCESS OPTION" may well be the way for you to go with the least development.

http://office.microsoft.com/en-us/assistance/HP010322891033.aspx

In your case, it should only add one additional query for each set of complex query calls. In other words, you should only have to have an additional query to call query 3 which would call query 2, etc, etc.

 

[MBorofsky]

Please keep in mind that I have never worked in security or SQL-Server before so I don't really know what I'm talking about. I'm quite lost right now.....

The data is now stored in SQL-Server. It, however, is fake. The user will be importing their own data into the system. (Someone else is worrying about this process)

We could put the queries into SQL-Server, but wouldn't that ultimately be just as insecure as Access? The user will have need their own copy of the SQL database because each will be importing their own dataset which they would not want to share with anyone else. We are still working on this process as well. Would it be possible to write code that would export secure-queries described by VBSlammer (which I have not yet gotten to work) directly into SQL-Server? Is it possible to send an SQL-Server file that the user could not open but would still have the right to delete/import data to?

I just discovered that if I open a 'compiled' access database from the full version of Access I can see/modify code, queries, ect. This makes LittleSmudge's solution undoable because any user who owned the full version of access would be able to 'uncompile' the project with ease.

I am going to continue try to get the security described by VBSlammer to function.

Thanks for all of your input, I will likely be pouring over this for awhile.

 

[LittleSmudge]

No OM

What you have done is miss out the loack down part.


You can create databases with the use of the standard MSAccess security model that allows or stops access to all sorts of levels of design, edit, etc.

As for CODE - it is even easier than that.

In the code window lick on the TOOLS Menu, Select the bottom option .. .. Properties,
Go to the Protection Tab - click on the "Lock Project For Viewing" and add a password.

Users will then be unable to see code without giving the password ( So YOU can see code when needed )


'ope-that-'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[MBorofsky]

Access 2003

Menu:
Tools->Security->User and Group Permissions

List: Users
Select 'Admin' and check all boxes
Select 'User' and uncheck all boxes

Exit Access and login as 'User'. I can still view the design of the query and modify that design. The only thing I can't do is to overwrite the original query with my modified design.

 

[MBorofsky]

littlesmudge says:

In the code window lick on the TOOLS Menu, Select the bottom option .. .. Properties,
Go to the Protection Tab - click on the "Lock Project For Viewing" and add a password.

------

The last item on my tools menu in the code-window is 'digital signature'. I looked around and didn't see what you were talking about.

I may have just figured out a major part of the problem here, I am using a .mdb file. Am I supposed to be using .ADP?

 

[VBslammer]

When you go to the permissions dialog, you need to select "Query" in the Ojbect Type combobox, then hold down the CTRL key and click all the queries you want to deny permissions for, then click Apply.

I would use Groups rather than individual users though, because setting the permissions for every object in the database can be tedious once, and nearly impossible to duplicate for dozens of individual users. Think about making changes: if you make a change to the permissions, you have to do it for every individual account. With groups you can just change the group and be done. I usually supply a default set of groups for any database I deliver, and let the site admins decide who they want to put in which group.

Typical groups could be:
[ul][li]Guests[/li][li]ReadOnlyUsers[/li][li]FullDataUsers[/li][li]PowerUsers[/li][li]Excecutives[/li][li]SiteAdmins[/li][/ul]
I've worked on some systems where the group names were very specific to the application, which can make it easier to figure out where people belong:
[ul][li]PurchasingAgents[/li][li]MaintSupervisors[/li][li]ForkLiftTechs[/li][li]DataClerks[/li][li]Developers[/li][/ul]



VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[MBorofsky]

Hello again. I just completed going through all the different possible options here and have decided that an MDE file is the way to go. Unfortunately my MDE file conversion seems to freeze up whenever I run it. I did get it to generate the file once. I ended up having to make some changes to code as referenced in a forum elsewhere:

Dim DB as DAO.Database
Dim RS as DAO.Recordset

Set DB = CurrentDB
Set RS = DB.OpenRecordset(...)

to

Dim RS as DAO.Recordset
Set RS = CurrentDB.OpenRecordSet(...)

After this conversion the MDE process worked fine once. Unfortunately it has stopped working again, it just kind of freezes as soon as it starts creating the file. I can't figure out what is wrong with the code that it wouldn't work. I have done all the little things I can think of like compacting and repairing the database and reseting my computer before trying it but to no avail. Are there any other strange blips out there that people know of?

 

[MBorofsky]

Never mind, I just figured out that I actually had to remove all DAO.Database declarations as well. Thanks for the input, it really helped.

Just as a side note, we decided not to go with the password protected queries because all you had to do was delete the password file which was exterior to the database! Not the most secure system....

I am working on a subroutine that will convert all of the queries into code automatically. It doesn't look like it will be too difficult. If anyone is curious about it I can post the code when I'm done.

 

[VBslammer]

Microsoft doesn't pretend that a database password is the ultimate in security, it's just a simple measure to keep average users out of your code. If you really need tight security it is provided as "integrated security with encryption." If you implement the full security system, deleting the workgroup security file renders the database useless - that works for me.

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[MBorofsky]

SELECT field1, field2, etc
FROM table1
INNER JOIN
[SELECT DISTINCT fieldX, fieldY
FROM tableZ
WHERE fieldY Like '%SDF%']. As SubQ
ON table1.field1 = SubQ.fieldX





The above is a query that calls a query. What do I do if a query calls a query that calls a query? I had originally assumed that I would:



SELECT field1, field2, etc
FROM table1
INNER JOIN
[SELECT DISTINCT SubQ2.fieldX, SubQ2.fieldY FROM

[SELECT fieldA FROM tblA]. As SubQ2
]. As SubQ
ON table1.field1 = SubQ.fieldX