Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2002

    Unanswered: Best data base design

    Hi all,
    Can any one of you help me out in giving the ideal database design for the following requirement.
    I will be having two heirachy of data. One which can be accesed by the master user and others by clients. There can be n number of clients who can be there. A client can view only his details and the masters.
    A master can view data of all the clients and the master. Each clients detail is expected to be kept in a different table.

    So in an apllication if the person who logs in is a master user we need to show details of all clients.
    If the person logs in as a client A then details of only his table is to be shown.
    Can any one get back on this,
    Thanking you in advance

  2. #2
    Join Date
    Aug 2002
    Wilmington, Delaware USA
    For what its worth ...

    I would only use Views to allow them to see the data, and stored procedures to update.

    For viewing data, I would create an association table with logins and allowed entities

    create table SecurityControl
    securitycontrol_id int identity not null ,
    user varchar(40) not null,
    company_no int not null

    then in the views i would join to it like so:

    from CompanyInfo ci
    join SecurityControl si on si.company_no = ci.company_no and suser_name() = si.user

    SQL 2000 does have row level security capability but
    1. I haven't used it enough to explain it.
    2. You didn't specific a version and this solution would be portable to most RDBMS's

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts