I would like to hide or encrypt the source code for a specific database procedure.
On May 3, 2021, at 6:56 PM, Paul White <paul.white@shift7solutions.com.au> wrote: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
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.
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@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
Paul White wrote:
I would like to hide or encrypt the source code for a specific databaseTo stop myself fussing with this loose tooth I am going to
procedure.
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
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
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 aOn 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.
_______________________________________________
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@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
Info-ingres mailing list
Info-ingres@lists.planetingres.org https://lists.planetingres.org/mailman/listinfo/info-ingres
Sysop: | Keyop |
---|---|
Location: | Huddersfield, West Yorkshire, UK |
Users: | 418 |
Nodes: | 16 (2 / 14) |
Uptime: | 21:23:40 |
Calls: | 8,764 |
Calls today: | 7 |
Files: | 13,287 |
D/L today: |
3 files (1,633K bytes) |
Messages: | 5,964,647 |