From Newsgroup: comp.databases.ingres
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@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:
rCo Restrict connection to the Server O/S, firewall, client software controls.
rCo Restrict Ingres services: iigcc, iigcd to specific ports
rCo Restrict connection to the database with authentication / passwords at a number of levels: User, role, operating system, network (PAM), installation.
rCo Once connected, restrict access to specific tables/columns using views, grants, procedures protected by user, role password, encryption and mask.
rCo Restrict access to physical directories which store database files, checkpoints, journals. This is a default for Ingres installations.
rCo Restrict user access to application or web portals eg Xenapp.
rCo Use a report server to restrict access points from the reporting account.
rCo 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@lists.planetingres.org
https://lists.planetingres.org/mailman/listinfo/info-ingres
_______________________________________________
Info-ingres mailing list
Info-ingres@lists.planetingres.org https://lists.planetingres.org/mailman/listinfo/info-ingres
--- Synchronet 3.21b-Linux NewsLink 1.2