Security at table level is not the problem!
What I really need is row-level security. Imagine a table with
millions of records and each user may only access a subset of
those records, even if they use Enterprise Manager or generic SQL.
The user interface of my application consists mainly of a dbgrid so I
can't use stored procedures (I would need different stored procs for
retrieving/storing/deleting and tDataset just doesn't offer that
I currently try to solve it with a view that matches the user
name with a list of rights (=list of userid & projectnr)
Create View MyVirtualTable as
Select * from Physicaltable P where exists
(select * from Rights R where (R.projectnr=P.projectnr)
and ((R.userid=current_user) OR (R.userid='ALL USERS'))
This should give me the possibility to lock "physicaltable"
completely to all users and grant access to this table only through
the "MyVirtualtable" view, which checks if the user has sufficient
rights for each desired record. Theoretically this should work.
Unfortunately the "create view" syntax is rather limited, I could
do much more in a stored procedure. I'm still experimenting,
(please remove the ".net" from my e-mail address)