[Info-ingres] Protect Ingres procedure source

[Paul White]

Trying to create a view to hide the procedures...

create view iiprocedures as select * from [$ingres].iiprocedures
Executing . . .

E_US13FC CREATE VIEW: Illegal tablename 'iiprocedures'. 'ii' is reserved for system catalogs.


I have not found a way to hide proprietary / secret source code in 
stored procedures.  I cannot hide the schema, tables, columns indices 
etc. As soon a user has a connection, for example ODBC, the database 
secrets can be mined.  Here is my use case:

OpenROAD, .Net and Web applications connect to a secure Ingres DB on 
Linux.  Each User / Service has its own operating system account and 
password which allows validation. The DBusers are created without a 
password - but I am thinking of changing this.    For Web and .Net apps, 
the connection DSN is encrypted in the config file.  The service account 
has restricted access to specific user tables in database.   The 
OpenROAD application uses a vnode with global connection definition.  
The first time a user runs the application,  the local vnode definition 
is completed automatically using the client USERNAME.  A unique password 
is generated to match the account in Linux.   Crystal Reports use an 
ODBC connection which relies on a global fixed vnode and a fixed 
reporting user/password.


I think, at this point, I have a number of options for protection:

  * Restrict connection to the Server O/S, firewall, client software
    controls.
  * Restrict Ingres services: iigcc, iigcd to specific ports
  * Restrict connection to the database with authentication / passwords
    at a number of levels: User, role, operating system, network (PAM),
    installation.
  * Once connected, restrict access to specific tables/columns using
    views, grants, procedures protected by user, role password,
    encryption and mask.
  * Restrict access to physical directories which store database files, 
    checkpoints, journals. This is a default for Ingres installations.
  * Restrict user access to application or web portals eg Xenapp.
  * Use a report server to restrict access points from the reporting
    account.
  * Put sensitive / proprietary code into a library routine or call to
    an app server


The gaps:

A data mining tool can connect to ODBC and browse the database schema as 
report user.  Even Excel can retrieve schema information

The application role password is stored in OpenROAD.  I think I can hide 
it in a compiled image and make it difficult (but not impossible) for a 
developer to debug.

Service and reporting passwords are not secret.  I can isolate DEV /QA / 
Production but it doesn't protect the schema.

dbunload and copydb files are in plain text.  Passwords are encrypted 
but not schema / procedural code.

Web and .net config files have a DSN which I believe can be decrypted.


On 1/05/2021 10:31 pm, Roy Hann wrote:

> Paul White wrote:
>
>> I would like to hide or encrypt the source code for a specific database
>> procedure.
> To stop myself fussing with this loose tooth I am going to
> publicly declare defeat so I can move on. I don't see a way to do it.
> Everything I thought of is easily subverted or breaks Ingres or (should)
> invalidate your support agreement.
>
> Roy
> _______________________________________________
> Info-ingres mailing list
> Info-ingres at lists.planetingres.org
> https://lists.planetingres.org/mailman/listinfo/info-ingres
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20210504/36cb73e6/attachment.html>