SYSPROC.SNAPSHOT_APPL_INFO Permissions ?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

PaulR - 22 Nov 2005 15:36 GMT

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

Reply to this Message

Ian - 22 Nov 2005 17:28 GMT

> Hi,
>
[quoted text clipped - 9 lines]
> 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.

Reply to this Message

Pierre Saint-Jacques - 22 Nov 2005 19:01 GMT

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.

Signature

Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515

> Hi,
>
[quoted text clipped - 13 lines]
>
> Paul Reddin

Reply to this Message

UDBDBA - 22 Nov 2005 20:29 GMT

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

Reply to this Message

Ian - 23 Nov 2005 01:01 GMT

> 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.

Reply to this Message

UDBDBA - 23 Nov 2005 01:55 GMT

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

Reply to this Message