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.

Users running 'bcp -E' need sa_role?

4 posts in General Discussion Last posting was on 2013-01-15 14:24:22.0Z
Michael Heaney Posted on 2013-01-14 22:37:31.0Z
From: Michael Heaney <mheaney@jcvi.org>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:17.0) Gecko/20130107 Thunderbird/17.0.2
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Users running 'bcp -E' need sa_role?
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: <50f488ab$1@forums-1-dub>
Date: 14 Jan 2013 14:37:31 -0800
X-Trace: forums-1-dub 1358203051 172.20.134.152 (14 Jan 2013 14:37:31 -0800)
X-Original-Trace: 14 Jan 2013 14:37:31 -0800, vip152.sybase.com
Lines: 17
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31646
Article PK: 1158994

We have tables with identity fields, and need to load them quickly with
millions of rows of data. I thought a good solution would be to create
flatfiles with precomputed identity values, then upload using bcp and
the -E option.

Unhappily, this will not work for regular users:

Server Message: SYBPROD - Msg 10369, Level 14, State 2:
Permission denied. You must be either the database or the object owner
or have sa_role to perform this operation.

Obviously I'm not going to grant 'sa_role' to normal users. Is there a
way to bypass this restriction?


Michael Heaney
JCVI


Bret Halford Posted on 2013-01-14 23:14:01.0Z
From: Bret Halford <bret.halford@sap.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:17.0) Gecko/20130107 Thunderbird/17.0.2
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Users running 'bcp -E' need sa_role?
References: <50f488ab$1@forums-1-dub>
In-Reply-To: <50f488ab$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: <50f49139$1@forums-1-dub>
Date: 14 Jan 2013 15:14:01 -0800
X-Trace: forums-1-dub 1358205241 172.20.134.152 (14 Jan 2013 15:14:01 -0800)
X-Original-Trace: 14 Jan 2013 15:14:01 -0800, vip152.sybase.com
Lines: 32
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31647
Article PK: 1158996


On 1/14/2013 3:37 PM, Michael Heaney wrote:
> We have tables with identity fields, and need to load them quickly with
> millions of rows of data. I thought a good solution would be to create
> flatfiles with precomputed identity values, then upload using bcp and
> the -E option.
>
> Unhappily, this will not work for regular users:
>
> Server Message: SYBPROD - Msg 10369, Level 14, State 2:
> Permission denied. You must be either the database or the object owner
> or have sa_role to perform this operation.
>
> Obviously I'm not going to grant 'sa_role' to normal users. Is there a
> way to bypass this restriction?
>
>
> Michael Heaney
> JCVI

In ASE 15.7 ESD 2 and above there is a new "granular permissions"
feature that would allow you to grant "IDENTITY_INSERT" to specific
logins. It requires a license, ASE_PRIVACY.

http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc01165.1572/doc/html/mas1337275700545.html



--
Bret Halford
Support Architect, ASE Tactical Support Team, AGS Primary Support
Sybase, Inc., an SAP Company
385 Interlocken Crescent, Suite 300, Broomfield, Colorado, 80021


Michael Peppler (Team Sybase) Posted on 2013-01-15 05:53:15.0Z
From: Michael Peppler (Team Sybase) <mpeppler@peppler.org>
Newsgroups: sybase.public.ase.general
Message-ID: <50f4eecb@forums-1-dub>
References: <50f488ab$1@forums-1-dub>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
Subject: Re: Users running 'bcp -E' need sa_role?
User-Agent: Unison/2.1.10
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 14 Jan 2013 21:53:15 -0800
X-Trace: forums-1-dub 1358229195 172.20.134.152 (14 Jan 2013 21:53:15 -0800)
X-Original-Trace: 14 Jan 2013 21:53:15 -0800, vip152.sybase.com
Lines: 29
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31649
Article PK: 1159005

On 2013-01-14 22:37:31 +0000, Michael Heaney said:

> We have tables with identity fields, and need to load them quickly with
> millions of rows of data. I thought a good solution would be to create
> flatfiles with precomputed identity values, then upload using bcp and
> the -E option.
>
> Unhappily, this will not work for regular users:
>
> Server Message: SYBPROD - Msg 10369, Level 14, State 2:
> Permission denied. You must be either the database or the object owner
> or have sa_role to perform this operation.
>
> Obviously I'm not going to grant 'sa_role' to normal users. Is there a
> way to bypass this restriction?

You can get below sa_role to "just" dbo. It's not great, but it is at
least a less of a security risk.

Either use a generic login that is aliased to dbo in the DB, or create
some logic that lets your users temporarily get dbo access (e.g. via
sp_addalias) as part of the loading process.

Of course if you are in 15.7#2 then the granular permissions that Bret
mentioned is a much better soluton.

Michael


"Mark A. Parsons" <iron_horse Posted on 2013-01-15 14:24:22.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Users running 'bcp -E' need sa_role?
References: <50f488ab$1@forums-1-dub>
In-Reply-To: <50f488ab$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 130112-1, 01/12/2013), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <50f56696@forums-1-dub>
Date: 15 Jan 2013 06:24:22 -0800
X-Trace: forums-1-dub 1358259862 172.20.134.152 (15 Jan 2013 06:24:22 -0800)
X-Original-Trace: 15 Jan 2013 06:24:22 -0800, vip152.sybase.com
Lines: 34
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31651
Article PK: 1159024

While I can think of a few kludgy workarounds (eg, have user-generated file passed to a DBA-owned shell script which
performs the bcp/-E; have user-generated file loaded into a scratch table that does not have an identity column, then
have DBA/dbo perform an insert/select operation; etc), I'd be more interested in your table design ...

- how are your users obtaining their identity values?

- are you constantly resetting the table's identity_burn_max value or have you set it artificially high (for normal
INSERTs) while users are inserting relatively low numbers?

- do you need to insure uniqueness of the identity values or are you allowing duplicates?

- what proportion of this table is populated via user-generated bcp files vs normal INSERTs?

- what version of ASE are you running?

With such a large volume of user-generated identity values I'm wondering why this table has an identity column to start
with, ie, what benefit does the identity column provide?

On 01/14/2013 15:37, Michael Heaney wrote:
> We have tables with identity fields, and need to load them quickly with millions of rows of data. I thought a good
> solution would be to create flatfiles with precomputed identity values, then upload using bcp and the -E option.
>
> Unhappily, this will not work for regular users:
>
> Server Message: SYBPROD - Msg 10369, Level 14, State 2:
> Permission denied. You must be either the database or the object owner or have sa_role to perform this operation.
>
> Obviously I'm not going to grant 'sa_role' to normal users. Is there a way to bypass this restriction?
>
>
> Michael Heaney
> JCVI