By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,226 Members | 1,027 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,226 IT Pros & Developers. It's quick & easy.

viewing privileges for stored procedures

P: n/a
Hi,

I'm new to DB2. I want to view all the exisiting privileges (i.e. which
users have which privilegens) for a given stored procedure that exists
on the database.

How do I do this?

Thanks in advance!

Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Andy S. wrote:
Hi,

I'm new to DB2. I want to view all the exisiting privileges (i.e. which
users have which privilegens) for a given stored procedure that exists
on the database.

How do I do this?

Thanks in advance!


The information you want is in syscat.routineauth; look at columns grantee,GranteeType, SpecificName,RoutineType and ExecuteAuth.

N. Shamsundar
University of Houston
Nov 12 '05 #2

P: n/a
When I ran the "select * from syscat.routineauth" logged in as
db2admin, I got the following error: (I'm running DB2 v7.2 FP 9; when I
looked at the Views in my DB, I didn't see ROUTINEAUTH )

DBA2191E SQL execution error.

com.ibm.db.DataException: A database manager error occurred. :
[IBM][CLI Driver][DB2/NT] SQL0204N "SYSCAT.ROUTINEAUTH" is an
undefined name. SQLSTATE=42704

Any ideas?

Nov 12 '05 #3

P: n/a

"Andy S." <an******@hotmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...
When I ran the "select * from syscat.routineauth" logged in as
db2admin, I got the following error: (I'm running DB2 v7.2 FP 9; when I
looked at the Views in my DB, I didn't see ROUTINEAUTH )

DBA2191E SQL execution error.

com.ibm.db.DataException: A database manager error occurred. :
[IBM][CLI Driver][DB2/NT] SQL0204N "SYSCAT.ROUTINEAUTH" is an
undefined name. SQLSTATE=42704

Any ideas?

Unless you made a simple typo in the table name in the query you wrote, it
looks like you don't have SYSCAT.ROUTINEAUTH on your system. That seems
rather odd to me unless perhaps SYSCAT.SYSROUTINEAUTH was new with Version 8
of DB2 for Unix/Linux/Windows. Unfortunately, I can't find a Version 7 SQL
Reference at the IBM site so I can't determine if the table is new to
Version 8.

I can think of two options but I'm not sure you're going to like either one:
1. Upgrade to Version 8 so that you have the new version of the catalog.
That seems like a lot of work to do just to check privileges for a stored
procedure but you'll want to upgrade to Version 8 sooner or later; Version 7
went out of service several months back so you've got that incentive to
upgrade.
2. Figure out why SYSCAT.ROUTINEAUTH is not on your system. I can't think of
any reason for that offhand so I don't know what to suggest.

Rhino
Nov 12 '05 #4

P: n/a
Rhino wrote:
"Andy S." <an******@hotmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...
When I ran the "select * from syscat.routineauth" logged in as
db2admin, I got the following error: (I'm running DB2 v7.2 FP 9; when I
looked at the Views in my DB, I didn't see ROUTINEAUTH )

DBA2191E SQL execution error.

com.ibm.db.DataException: A database manager error occurred. :
[IBM][CLI Driver][DB2/NT] SQL0204N "SYSCAT.ROUTINEAUTH" is an
undefined name. SQLSTATE=42704

Any ideas?


Unless you made a simple typo in the table name in the query you wrote, it
looks like you don't have SYSCAT.ROUTINEAUTH on your system. That seems
rather odd to me unless perhaps SYSCAT.SYSROUTINEAUTH was new with Version 8
of DB2 for Unix/Linux/Windows. Unfortunately, I can't find a Version 7 SQL
Reference at the IBM site so I can't determine if the table is new to
Version 8.

I can think of two options but I'm not sure you're going to like either one:
1. Upgrade to Version 8 so that you have the new version of the catalog.
That seems like a lot of work to do just to check privileges for a stored
procedure but you'll want to upgrade to Version 8 sooner or later; Version 7
went out of service several months back so you've got that incentive to
upgrade.
2. Figure out why SYSCAT.ROUTINEAUTH is not on your system. I can't think of
any reason for that offhand so I don't know what to suggest.

Rhino

EXECUTE privilege on functions is new in DB2 V8.1 for LUW. Also prior to
V8 there were to sets of view: SYSCAT.PROCEDURES and SYSCAT.FUNCTIONS.
SYSCAT>ROUTINES is new.
Now, w.r.t privileges on procedures I have a hard time remembering. I
suspect the way to control those was via package authorization only.
Anyway: Look for packages and procedures. "routine" is a V8 beast.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #5

P: n/a
Thanks everyone! I will look into it and post if I have any more
troubles...

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.