Re: Granting access to rows in a table

Vaclav Kolar wrote: > > Hello ! > > I'm using SQL server Interbase and I will use SQL servers Informix, MS > SQL, Oracle and Sybase. Interesting that you don't mention Informix in your list of illustrious databases. I will restrict my answer to C.D.I. Any answers I supply may show an Informix bias but the SQL commands are fairly standard, I think. > And my questions: > 1. How can I grant user access to a table, to columns in a table and > to rows in a table? For access to tables and columns, you have the SQL commands GRANT and REVOKE. I will not launch into a lecture on how to use them. FOr restricting row access - e.g. User X may access only those rows where CITY = "Kalamazoo"- you need a view. Revoke all privileges on a table from the general population (public) and create a view for each logical grouping of your rows in the table. Then GRANT access to your user groups to one view at a time. The permissions of the view will override the non-permissions on the table, but only for those rows and columns listed in the view. > 2. In Interbase read-only views can be updated by using a combination > of user-defined referential constraint, triggers, and unique indexes. > Is anable this feature in other SQL servers? When you need to do the update, call a stored procedure that uses the name of the original table[s] and was created by someone who has the privilege of writing to the underlying table[s] and has granted permission to execute the procedure. But don't go brazenly overriding the view! In Informix, any view that has a join or a pseudo column is unwritable. Often, the write would not even make sense or would omit primary key info from the underlying tables. -- -- Jake (In pursuit of undomesticated semi-aquatic avians) +---------------------------------------------------------------+ |Insofar as manifestations of functional deficiencies are agreed| |by any and all concerned parties to be imperceivable, and are | |so stipulated, it is incumbent upon said heretofore mentioned | |parties to exercise the deferment of otherwise pertinent | |maintenance procedures. | +------------------- A Legal Minded Engineer (hardyharhar.com) -+