We have a production database running ABF on unix which is currently
GRANT ALL ON <all> TO PUBLIC

Application Users are locked into the application at logon time and
have no access to isql

The DBA and other developers have isql access

What we want is for the DBA to still have full access
The application users to have full access through the application only
and the other developers to have no access

Normally we would use roles to allow all non-dba users access through
the application
To do this we would build the abf application with -R

but then what about ReportWriter called via CALL SYSTEM
Surely that would fail ?

Another option is to create dummy isql, sql and qbf shells so that non-
dba users get an 'Access Denied' message but this is not 100% secure

What is the normal solution for this problem ?

Colin Hay

Hi Colin,

Assuming you have specific usernames for the developers that you need to
block you can do this via Accessdb or via a GRANT against iidbdb

GRANT NOACCESS ON DATABASE mydb TO username,username

I'm sure you now this Colin, but for wider consumption it is very bad
practice to let Developers anywhere near a production database. If they need
access to production data then this should be done creating a copy of the
data or via a username that has readonly access.

Regards

Peter Gale
Ingres Corp.

2009/2/12 Colin Hay <wobble7 (AT) hotmail (DOT) com>



--
Peter Gale
pgale61 (AT) gmail (DOT) com

Hi Colin,

Peter provided some very good advice as usual. This is really an
extension to what he suggested.

The access restrictions that you mention should really be common
practice, but are often usually only seen in environments where
actually needed due to regulatory concerns. The first question that I
ask is, "How much control of the environment do you want or need?"

Whenever possible, it is best to have completely separate environments
for development and production. That will make it easy to restrict
all access to the production environment. A caveat here is that you
don't want Ingres/Net connections between those two environments. You
will also need some account (DBA or special management account - the
latter being my preference). At my old company we (Peter, myself, and
a handful of other guys) managed a very large 24x7 mission-critical
environment in this manner. We had a rigorous change control process,
and changes made outside of applications were only done by us using
this procedure. It was very easy to control and audit.

If you don't have multiple machines, you can still separate
environments with multiple Ingres installations. It is not as ideal
(since you can still have one environment affect the other from a
performance and capacity perspective). This allows you to provide a
greater level of control by Ingres user accounts. Again, you don't
want Ingres/Net connections between the two environments.

If you have one system and one Ingres installation, then the method
Peter describes is best. I've seen people do things like change
permissions to sql / isql / quel / iquel / tm, but that doesn't stop
you from using copydb unless the grants and account permissions are
properly set. The use of groups is helpful and easier to manage. The
problem that I've seen with Roles is where people embed the role
password in the application. Not very secure. We implemented a cool
solution years ago that used a simple encryption routine to manage
this.

There are many ways to approach this, but it does make sense to really
look at the current configuration and your specific needs. Physical
separation of environments is really the direction that you should be
looking at, whether that is simply with multiple Ingres installations,
or with complete separation by system at a hardware level. From there
you can get as granular as you need using account permissions, roles,
groups, and permits. Once you have defined the requirements, the
strategy and plan fall out of that pretty easily.

Cheers,

Chip

Sorry for the confusion - we do have two seperate machines
When I meant developers - I meant system admin guys

In our OpenROAD apps we use a crypted role password in a .ini file
which is decrypted prior to connecting to the database

I was planning to do something like that for this ABF application,
however I'm not sure that connecting / disconnecting / multiple
sessions is so easy in ABF

I was then told you could compile an ABF application with a role which
would do away with the need for this logic
However surely the role would only apply to the ABF and any subsystems
called from ABF e.g. CALL REPORT, CALL QBF
It would not work if we called reports via shell scripts, C or CALL
SYSTEM commands

So my dilema is

How do I prevent non-captive users from having SELECT access in isql
and yet have ReportWriter work for everyone ?

Colin

Colin Hay wrote:

You are never going to get an elegant and robust solution if the people
who originally set this system up subverted Ingres' ability to
authenticate and restrict specific users, as it seems they did.

You could try putting a SET ROLE statement in the .SETUP section of the
reports. If the Report Writer is executing on the Ingres host and not
the client workstation then the user will never have a chance to
examine the source code. That won't be bullet-proof but it will defeat
the enthusiastic and opportunistic amateur.

If you want bullet-proof, with auditing and compliance that might stand
up in a courtroom, we'll have to have a longer talk.

--
Roy

UK Ingres User Association Conference 2009 will be on Tuesday June 9, 2009
Go to http://www.iua.org.uk/join to get on the mailing list.

Colin,

It seems that there are really two issues.

The first issue is whom to trust. It is extremely difficult to lock-
out a System Administrator that has all privileges and full access.
VMS is actually a lot better than Unix and Linux for doing that.
Ultimately, it comes down to needing to trust someone. The group of
people with that access (and implied trust) should be small. It is
possible to have System Administrators that don't have full "root"
access, and that is good in some environments.

Before you go locking things down, it is important to understand what
you are protecting, what the risks are, and whom you are protecting
things from. It makes it easier to focus on implementing security
measures that accomplish the intended goal while minimizing disruption
to a production environment.

The second issue controlling access to reports and scripts accessed
via "call system" calls since they would not run with the same role
information. My suggestion here would be to use "sudo" (see
http://www.gratisoft.us/sudo/ for more information). This is a
powerful and flexible tool that allows either pass-through
authentication or prompting for passwords, can control what is run and
how it is run (e.g., using the -R flag (something that you could do
with call system as well, but would likely be prompted for a
password)), and could run as whatever user you needed it to do.

This could be implemented via a special restricted access user account
that has additional levels of control and auditing, if desired. Using
sudo provides control, flexibility, and the ability to audit. It is
not difficult to implement, but would require coding changes some or
all of the calls. If you are calling a script it would be easy enough
to incorporate sudo into that script without making other changes, but
direct calls to reports or QBF will need to be changed.

Cheers,

Chip