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.

User Impersonation

2 posts in General Discussion Last posting was on 2009-08-17 06:32:45.0Z
Scott Kellish Posted on 2009-08-13 21:06:41.0Z
From: "Scott Kellish" <skellish@softsystemsolutions.com>
Newsgroups: sybase.public.ase.general
Subject: User Impersonation
Lines: 50
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a848061@forums-1-dub>
Date: 13 Aug 2009 14:06:41 -0700
X-Trace: forums-1-dub 1250197601 10.22.241.152 (13 Aug 2009 14:06:41 -0700)
X-Original-Trace: 13 Aug 2009 14:06:41 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28153
Article PK: 77398

Hi,
Our ASE 12.5.x server has a production database and 8-10 archive databases,
one per calendar year (salesdb = production, salesdb_arch07, salesdb_arch08,
...). Currently we have a number of stored procedures that are passed the
database name and using dynamic SQL access the named database to return
data. Up until now these procedures have been used by a very limited set of
user ids, but now new features require our general population of users
(150+) to use them to. These users exist in the production database but not
in any of the archive databases. DBA does not want to add them (there are 22
server instances around the world), and even if he did, he does not want to
grant them select permissions against any of these tables (in production or
archives) because he's worried about a user inadvertently doing a
multi-table join and hanging the system. This effectively prevents the use
of dynamic SQL.

Does anyone know if it is possible to have a stored procedure which will be
compiled by sa, that can impersonate a different user. I would like to have
a single user in the archive databases with permissions to its table, have
any user call a stored procedure which would then impersonate this user and
use dynamic SQL to access the appropriate archive database and return.

sp_procxmode takes care of the dynamic SQL permission but only in the same
database. I tried looking at set proxy but could not get it to work.

The current developer solutions is to have two connections to the database
from their apps, one using the normal user login (we use their real login
name for audit trails) and another connection logged in as a special user
who has permissions in the archive databases.


Thank You
Scott Kellish

--- Quote of the Minute ---

A wise man gets more use from his enemies than a fool from his friends.

_________________________________________
Scott Kellish, Owner
SoftSystem Solutions, LLC.
18 Ridge Road
Clark, New Jersey 07066 USA

Tel. +01 732 382 1873
Fax: +01 732 382 1873
Mobile: +01 732 261-5856
e-mail: skellish@softsystemsolutions.com
_________________________________________


Luc Van der Veurst Posted on 2009-08-17 06:32:45.0Z
From: "Luc Van der Veurst" <dba_azvub@hotmail.com>
Newsgroups: sybase.public.ase.general
References: <4a848061@forums-1-dub>
Subject: Re: User Impersonation
Lines: 64
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
x-mimeole: Produced By Microsoft MimeOLE V6.00.2900.3350
X-RFC2646: Format=Flowed; Response
X-Forwarded: by - (DeleGate/5.8.7)
X-Forwarded: by - (DeleGate/5.8.7)
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a88f98d$2@forums-1-dub>
Date: 16 Aug 2009 23:32:45 -0700
X-Trace: forums-1-dub 1250490765 10.22.241.152 (16 Aug 2009 23:32:45 -0700)
X-Original-Trace: 16 Aug 2009 23:32:45 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28170
Article PK: 77412

I'd add the guest user to the archive database and if everyone who can login
into the server may access the data, through a stored procedure, I'd grant
exec on the stored proc to public. If only a limited number of users should
be able to execute the SP, I'd add a server role, grant exec to the role,
and
grant the role to specific users.

Luc.

"Scott Kellish" <skellish@softsystemsolutions.com> wrote in message
news:4a848061@forums-1-dub...
> Hi,
> Our ASE 12.5.x server has a production database and 8-10 archive
> databases, one per calendar year (salesdb = production, salesdb_arch07,
> salesdb_arch08, ...). Currently we have a number of stored procedures that
> are passed the database name and using dynamic SQL access the named
> database to return data. Up until now these procedures have been used by a
> very limited set of user ids, but now new features require our general
> population of users (150+) to use them to. These users exist in the
> production database but not in any of the archive databases. DBA does not
> want to add them (there are 22 server instances around the world), and
> even if he did, he does not want to grant them select permissions against
> any of these tables (in production or archives) because he's worried about
> a user inadvertently doing a multi-table join and hanging the system. This
> effectively prevents the use of dynamic SQL.
>
> Does anyone know if it is possible to have a stored procedure which will
> be compiled by sa, that can impersonate a different user. I would like to
> have a single user in the archive databases with permissions to its table,
> have any user call a stored procedure which would then impersonate this
> user and use dynamic SQL to access the appropriate archive database and
> return.
>
> sp_procxmode takes care of the dynamic SQL permission but only in the same
> database. I tried looking at set proxy but could not get it to work.
>
> The current developer solutions is to have two connections to the database
> from their apps, one using the normal user login (we use their real login
> name for audit trails) and another connection logged in as a special user
> who has permissions in the archive databases.
>
>
> Thank You
> Scott Kellish
>
> --- Quote of the Minute ---
>
> A wise man gets more use from his enemies than a fool from his friends.
>
> _________________________________________
> Scott Kellish, Owner
> SoftSystem Solutions, LLC.
> 18 Ridge Road
> Clark, New Jersey 07066 USA
>
> Tel. +01 732 382 1873
> Fax: +01 732 382 1873
> Mobile: +01 732 261-5856
> e-mail: skellish@softsystemsolutions.com
> _________________________________________
>