Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

db2 privileges

Posted on 2004-03-22
2
2,545 Views
Last Modified: 2008-02-01
hi

how do i grant execute permission for stored procedure in db2

db2> grant execute on schemaname.spname to user xyz

i am getting SQL0204N  "schema.spname" is an undefined name.  SQLSTATE=42704

db2 >grant execute on package schemaname.spname to user xyz

i am getting SQL0204N  "schema.spname" is an undefined name.  SQLSTATE=42704

db2 > select procname from syscat.procedures where procschema='schemaname'
i am able to see the procedure.

how do i grant execute (or any) permission on stored procedure to an user

thanks
banu
0
Comment
Question by:banumaran
2 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 10660877
try

Select 'Grant EXEC on "' concat ProcShema concat '"."' concat procname
 concat '" to XYZ ' from syscat.procedures
 where procshema='schemaname'
order by 1

is the USER before xyz required?

which OS & UDB version?
0
 
LVL 13

Accepted Solution

by:
ghp7000 earned 125 total points
ID: 10663999
the stored procedure can be executed by anybody with privileges to the object(s) that the stored procedure references-you only grant execute on packages, not Sp's.

If the authorization ID has insufficient authority to perform the operation, an error (SQLSTATE 42502) is raised.

0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question