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?

asked 04 May '11, 01:55

Kman's gravatar image

Kman
1114411
accept rate: 0%

edited 08 Jan '13, 15:53

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819


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).

permanent link

answered 04 May '11, 03:45

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

edited 04 May '11, 03:47

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.

permanent link

answered 04 May '11, 02:47

Martin's gravatar image

Martin
9.0k130169257
accept rate: 14%

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

(04 May '11, 06:55) Graeme Perrow
Replies hidden
1

You are right, eh ... now he knows both ;-)

(04 May '11, 10:05) Martin
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×108
×31

question asked: 04 May '11, 01:55

question was seen: 5,129 times

last updated: 08 Jan '13, 15:53