Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cornall
Posting Yak Master
148 Posts |
 Posted - 2009-11-02 : 06:21:28
|
| Hi,I am looking to write a number of views across our LOBS(Line of Business systems) to be used in data definitions for MOSS 2007.I want this to be as maintainable and secure as possible. I was thinking of creating a MOSSLOB database which would contain my views accross our systems with the MOSSLOB account only having select granted on the views in this centraliased database.I don't want to and don't think it is advisable to set up database chaining so I am wondering is there any way around granting select on the underlying tables to the MOSSLOB account?Effectively what I want to do is grant the view select on the tables and grant the user select on the view! This works fine if the view is in the same database as the underlying tables but in my design they won't be and often they will be accross databases.Is there any best practice for what I am trying to achive?Cheers D |
|
|
cornall
Posting Yak Master
148 Posts |
Posted - 2009-11-02 : 07:23:39
|
| Ok here is my solution not sure if this is huge overkill but I want to secure my bussiness systems.SERVER1 (contains all the bussiness system DBs) Account MOSSLOB1SERVER2 (contains my MOSS view database) Account MOSSLOB21. Grant select to the account MOSSLOB1 on the tables within the bussiness systems for which I want to create views on SERVER1.2. Create a linked server that uses the MOSSLOB1 connection to SERVER13. Create the view on SERVER2 using 4 part names SERVER1.database.owner.table4. Grant select to MOSSLOB2 on the view createdSo this means that the account MOSSLOB2 only has access to the view and not to the underlying tables. You would need to hijack the MOSSLOB1 account to gain access to the underlying tables.To me this is more secure as the MOSSLOB1 credentials are stored in the Sharepoint 2007 bussiness application definition.Any thoughts? Am I being way over the top here! |
 |
|
|
cornall
Posting Yak Master
148 Posts |
Posted - 2009-11-02 : 07:32:47
|
| Ok scratch the above idea!! Because I have entered security credentials for my linked server MOSSLOB2 can simply do a SELECT * FROM SERVER1.database.owner.table and it has the same rights as MOSSLOB1.Man this is a nightmare surely other people want to do similar? |
|
|
|
|
|
|
Best Practice View location and security