EJB design: User-based DB Views and EJBs

  1. User-based DB Views and EJBs (1 messages)

    My current DB Schema has real DB users for each user in the system. We then have views that sit on top of each table that enforces other restrictions per user. How do I model this using EJBs. If I only map to the views, I am replicating huge amounts of data; but if I only map the underlying tables, I have to reimplement all the logic that goes into the views (which will be much slower).

    My only thought thus far, was to map the underlying tables in full, and only bring back the primary key for each view. When bringing back data from the underlying table, I will check that that row's primary key exists in the view before I return that row. Are there any other patterns/solutions for this problem.
  2. Hi,

    From the J2EE container, you can get the name (object of class Principal) for the user. (I suppose you have the same user in the application server as in the database.) If the name of the view contians, or can be derived, from the username you can generate SQL dynamically and execute it in the database. You cannot use PreparedStatement, so performance can be a little bad.

    This is definitely an unconventional solution, but with such a legacy i think you have good arguments for it :-)