SEO

Hi-

Is there any way of restricting access to a database by application &
account? For example, I only want the application APP1 to access the
database using the USER1 account. I've tried to use the DB2 Governor to
do this, but it seems it always allows the the first query to be
processed before forcing the connection. Here is my db2gov config
file:

--- start config
interval 1; dbname sample; account 30;

desc "Force any USER1 account access"
authid USER1
setlimit rowsread 1 idle 1 cpu 1 rowssel 1 uowtime 1
action force;

desc "Allow USER1 user from APP1"
authid USER1
applname APP1
setlimit rowsread -1 idle -1 cpu -1 rowssel -1 uowtime -1;
--- end config

Is there any other way of accomplishing this?

Thanks,
Todd

I think your second rule negates the first.
Values of -1 means ignore whatever rules for limits specified before. In
your case, rowsread, idle, cpu, rowssel and uowtime previously set limits
are ignored because of the last rule.

The way you have set the rule (1) it forces the USER1 if it is logged on.
The way rule (2) is set, it negates rule (1) and then forces only if USER1
uses APP1 but not USER1 if it uses another appl.

If you only want USER1 and APP1 to access, then remove authid USER1 line
from rule (1). This will force any other id using any other appl.
Then rule (2) will allow USER1 using only APP1

Add a rule with the same restriction as your rule (2) but remove the
applname limit and change the authid to specify the DBa's and SYSADMIN's id.
This way, everybody gets forced off except USER1 and the DBA's and/or
SYSADMIN. It should lok like this:
--- start config
interval 1; dbname sample; account 30;

desc "Force any BODY off account access"
setlimit rowsread 1 idle 1 cpu 1 rowssel 1 uowtime 1
action force;

desc "Allow USER1 user from APP1"
authid USER1
applname APP1
setlimit rowsread -1 idle -1 cpu -1 rowssel -1 uowtime -1;

desc "Let DBA's and SYSADMIN on"
authid <dbas>, <sysadmins>
setlimit rowsread -1 idle -1 cpu -1 rowssel -1 uowtime -1;
> --- end config


Also note that the connection will most of the time be honored and some work
may start as the governor may not be "awake" as the connection starts.

HTH, Pierre.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
<toddles666@yahoo.com> a écrit dans le message de news:
1135370404.483624.145460@g44g2000cwa.googlegroups. com...
> Hi-
>
> Is there any way of restricting access to a database by application &
> account? For example, I only want the application APP1 to access the
> database using the USER1 account. I've tried to use the DB2 Governor to
> do this, but it seems it always allows the the first query to be
> processed before forcing the connection. Here is my db2gov config
> file:
>
> --- start config
> interval 1; dbname sample; account 30;
>
> desc "Force any USER1 account access"
> authid USER1
> setlimit rowsread 1 idle 1 cpu 1 rowssel 1 uowtime 1
> action force;
>
> desc "Allow USER1 user from APP1"
> authid USER1
> applname APP1
> setlimit rowsread -1 idle -1 cpu -1 rowssel -1 uowtime -1;
> --- end config
>
> Is there any other way of accomplishing this?
>
> Thanks,
> Todd
>

Pierre-

Thanks for the reply. I apologize for not make myself clear. I didn't
want to restrict access to the database only to that userid and
application, I wanted to restrict access in such a way that only USER1
would be able to access with that specific APP1 application, any other
application that attempted to access with the USER1 account would be
immediately forced or otherwise denied access. Other accounts would
have free access using any application.

Your last comment indicates that the governor won't work, and my tests
show the same thing: a small portion of work will be done before the
governor forces the application, even including returning a full result
set if the query is < 1 second. I'd like to completely deny access to
USER1 if they attempt to connect with any other application other than
APP1. Is there any way to do this?

Thanks,
Todd


Pierre Saint-Jacques wrote:
> I think your second rule negates the first.
> Values of -1 means ignore whatever rules for limits specified before. In
> your case, rowsread, idle, cpu, rowssel and uowtime previously set limits
> are ignored because of the last rule.
>
> The way you have set the rule (1) it forces the USER1 if it is logged on.
> The way rule (2) is set, it negates rule (1) and then forces only if USER1
> uses APP1 but not USER1 if it uses another appl.
>
> If you only want USER1 and APP1 to access, then remove authid USER1 line
> from rule (1). This will force any other id using any other appl.
> Then rule (2) will allow USER1 using only APP1
>
> Add a rule with the same restriction as your rule (2) but remove the
> applname limit and change the authid to specify the DBa's and SYSADMIN's id.
> This way, everybody gets forced off except USER1 and the DBA's and/or
> SYSADMIN. It should lok like this:
> --- start config
> interval 1; dbname sample; account 30;
>
> desc "Force any BODY off account access"
> setlimit rowsread 1 idle 1 cpu 1 rowssel 1 uowtime 1
> action force;
>
> desc "Allow USER1 user from APP1"
> authid USER1
> applname APP1
> setlimit rowsread -1 idle -1 cpu -1 rowssel -1 uowtime -1;
>
> desc "Let DBA's and SYSADMIN on"
> authid <dbas>, <sysadmins>
> setlimit rowsread -1 idle -1 cpu -1 rowssel -1 uowtime -1;
> > --- end config
>
>
> Also note that the connection will most of the time be honored and some work
> may start as the governor may not be "awake" as the connection starts.
>
> HTH, Pierre.
>
> --
> Pierre Saint-Jacques
> SES Consultants Inc.
> 514-737-4515
> <toddles666@yahoo.com> a écrit dans le message de news:
> 1135370404.483624.145460@g44g2000cwa.googlegroups. com...
> > Hi-
> >
> > Is there any way of restricting access to a database by application &
> > account? For example, I only want the application APP1 to access the
> > database using the USER1 account. I've tried to use the DB2 Governor to
> > do this, but it seems it always allows the the first query to be
> > processed before forcing the connection. Here is my db2gov config
> > file:
> >
> > --- start config
> > interval 1; dbname sample; account 30;
> >
> > desc "Force any USER1 account access"
> > authid USER1
> > setlimit rowsread 1 idle 1 cpu 1 rowssel 1 uowtime 1
> > action force;
> >
> > desc "Allow USER1 user from APP1"
> > authid USER1
> > applname APP1
> > setlimit rowsread -1 idle -1 cpu -1 rowssel -1 uowtime -1;
> > --- end config
> >
> > Is there any other way of accomplishing this?
> >
> > Thanks,
> > Todd
> >

Not that I can think of for the moment but let me ponder.
Regards, Pierre.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
<toddles666@yahoo.com> a écrit dans le message de news:
1135408809.020130.197380@g47g2000cwa.googlegroups. com...
Pierre-

Thanks for the reply. I apologize for not make myself clear. I didn't
want to restrict access to the database only to that userid and
application, I wanted to restrict access in such a way that only USER1
would be able to access with that specific APP1 application, any other
application that attempted to access with the USER1 account would be
immediately forced or otherwise denied access. Other accounts would
have free access using any application.

Your last comment indicates that the governor won't work, and my tests
show the same thing: a small portion of work will be done before the
governor forces the application, even including returning a full result
set if the query is < 1 second. I'd like to completely deny access to
USER1 if they attempt to connect with any other application other than
APP1. Is there any way to do this?

Thanks,
Todd


Pierre Saint-Jacques wrote:
> I think your second rule negates the first.
> Values of -1 means ignore whatever rules for limits specified before. In
> your case, rowsread, idle, cpu, rowssel and uowtime previously set limits
> are ignored because of the last rule.
>
> The way you have set the rule (1) it forces the USER1 if it is logged on.
> The way rule (2) is set, it negates rule (1) and then forces only if USER1
> uses APP1 but not USER1 if it uses another appl.
>
> If you only want USER1 and APP1 to access, then remove authid USER1 line
> from rule (1). This will force any other id using any other appl.
> Then rule (2) will allow USER1 using only APP1
>
> Add a rule with the same restriction as your rule (2) but remove the
> applname limit and change the authid to specify the DBa's and SYSADMIN's
> id.
> This way, everybody gets forced off except USER1 and the DBA's and/or
> SYSADMIN. It should lok like this:
> --- start config
> interval 1; dbname sample; account 30;
>
> desc "Force any BODY off account access"
> setlimit rowsread 1 idle 1 cpu 1 rowssel 1 uowtime 1
> action force;
>
> desc "Allow USER1 user from APP1"
> authid USER1
> applname APP1
> setlimit rowsread -1 idle -1 cpu -1 rowssel -1 uowtime -1;
>
> desc "Let DBA's and SYSADMIN on"
> authid <dbas>, <sysadmins>
> setlimit rowsread -1 idle -1 cpu -1 rowssel -1 uowtime -1;
> > --- end config
>
>
> Also note that the connection will most of the time be honored and some
> work
> may start as the governor may not be "awake" as the connection starts.
>
> HTH, Pierre.
>
> --
> Pierre Saint-Jacques
> SES Consultants Inc.
> 514-737-4515
> <toddles666@yahoo.com> a écrit dans le message de news:
> 1135370404.483624.145460@g44g2000cwa.googlegroups. com...
> > Hi-
> >
> > Is there any way of restricting access to a database by application &
> > account? For example, I only want the application APP1 to access the
> > database using the USER1 account. I've tried to use the DB2 Governor to
> > do this, but it seems it always allows the the first query to be
> > processed before forcing the connection. Here is my db2gov config
> > file:
> >
> > --- start config
> > interval 1; dbname sample; account 30;
> >
> > desc "Force any USER1 account access"
> > authid USER1
> > setlimit rowsread 1 idle 1 cpu 1 rowssel 1 uowtime 1
> > action force;
> >
> > desc "Allow USER1 user from APP1"
> > authid USER1
> > applname APP1
> > setlimit rowsread -1 idle -1 cpu -1 rowssel -1 uowtime -1;
> > --- end config
> >
> > Is there any other way of accomplishing this?
> >
> > Thanks,
> > Todd
> >

You haven't stated how APP1 accesses the database.

USER1 can access the database using one of two access authorizations.
1. USER1 was granted access to the tables.
2. USER1 is granted USE of a bound package that has authority to access
the database. This technique is usually used with static SQL. The
package binder has the authorities to the underlying tables which the
user has no direct access to. Access to the package can be limited to
USER1 so others can't do what that user is authorized to do. USER1 is
not given any access to other packages accessing the database, or the
underlying tables.

The second case does not restrict access from the package binder. This
would normally be a dba who would have access to the tables anyway.

Phil Sherman


toddles666@yahoo.com wrote:
> Pierre-
>
> Thanks for the reply. I apologize for not make myself clear. I didn't
> want to restrict access to the database only to that userid and
> application, I wanted to restrict access in such a way that only USER1
> would be able to access with that specific APP1 application, any other
> application that attempted to access with the USER1 account would be
> immediately forced or otherwise denied access. Other accounts would
> have free access using any application.
>
> Your last comment indicates that the governor won't work, and my tests
> show the same thing: a small portion of work will be done before the
> governor forces the application, even including returning a full result
> set if the query is < 1 second. I'd like to completely deny access to
> USER1 if they attempt to connect with any other application other than
> APP1. Is there any way to do this?
>
> Thanks,
> Todd
>
>
> Pierre Saint-Jacques wrote:
>
>>I think your second rule negates the first.
>>Values of -1 means ignore whatever rules for limits specified before. In
>>your case, rowsread, idle, cpu, rowssel and uowtime previously set limits
>>are ignored because of the last rule.
>>
>>The way you have set the rule (1) it forces the USER1 if it is logged on.
>>The way rule (2) is set, it negates rule (1) and then forces only if USER1
>>uses APP1 but not USER1 if it uses another appl.
>>
>>If you only want USER1 and APP1 to access, then remove authid USER1 line
>>from rule (1). This will force any other id using any other appl.
>>Then rule (2) will allow USER1 using only APP1
>>
>>Add a rule with the same restriction as your rule (2) but remove the
>>applname limit and change the authid to specify the DBa's and SYSADMIN's id.
>>This way, everybody gets forced off except USER1 and the DBA's and/or
>>SYSADMIN. It should lok like this:
>>--- start config
>>interval 1; dbname sample; account 30;
>>
>> desc "Force any BODY off account access"
>>setlimit rowsread 1 idle 1 cpu 1 rowssel 1 uowtime 1
>>action force;
>>
>> desc "Allow USER1 user from APP1"
>>authid USER1
>>applname APP1
>>setlimit rowsread -1 idle -1 cpu -1 rowssel -1 uowtime -1;
>>
>>desc "Let DBA's and SYSADMIN on"
>>authid <dbas>, <sysadmins>
>>setlimit rowsread -1 idle -1 cpu -1 rowssel -1 uowtime -1;
>>
>>>--- end config
>>
>>
>>Also note that the connection will most of the time be honored and some work
>>may start as the governor may not be "awake" as the connection starts.
>>
>>HTH, Pierre.
>>
>>--
>>Pierre Saint-Jacques
>>SES Consultants Inc.
>>514-737-4515
>><toddles666@yahoo.com> a écrit dans le message de news:
>>1135370404.483624.145460@g44g2000cwa.googlegroup s.com...
>>
>>>Hi-
>>>
>>>Is there any way of restricting access to a database by application &
>>>account? For example, I only want the application APP1 to access the
>>>database using the USER1 account. I've tried to use the DB2 Governor to
>>>do this, but it seems it always allows the the first query to be
>>>processed before forcing the connection. Here is my db2gov config
>>>file:
>>>
>>>--- start config
>>>interval 1; dbname sample; account 30;
>>>
>>>desc "Force any USER1 account access"
>>>authid USER1
>>>setlimit rowsread 1 idle 1 cpu 1 rowssel 1 uowtime 1
>>>action force;
>>>
>>>desc "Allow USER1 user from APP1"
>>>authid USER1
>>>applname APP1
>>>setlimit rowsread -1 idle -1 cpu -1 rowssel -1 uowtime -1;
>>>--- end config
>>>
>>>Is there any other way of accomplishing this?
>>>
>>>Thanks,
>>>Todd
>>
>>>
>
>
>

For LUW, it's GRANT EXECUTE, not USE on package..

-Eugene