Is it possible to use groups/profiles in Sybase Adaptive Server Anywhere to limit what content of a table is visible for a user?
Let's say we have a table with a CAT_ID column where I would like to allow adjust which rows is visible for a certain group of users?
So if I connect with group user01 a select * from TABLE would return a different resultset than if I were connected with a user belonging to user02 ??
I know this is possible in other db platforms, but is this possible in Sybase?
If you want to filter which rows (in contrast to which columns) of a table a particular user/group can access, this can be done with the help of views.
The docs do contain some samples on this.
Basically, you have to decide whether the limitation
In the first case, you would usually just have to build a view for each group and limit the rows for that group:
create view user01.MyView as select * from MyTable where CAT_ID = 1 with check option; create view user02.MyView as select * from MyTable where CAT_ID = 2 with check option; create view user03.MyView as select * from MyTable -- is allowed to access all rows with check option;
So a "select * from MyView" would return different results based on which user is connected and (by omitting the owner name) which view gets used.
Note: The WITH CHECK OPTION clause is useful when users are allowoed to modify data - it assures they can only insert/update data in their according CAT_ID range.
For the second approach, you would basically have to map individual users to an ID and would use only one view that joins the rows to the desired ID:
-- here with a 1:1 mapping of username and ID (1:n might be useful, too) create table MyUserMapping ( username varchar(128) not null primary key, CAT_ID int not null); insert MyUserMapping values ('user01', 1); insert MyUserMapping values ('user02', 2); insert MyUserMapping values ('user03', 3); -- Note: The view does not use a join but a subquery in order to be updateable. create view MyView as select * from MyTable where CAT_ID in (select CAT_ID from MyUserMapping where username = current user) with check option;
Now each user would only have access to those rows of MyTable with the according rows.
We do use this approach to filter the access to customer data for the according sales representatives (in a 1:n relationship here).
You can restrict the visibility of columns in a table. see the
GRANT SELECT (column1,column2,...) ON table TO user
So select * can return more columns of a table for priviledged users.
answered 04 May '11, 02:47