Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Create a user group in ASE 15.5 for read only purpose

3 posts in General Discussion Last posting was on 2011-12-02 17:11:25.0Z
RayLee Posted on 2011-11-30 17:53:28.0Z
From: RayLee <pp@hot.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:8.0) Gecko/20111105 Thunderbird/8.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Create a user group in ASE 15.5 for read only purpose
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ed66d98$1@forums-1-dub>
Date: 30 Nov 2011 09:53:28 -0800
X-Trace: forums-1-dub 1322675608 10.22.241.152 (30 Nov 2011 09:53:28 -0800)
X-Original-Trace: 30 Nov 2011 09:53:28 -0800, vip152.sybase.com
Lines: 8
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30726
Article PK: 73615

Hi,

Is it possible to create a user group in ASE 15.5 such that the user id
in this user group only grant with "select" right for all tables in
this server? No need to grant "select" right for each table.

Thanks,
Ray


Bret Halford Posted on 2011-11-30 19:03:00.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:8.0) Gecko/20111105 Thunderbird/8.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Create a user group in ASE 15.5 for read only purpose
References: <4ed66d98$1@forums-1-dub>
In-Reply-To: <4ed66d98$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ed67de4$1@forums-1-dub>
Date: 30 Nov 2011 11:03:00 -0800
X-Trace: forums-1-dub 1322679780 10.22.241.152 (30 Nov 2011 11:03:00 -0800)
X-Original-Trace: 30 Nov 2011 11:03:00 -0800, vip152.sybase.com
Lines: 36
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30727
Article PK: 73617


On 11/30/2011 10:53 AM, RayLee wrote:
> Hi,
>
> Is it possible to create a user group in ASE 15.5 such that the user id
> in this user group only grant with "select" right for all tables in
> this server? No need to grant "select" right for each table.
>
> Thanks,
> Ray

There is no "grant select on all tables" option. You can create a group
and grant select on each table to the group. You can
then assign users to the group and avoid having to grant
select on each table to them individually.

I would use roles rather than groups though, there is more flexibility.
The process is otherwise the same.

It isn't hard to generate a script that grants select on all
tables. When new tables are created, permission on the new table
will have to be granted.


isql -Usa -P -Dmydatabase -o grant_selects.sql -b << EOF
set nocount on
go
select "grant select on " + name + " to my_role" as "--"
from sysobjects
where type = 'U'
go
print "go"
go
EOF

isql -Usa -P -Dmydatabase -i grant_selects.sql


RayLee Posted on 2011-12-02 17:11:25.0Z
From: RayLee <pp@hot.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:8.0) Gecko/20111105 Thunderbird/8.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Create a user group in ASE 15.5 for read only purpose
References: <4ed66d98$1@forums-1-dub> <4ed67de4$1@forums-1-dub>
In-Reply-To: <4ed67de4$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ed906bd@forums-1-dub>
Date: 2 Dec 2011 09:11:25 -0800
X-Trace: forums-1-dub 1322845885 10.22.241.152 (2 Dec 2011 09:11:25 -0800)
X-Original-Trace: 2 Dec 2011 09:11:25 -0800, vip152.sybase.com
Lines: 41
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30736
Article PK: 73627

Thanks a lot for your advice.

On 12/1/2011 3:03 AM, Bret Halford wrote:
> On 11/30/2011 10:53 AM, RayLee wrote:
>> Hi,
>>
>> Is it possible to create a user group in ASE 15.5 such that the user id
>> in this user group only grant with "select" right for all tables in
>> this server? No need to grant "select" right for each table.
>>
>> Thanks,
>> Ray
>
>
> There is no "grant select on all tables" option. You can create a group
> and grant select on each table to the group. You can
> then assign users to the group and avoid having to grant
> select on each table to them individually.
>
> I would use roles rather than groups though, there is more flexibility.
> The process is otherwise the same.
>
> It isn't hard to generate a script that grants select on all
> tables. When new tables are created, permission on the new table
> will have to be granted.
>
>
> isql -Usa -P -Dmydatabase -o grant_selects.sql -b << EOF
> set nocount on
> go
> select "grant select on " + name + " to my_role" as "--"
> from sysobjects
> where type = 'U'
> go
> print "go"
> go
> EOF
>
> isql -Usa -P -Dmydatabase -i grant_selects.sql