Solved

Need to use view or stored procedure on SQL Server in OpenOffice Calc/Base

Posted on 2008-06-19
2
1,389 Views
Last Modified: 2013-12-27
I have a substantial Excel Workbook that it almost entirely converted to Calc.  One of the sheets in the workbook dynamically queries simple employee data from a table in one of our HR systems.  In Excel, the sheet uses a QueryTable that is linked to an ASPX located on our intranet.  Unless I'm mistaken, Calc doesn't have that functionality (you can only link to *.html...this is an option for a solution for this issue, but one I'm avoiding for several reasons.)

Instead I registerd an ADO connection to the Employees table in the SQL server, built the query I needed in Base, dropped it on the worksheet, and put a refresh macro in the createdocument event (I'm using templates over a network).  This works well as far as obtaining current data is concerned, but it's not such a good idea for security reasons.  Registering the ADO connection to the server gives any user that opens the spreadsheet access to ALL of the tables in this particular system just by hitting F4.  This includes personal information that can't be shared.

I'll need access to other systems for other workbooks I'm converting, so rather than get into a tiresome routine of manipulating SQL server login access, I created a new DB (on a different SQL server) called "OpenOffice".  The idea was that I would just create a view in this DB that gave me only the Employee Info that I need.  Then I could register this DB in base, and distribute across the network without security  concerns (The .odf is on a shared drive).  The problem is that base doesn't see views, and I'm wondering now about stored procedures as well.  Does anybody have any insight on this issue?  I'm willing to  explore other solutions as well.

         
0
Comment
Question by:largeone
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 2

Accepted Solution

by:
Tdl earned 250 total points
ID: 21923876
Dear largeone.

Do I get you correctly that the users of the spreadsheet only read data?
Why don't you create a view in the original database which only consists of the data the users are allowed to see? Then remove the normal (do you use public?) read permission on the table itself and add read permission on the view for all the users (use a group for simpler administration). Then they can only read what they're supposed to.
This is a often recommended design, have the base tables with no access to the users directly and use views or stored procedures.

Regards,
Marc
0
 

Author Comment

by:largeone
ID: 21928417
Marc,

Thanks for the input.  Part of the reason I was hoping to avoid a routine of SQL server permissions maintenance was that I don't fully understand them.  I can't say I have a great grasp on them now either, but since I posted this question, I've learned more (enough to do what I need anyway).  I ended up using a restricted access login (that already existed) and created several queries in base that I use to access the data I need.  I believe this is similar to what you suggested.    

My concern wasn't that they could see and/or edit data in the table that I need them to see.  It was that registering the database in OOo would leave all of the other objects exposed as well.  I also set up a table filter in the base conection, but it seems like anyone who knows a little can edit that.  If that's not the case, I'd love to hear how other people approach this.  

I'm going to leave this open for a while if that's ok.  I've sort of gotten past this issue, but I'm still curious to see if there are more suggestions.      
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question