SYSPROC.SNAPSHOT_APPL_INFO Permissions ?

PaulR

Hi,

I have used the Table Function SYSPROC.SNAPSHOT_APPL_INFO in a Trigger
body, and am having problems granting suitable permissions to allow it
to run for mere mortal users.

I have tried granting EXECUTE permissions to individual Users,Groups
and DBADM authority to users etc. - all fail with permission failure
SQL1092.

The only permission which allows it to work, is putting a user in the
Instance Owner group i.e implicitly giving Instance Owner authority -
which is clearly undesirable,

Anybody have any ideas?

Paul Reddin

Nov 23 '05 #1

Ian

PaulR wrote:

Hi,

I have used the Table Function SYSPROC.SNAPSHOT_APPL_INFO in a Trigger
body, and am having problems granting suitable permissions to allow it
to run for mere mortal users.

I have tried granting EXECUTE permissions to individual Users,Groups
and DBADM authority to users etc. - all fail with permission failure
SQL1092.

The only permission which allows it to work, is putting a user in the
Instance Owner group i.e implicitly giving Instance Owner authority -
which is clearly undesirable,

I'd say giving DBADM is equally inadvisable :-)

The solution is using the SYSMON_GROUP database manager config parameter
and adding your users to this group.

Nov 23 '05 #2

Pierre Saint-Jacques

Find the corresponding package to the proc and rebind it with DYNAMIC RULES
BIND options. I believe you may be encountering problems with dynamic SQL
attempting to be executed by user ids who don't have the privilege.
I don't have my system up so can't point you to the proper pkg. and option.
HTH, Pierre.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"PaulR" <pa**@abacus.co.uk> a écrit dans le message de news:
11*********************@g14g2000cwa.googlegroups.c om...

Hi,

I have used the Table Function SYSPROC.SNAPSHOT_APPL_INFO in a Trigger
body, and am having problems granting suitable permissions to allow it
to run for mere mortal users.

I have tried granting EXECUTE permissions to individual Users,Groups
and DBADM authority to users etc. - all fail with permission failure
SQL1092.

The only permission which allows it to work, is putting a user in the
Instance Owner group i.e implicitly giving Instance Owner authority -
which is clearly undesirable,

Anybody have any ideas?

Paul Reddin

Nov 23 '05 #3

UDBDBA

Option 1 - SYSMON_GROUP given the user to RESET MONITOR and I doubt if
you want regular users to be running RESET MONITOR command.

Option 2 - The registry variable DB2_SNAPSHOT_NOAUTH is replaced by
SYSMON_GROUP. But you can still try :)) setting the
DB2_SNAPSHOT_NOAUTH=YES and see if it works. I don't think this
registry variable allows to RESET MONITOR.

Option 3 - Alternate way is to create a view like
sysproc.snapshot_appl_info and give select on the views to users/group
you wish.

HTH

Vijay

Nov 23 '05 #4

Ian

UDBDBA wrote:

Option 1 - SYSMON_GROUP given the user to RESET MONITOR and I doubt if
you want regular users to be running RESET MONITOR command.

Just a note, RESET MONITOR is local to an individual attachment, it does
not affect any other attachment's monitoring data.

Nov 23 '05 #5

UDBDBA

That's correct Ian, i forgot the attachment of instance.
-Vijay

Nov 23 '05 #6