Skip to content

Using database groups to manage permissions in Sybase® IQ

July 7, 2011

www.arbime.com 

Introduction

In Sybase IQ, you can assign permission on database objects to each user of the database or, you can assign permissions to groups, give users membership in the groups, the members of a group will inherit the group permissions.

The following steps use the group approach and have been tested in Sybase IQ 12.6 ESD#3 and Sybase IQ 15.2 ESD#2.

Procedure

1. Create the group

grant connect to grpName
grant group to grpName

2. Grant permissions to the group

grant select, references on test.Table1 to grpName
grant select, references on test.Table2 to grpName
grant select, references on test.Table3 to grpName
grant select, references on test.TableN to grpName

grant execute on test.sProcedure1 to grpName

3. Create the users

grant connect to user1 identified by password
grant connect to user2 identified by password
grant connect to user3 identified by password

4. Add the users to the group

grant membership in group grpName to user1
grant membership in group grpName to user2
grant membership in group grpName to user3

Verify Permissions

5. List all the groups on the database:

select user_id, user_name
from sysuserperms
where user_group = ‘Y’

6. List of all users in grpName

select * from sysgroups
where group_name = ‘grpName’

On the next steps, I assume the user_id for grpName returned on step 5 is ‘123’

7. Verify the permissions on tables and views granted to the group

select t.table_name, t.table_type, p.selectauth, p.insertauth,
p.deleteauth, p.updateauth, p.alterauth, p.referenceauth
from systableperm p, systable t
where p.stable_id = t.table_id
and grantee = 123

8. Verify permissions on stores procedures

select s.proc_name
from sys.sysprocperm p, sys.sysprocedure s
where p.proc_id = s.proc_id
and grantee = 123

Voilà , now you can use groups to manage your permissions.

www.arbime.com

Advertisements

From → SAP IQ

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: