Limit table content by user group/profiles/rules?

[Former Member]

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?

[VolkerBarth · Accepted solution]

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

• is statically (say, some users are only allowed to access some rows, others can access all rows - as in the docs samples) or
• is dynamically (based on some mapping of users to particular CAT_IDs, they are allowed to access only those rows with that particular CAT_ID values).

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.
When adding users, you would simply add an entry in MyUserMapping, and they can access "their" data.

We do use this approach to filter the access to customer data for the according sales representatives (in a 1:n relationship here).

[MCMartin]

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.

[graeme_perrow]

I think he's asking about restricting the rows of the resultset, not the columns.

[MCMartin]

You are right, eh ... now he knows both 😉