Subject RE: [IBO] row selective access - looking for best solution
Author Kaputnik
why not retreiving the table through a stored procdure insted of a plain
based on, say, the user-name or the user-role, the stored procedure can
limit the access to the table, and select-rights to the table itself could
be revoked completely.

The SP can then construct a result-set which contains only the blobs the
user is allowed to see.
YOu can e.g. grant roles for all different doc-types to a user but not use
the roles for table-security at all.
A pretty simple view on the system tables can get the SP the list of all
granted roles for a user and based on this info it can then work.
No dependency on the ISC4 at all.


Nick Josipovic

CRM Administration
Prof. Dr. Franz Steffens
68163 Mannheim
Phone: ++49 621 181-1621
Fax: ++49 621 181-1618

> -----Original Message-----
> From: spou [mailto:spou@...]
> Sent: Thursday, January 03, 2002 9:13 PM
> To:
> Subject: [IBO] row selective access - looking for best solution
> Hi there people!
> First, and for all, I wish a very good year 2002. May all your code
> compile without error!
> That beeing said, I would also like to submit a problem I have. I
> dont think it fits anywhere in the IB support lists I am aware of, but
> we've got knowledgable people here, which I hope can help clear things
> for me.
> And there it is:
> I have this soft I've been working on for quite a while now, it stores
> various files for quick search and viewing. I'd like to add some kind
> of security by type of documents.
> In the db, I got the blob which stores the files, a column stating the
> document type in clear, and an other one in "code". I spare you the
> non related columns.
> document_type_descript doc_type_id blob
> management_word_doc A XXX
> scanned_bitmap B XXX
> QA_report C XXX
> Accounting_excel_doc D XXX
> shipping_forms E XXX
> ect...
> I would like a user to be allowed to only view *some* type of
> document, as in:
> username type_allowed
> Regular_luser B,C,D
> QA_inspector C
> Big_cheese A,B,C,D,E
> bean_counter D,C
> foam_peanut_guy E,C,B
> I plan to have an administrative applet to do that permission mix and
> match, as well as create/edit/modify a user. users *may* have
> password edit capabilities, but not yet.
> ------
> my question relates to where should I store the permissions ?
> I could create a parallel table into the database, with the username
> and types allowed, which would need to somehow synchronize with the
> user DB (ISC4.GDB) so when a user is deleted there, then it is deleted
> from the parallel DB (via the admin applet). The admin apps beeing
> the only one requiring 2 db, the client relying only on the main db
> OR
> I could expand the user DB (ISC4.GDB) so everything related to a user
> would be stored in the same place. It requires access to 2 different
> DBs (a hot topic lately) by the client, and could bring other
> surprises I'm not aware of yet.
> I've been thinking about it, and asking around for advice, and both
> suggestions got pros and cons. All that to say that I'm nowhere
> further today than I was a week ago.
> Has anyone been confronted with such a problem ?
> Any advice / suggestions / pointers / URL to help me ?
> TIA,