<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Trying to create a view to hide the procedures...<br>
</p>
<pre>create view iiprocedures as select * from [$ingres].iiprocedures
Executing . . .</pre>
<pre>E_US13FC CREATE VIEW: Illegal tablename 'iiprocedures'. 'ii' is reserved for system catalogs.</pre>
<br>
<p>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:</p>
<p>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.</p>
<p><br>
</p>
<p>I think, at this point, I have a number of options for
protection:</p>
<ul>
<li>Restrict connection to the Server O/S, firewall, client
software controls.</li>
<li>Restrict Ingres services: iigcc, iigcd to specific ports<br>
</li>
<li>Restrict connection to the database with authentication /
passwords at a number of levels: User, role, operating system,
network (PAM), installation.<br>
</li>
<li>Once connected, restrict access to specific tables/columns
using views, grants, procedures protected by user, role
password, encryption and mask.</li>
<li>Restrict access to physical directories which store database
files, checkpoints, journals. This is a default for Ingres
installations.</li>
<li>Restrict user access to application or web portals eg Xenapp.</li>
<li>Use a report server to restrict access points from the
reporting account.</li>
<li>Put sensitive / proprietary code into a library routine or
call to an app server<br>
</li>
</ul>
<p><br>
</p>
<p>The gaps:</p>
<p>A data mining tool can connect to ODBC and browse the database
schema as report user. Even Excel can retrieve schema information</p>
<p>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.<br>
</p>
<p>Service and reporting passwords are not secret. I can isolate
DEV /QA / Production but it doesn't protect the schema.</p>
<p>dbunload and copydb files are in plain text. Passwords are
encrypted but not schema / procedural code.</p>
<p>Web and .net config files have a DSN which I believe can be
decrypted. <br>
</p>
<br>
<p>On 1/05/2021 10:31 pm, Roy Hann wrote:<br>
</p>
<blockquote type="cite" cite="mid:s6jhmq$v10$1@gioia.aioe.org">
<pre class="moz-quote-pre" wrap="">Paul White wrote:
</pre>
<blockquote type="cite">
<pre class="moz-quote-pre" wrap="">I would like to hide or encrypt the source code for a specific database
procedure.
</pre>
</blockquote>
<pre class="moz-quote-pre" wrap="">
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
<a class="moz-txt-link-abbreviated" href="mailto:Info-ingres@lists.planetingres.org">Info-ingres@lists.planetingres.org</a>
<a class="moz-txt-link-freetext" href="https://lists.planetingres.org/mailman/listinfo/info-ingres">https://lists.planetingres.org/mailman/listinfo/info-ingres</a>
</pre>
</blockquote>
</body>
</html>