Implementing Data Warehouse

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hello, all of you, DB Gurus!

I would like you to give your opinion about the way I'm implementing Data
Warehouse on my company, and the ways to enhance it.

If this is not the best place to publish it please let me know, as far as
this is an important question for the future of information architecture for
our enterprise, and I would like to receive a real helper hand... THANKS IN
ADVANCE!!! 8-D

Let's see... For that, I'm going to explain as schematic and brief as I can
how it is implemented today:

We have one SQL Server 2000 with several different databases from 3rd party
programs called SIGRID2, GPCNBD, and DOCUWARE. The database owner for all
databases is dbo, EXCEPT for database GPCNBD that has its own owner called
GPCN, as all the objects (tables, views, etc.) inside it. The structure and
permissions for these databases are untouchable, as their associated programs
are the only ones that can manipulate them.

We extract and mix data from all the databases in several ways (into Excel,
crystal reports, etc.), and every time we need this information it needs to
be "IN REAL TIME"; I mean, that when we request the data, it cannot be for
example, from 10 minutes later...

We also do not have any OLAP technology installed nor working from the time
being.

The path to reach the data, usually is:

From .NET software I create, I'm accessing objects (views, stored
procedures, etc) from OUR custom database called LEITER, on the same SQL
SERVER where the other databases reside.

The views and stored procedures on this database look for data FROM THE
OTHER DATABASES. As OWNERSHIP CHAINING for all the databases on the sql
server are enabled, permissions are guaranteed to work.

Note: when I access to database GPCNBD, as it has another owner, I create my
views on Leiter with the same owner in GPCNBD for not to break the ownership
chaining. I'm also not using dynamic sql as far as it would break ownership
chaining too...

We also have a windows network domain called LEITERMI, and people and groups
associated to it.

For the PERMISSIONS CHAIN to work, I create a group, for example GROUP_APP1,
for each new application, APP1, and add the people that will use APP1 to
this group.

The user starts APP1 and it accesses the database object on LEITER. As the
windows user belongs to GROUP_APP1, and this group is added as a login in the
SQL SERVER and it has explicit permissions to the objects in LEITER, it can
access to data on LEITER's object and the other databases (remember ownership
chaining)

This way, the steps I'm following with each new application are always:

- Create the domain's group for the new application and assign network users
to it
- Add the group as a login on the sql server
- Add the group as a user FOR ALL THE DATABASES I'm accessing on, with
PUBLIC access (I'm NOT touching individual permissions for other objects than
our OWN database called LEITER)

- Create views / stored procedures etc, on LEITER db or take advantage of
the ones already created (I'm usually creating a view for each table on the
3rd party database; I'm also create more complex views sometimes, of course)

- Assign permissions on LEITER db for these objects, to the new domain group.

So when the windows user accesses the objects on LEITER using windows
authentication, there are no problems as ownership chaining works.


Drawbacks:

3rd party creators of these applications assured us that they will NEVER
change the structure of their database, but it is risky. The only thing they
told us that can happen is that some information and fields could be added.

Although I'm not changing the actual structure or permissions for these 3rd
party databases, I'm still adding the group for the whole database with
PUBLIC access... still... fair play ?

I'm waiting eager for your answer... 8-D

Thanks again for causing you a terrible headache 8-D,

--
Roger Tranchez
MCTS
..NET 2005 and DB developer
.

Relevant Pages

  • Re: Configuring SQL 2000 for use with ESRI ArcSDE
    ... You can assign SQL Server users to database roles using sp_addrolemember: ... Permissions are entirely application specific. ... vendor for clarification of security requirements. ... I have Two databases 3 Roles ...
    (microsoft.public.sqlserver.server)
  • Re: Permission to restore databases and access them without being
    ... dbcreator to the login so that you can restore the databases. ... " in SQL Server 2005 Books Online: ... assign your wanted permissions such as db_reader/db_writer ...
    (microsoft.public.sqlserver.security)
  • Re: Hiding database
    ... Jasper Smith (SQL Server MVP) ... >>>Can someone explan how to hide databases in enterprise manager. ... >> permissions are granted to these users. ... > have access rights to one of them. ...
    (microsoft.public.sqlserver.tools)
  • Re: Security Script
    ... You when you restore a database, or attach it, SQL Server maintains the ... logins but removes all permissions to databases and their objects. ...
    (microsoft.public.sqlserver.security)
  • Re: public role???
    ... What exactly do you mean by "new users have access to all DBs"? ... If you mean that new users can see the names of the other databases, ... Documentation on the "guest" account is available in the SQL Server Books ... > permissions. ...
    (microsoft.public.sqlserver.security)