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.

New DB_READER and SA_READER roles?

2 posts in Product Futures Discussion Last posting was on 2002-11-15 21:44:23.0Z
Kai_Tomren Posted on 2002-11-14 12:34:00.0Z
From: Kai_Tomren
Date: Thu, 14 Nov 2002 07:34:00 -0500
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: New DB_READER and SA_READER roles?
Message-ID: <C9D17BBD1B7A36B10045081085256C71.0045083F85256C71@webforums>
Lines: 30
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:250
Article PK: 93424

Hi.

Many (all?) sybase sites struggle with a problem causing a lot of
administration, frustration and lost time. The problem stems from the fact
that a (small) group of people (application developers, "super-users",
"dba's to be", analysts/designers) need permission to READ everything in
spesific database(s) or servervide. The select permission on tables of
course is the most important.

I guess this is solved in one of two bad ways:

By giving them far too much (like dbo or dbo alias or sa_role) causing dba
"nightmare's" or by a very great lot of "granting select table by table"
(often forgotten...).

I know that the new role based authorization that came with 12.x probably
could be used to reduce the problem, but it would still take a lot of
permission administration.

The problem would be solved with a new DB_READER role (and possibly a
similar servervide SA_READER_ROLE). Logins with DB_READER role on db x
should have "all permissions that the DBO(alias) has on db x, EXCEPT the
permission to CHANGE anything". The new role must be type "zero
administration", example: must include all existing and new tables
automatically.

Are you familiar with this problem?
Do you like the suggestion?

Regards
Kai Tomren


Mike Harrold Posted on 2002-11-15 21:44:23.0Z
Subject: Re: New DB_READER and SA_READER roles?
References: <C9D17BBD1B7A36B10045081085256C71.0045083F85256C71@webforums>
X-Newsreader: trn 4.0-test75 (Feb 13, 2001)
From: ao@shell.core.com (Mike Harrold)
Originator: ao@shell.core.com (Mike Harrold)
Message-ID: <B$TupAPjCHA.161@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
Date: Fri, 15 Nov 2002 16:44:23 -0500
Lines: 44
NNTP-Posting-Host: shell.core.com 169.207.1.89
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:245
Article PK: 93418

In article <C9D17BBD1B7A36B10045081085256C71.0045083F85256C71@webforums>,

<Kai_Tomren> wrote:
>
>Hi.
>
>Many (all?) sybase sites struggle with a problem causing a lot of
>administration, frustration and lost time. The problem stems from the fact
>that a (small) group of people (application developers, "super-users",
>"dba's to be", analysts/designers) need permission to READ everything in
>spesific database(s) or servervide. The select permission on tables of
>course is the most important.
>
>I guess this is solved in one of two bad ways:
>
>By giving them far too much (like dbo or dbo alias or sa_role) causing dba
>"nightmare's" or by a very great lot of "granting select table by table"
>(often forgotten...).
>
>I know that the new role based authorization that came with 12.x probably
>could be used to reduce the problem, but it would still take a lot of
>permission administration.
>
>The problem would be solved with a new DB_READER role (and possibly a
>similar servervide SA_READER_ROLE). Logins with DB_READER role on db x
>should have "all permissions that the DBO(alias) has on db x, EXCEPT the
>permission to CHANGE anything". The new role must be type "zero
>administration", example: must include all existing and new tables
>automatically.
>
>Are you familiar with this problem?
>Do you like the suggestion?
>
>Regards
>Kai Tomren

This is interesting, but for the record this is _not_ a Sybase-specific
problem. 'Orable has the same issue. I assume other DBMS do as well.

I'm not sure the new role-based admin makes much of a difference here
as you could always have granted the select permission to public in the
past.

/Mike