[Info-ingres] Protect Ingres procedure source

[Paul White]

It was worth a try

sql xpw +U -u'$ingres' -s
set autocommit on;
revoke all on iiprocedure from public restrict;
E_US138E Table is in use. End other transactions using the table and try again.


On 4/05/2021 9:05 am, Karl Schendel wrote:
> I'm going to regret even suggesting this, but hey, whatever...
>
> If all the sessions that actually need to execute the DB procedures connect with some
> role identifier, I suspect you could manually revoke/drop the grant select to public on
> iiprocedure using a $ingres +U session, and manually grant select to the relevant role.
> This is off the top of my head and I don't know what other fallout it might cause.
>
> I think this might be the first time I've come across a valid use case for hiding
> DB procedure definitions, and it's worth some thought.
>
> Karl
>
>
>> On May 3, 2021, at 6:56 PM, Paul White <paul.white at shift7solutions.com.au> wrote:
>>
>> 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
>> _______________________________________________
>> Info-ingres mailing list
>> Info-ingres at lists.planetingres.org
>> https://lists.planetingres.org/mailman/listinfo/info-ingres
> _______________________________________________
> 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/53ec73b8/attachment-0001.html>