Re: SQL Svr security not enuf: finer grain, more auditing?

In article <5e275l$i3h_at_miso.wwa.com>, From knight_at_sashimi.wwa.com, the following was written:
>
> 1. Access restriction
> Allow row and column level restrictions on SELECT, UPDATE, INSERT and
> DELETE. Each user belongs to 8 different security classes, in each of
> which he/she may have varying levels of access for each type of
> activity (select, update, insert, delete). Apart from all of this, the
> users may be given global rights for the 4 types of access.

Part One (columns): This is possible using column-level access restrictions. This restricts the group of users sec_class_D from modifying the primary key in authors, and from deleting any rows:

grant all on authors to sec_class_D
revoke delete on authors from sec_class_D revoke update on authors(au_id) from sec_class_D

Part Two (rows): Consider using a view with a CASE expression that evaluates the user name/user id. I don't know of any other way to implement vertical security than through a view. Properly implemented, it should run quite fast. Let me know if you want help. > 2. Full audit trail > Ability to create an audit trail of activity including the SQL > statements issued by clients, and be able to either have a full trail > (ALL activity) or a trail by user and/or by type of activity.

Don't know a lot about this; have you looked at xp_sqltrace?

--
02/18/97 11:45
Ben McEwan
Geist, LLC    -    http://www.metis.com/~geist
bmcewan_at_metis.com.