Restrict Access by Application

[toddles666]

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 Saint-Jacques]

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
<to********@yahoo.com> a écrit dans le message de news:
11**********************@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

[toddles666]

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
<to********@yahoo.com> a écrit dans le message de news:
11**********************@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 Saint-Jacques]

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

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
<to********@yahoo.com> a écrit dans le message de news:
11**********************@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
<to********@yahoo.com> a écrit dans le message de news:
11**********************@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

[Phil Sherman]

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
to********@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
<to********@yahoo.com> a écrit dans le message de news:
11**********************@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

[Eugene F]

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

-Eugene