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.

External Access to a table from other database

4 posts in General Discussion Last posting was on 2012-12-07 05:52:00.0Z
karthik Posted on 2012-12-06 05:24:54.0Z
Sender: 156e.50c0274a.1804289383@sybase.com
From: Karthik
Newsgroups: sybase.public.ase.general
Subject: External Access to a table from other database
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50c02c26.1634.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 5 Dec 2012 21:24:54 -0800
X-Trace: forums-1-dub 1354771494 172.20.134.41 (5 Dec 2012 21:24:54 -0800)
X-Original-Trace: 5 Dec 2012 21:24:54 -0800, 172.20.134.41
Lines: 35
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31584
Article PK: 1158160

Hi All,

I have two databases. db1 & db2.

db1:

I have created a group called "readwrite" in db1.
I have created a user called "uv1" in db1 and it belongs to
"readwrite" group.

db2:
I have created a group called "readwrite" in db1.
I don't have uv1 in this db.

There are around 50 tables in db2 and granted permission to
"readwrite" group for all those 50 tables.

when I execute any query from db1 to access db2 tables by
using
"uv1", i am getting the access denied error message.

Approach #1 :

I have provided grant permission to pulbic group for the
required tables and then accessed db2 tables from db1.

Approach #2 :

I have added "guest" user in db2 and then accessed db2
tables from db1.

apart from this, is there any external kind of login
available to access db2 tables from db1.

NOTE: I don't want to add the userid "uv1" in db2.


Michael Peppler (Team Sybase) Posted on 2012-12-06 05:54:03.0Z
From: Michael Peppler (Team Sybase) <mpeppler@peppler.org>
Newsgroups: sybase.public.ase.general
Message-ID: <50c032fb@forums-1-dub>
References: <50c02c26.1634.1681692777@sybase.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
Subject: Re: External Access to a table from other database
User-Agent: Unison/2.1.9
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 5 Dec 2012 21:54:03 -0800
X-Trace: forums-1-dub 1354773243 172.20.134.152 (5 Dec 2012 21:54:03 -0800)
X-Original-Trace: 5 Dec 2012 21:54:03 -0800, vip152.sybase.com
Lines: 54
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31585
Article PK: 1158161

You should probably look at roles to give you better
control/granularity over access permissions.

Roles apply to logins, rather than to users, so are consisten across databases.

Users and groups are limited to single database scope, and one user can
only be part of a single group (in addition to "public"), so this is
somewhat limited in functionality.

Roles have much more flexibility, can be hierchical (i.e. role1 is
granted to role2, and role2 is granted to login1, so login1 has role1
and role2).

Michael

On 2012-12-06 05:24:54 +0000, Karthik said:

> Hi All,
>
> I have two databases. db1 & db2.
>
> db1:
>
> I have created a group called "readwrite" in db1.
> I have created a user called "uv1" in db1 and it belongs to
> "readwrite" group.
>
> db2:
> I have created a group called "readwrite" in db1.
> I don't have uv1 in this db.
>
> There are around 50 tables in db2 and granted permission to
> "readwrite" group for all those 50 tables.
>
> when I execute any query from db1 to access db2 tables by
> using
> "uv1", i am getting the access denied error message.
>
> Approach #1 :
>
> I have provided grant permission to pulbic group for the
> required tables and then accessed db2 tables from db1.
>
> Approach #2 :
>
> I have added "guest" user in db2 and then accessed db2
> tables from db1.
>
> apart from this, is there any external kind of login
> available to access db2 tables from db1.
>
> NOTE: I don't want to add the userid "uv1" in db2.


Bret Halford Posted on 2012-12-06 16:23:22.0Z
From: Bret Halford <bret.halford@sap.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:16.0) Gecko/20121026 Thunderbird/16.0.2
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: External Access to a table from other database
References: <50c02c26.1634.1681692777@sybase.com> <50c032fb@forums-1-dub>
In-Reply-To: <50c032fb@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: <50c0c67a$1@forums-1-dub>
Date: 6 Dec 2012 08:23:22 -0800
X-Trace: forums-1-dub 1354811002 172.20.134.152 (6 Dec 2012 08:23:22 -0800)
X-Original-Trace: 6 Dec 2012 08:23:22 -0800, vip152.sybase.com
Lines: 38
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31590
Article PK: 1158173

A lesson from a recent support case involving roles:
If your clients login frequently (login, run transaction, log out,
repeat thousands of times a day) as opposed to infrequently
(login, run transactions all day, logout), don't assign
many roles to each login.

During login, ASE has to access the sysattributes table for each
role. This lead to performance problems traced to a bottleneck
on login on a busy system where there were 25 roles assigned
to the login account. Reducing the number of roles quickly
improved performance. (Rewriting the client to maintain connections,
or to go through an application server that maintained connections
would improve performance even more as login is a somewhat costly
operation).


-bret

On 12/5/2012 10:54 PM, Michael Peppler (Team Sybase) wrote:
> You should probably look at roles to give you better control/granularity
> over access permissions.
>
> Roles apply to logins, rather than to users, so are consisten across
> databases.
>
> Users and groups are limited to single database scope, and one user can
> only be part of a single group (in addition to "public"), so this is
> somewhat limited in functionality.
>
> Roles have much more flexibility, can be hierchical (i.e. role1 is
> granted to role2, and role2 is granted to login1, so login1 has role1
> and role2).
>
> Michael


Michael Peppler (Team Sybase) Posted on 2012-12-07 05:52:00.0Z
From: Michael Peppler (Team Sybase) <mpeppler@peppler.org>
Newsgroups: sybase.public.ase.general
Message-ID: <50c18400@forums-1-dub>
References: <50c02c26.1634.1681692777@sybase.com> <50c032fb@forums-1-dub> <50c0c67a$1@forums-1-dub>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
Subject: Re: External Access to a table from other database
User-Agent: Unison/2.1.9
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 6 Dec 2012 21:52:00 -0800
X-Trace: forums-1-dub 1354859520 172.20.134.152 (6 Dec 2012 21:52:00 -0800)
X-Original-Trace: 6 Dec 2012 21:52:00 -0800, vip152.sybase.com
Lines: 50
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31591
Article PK: 1158179

Indeed. At work we had logins with > 100 roles, each connection
creation could take a second or more, and really added a lot of load on
the server.

We saw a very significant drop in server load when we brought this down
to something more reasonable (i.e. between 2 and 10 roles per login).

And of course also tried to limit the number of new connections to the
server (i.e. ensure that applications keep their connections open)

On 2012-12-06 16:23:22 +0000, Bret Halford said:

> A lesson from a recent support case involving roles:
> If your clients login frequently (login, run transaction, log out,
> repeat thousands of times a day) as opposed to infrequently
> (login, run transactions all day, logout), don't assign
> many roles to each login.
>
> During login, ASE has to access the sysattributes table for each
> role. This lead to performance problems traced to a bottleneck
> on login on a busy system where there were 25 roles assigned
> to the login account. Reducing the number of roles quickly
> improved performance. (Rewriting the client to maintain connections,
> or to go through an application server that maintained connections
> would improve performance even more as login is a somewhat costly
> operation).
>
>
> -bret
>
>
>
> On 12/5/2012 10:54 PM, Michael Peppler (Team Sybase) wrote:
>> You should probably look at roles to give you better control/granularity
>> over access permissions.
>>
>> Roles apply to logins, rather than to users, so are consisten across
>> databases.
>>
>> Users and groups are limited to single database scope, and one user can
>> only be part of a single group (in addition to "public"), so this is
>> somewhat limited in functionality.
>>
>> Roles have much more flexibility, can be hierchical (i.e. role1 is
>> granted to role2, and role2 is granted to login1, so login1 has role1
>> and role2).
>>
>> Michael