<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Hi Ingres gurus.</p>
I would like to hide or encrypt the source code for a specific
database procedure.<br>
I think it would involve disabling HELP PROCEDURE and restricting
access to the iiprocedures system catalog.<br>
I would like to hide the rest of the schema as well. <br>
I think there is some capacity to block more system catalog tables
as indicated by copydb behaviour (see below)<br>
<br>
I can restrict connection with a role and password, but once
connected, there are several methods to extract schema and source
from the database.<br>
Documentation on Encryption in the database indicates the feature is
for data stored in user tables.<br>
<br>
<p>example I have a row producing procedure:</p>
<pre>
sql iidbdb
* grant noselect_syscat on database testdb to pwhite\g\q
sql testdb
drop procedure if exists p_hidden
\g
create procedure p_hidden(v varchar(16))
result row game(d ingresdate, msg varchar(50) not null with default)
as
declare
d ingresdate;
msg varchar(50);
begin
if v = 'apple' then
msg = 'fruit'
elseif v = 'carrot' then
msg = 'vegetable'
elseif v = 'cow' then
msg = 'animal'
elseif v = 'truck' then
msg = 'mineral'
else
msg = 'dunno'
endif;
select :d = date('now');
RETURN ROW(:d, :msg);
end;
\g
grant execute on procedure p_hidden to public
\g
\q
sql testdb -upwhite
* select * from p_hidden(v = 'apple'); \g
+-------------------------+--------------------------------------------------+
|d |msg |
+-------------------------+--------------------------------------------------+
|01/05/21 16:37:15 |fruit |
+-------------------------+--------------------------------------------------+
* select * from p_hidden(v = 'carrot'); \g
+-------------------------+--------------------------------------------------+
|d |msg |
+-------------------------+--------------------------------------------------+
|01/05/21 16:37:23 |vegetable |
+-------------------------+--------------------------------------------------+
* help procedure p_hidden\g
Procedure: p_hidden
Owner: ingres
Procedure Type: native
Object type: user object
Created: 01/05/21 16:37:08
Procedure Definition:
/* 1 */ create procedure p_hidden(v varchar(16)) result row game(d ingresdate, msg varchar(50) not null with default) as declare d ingresdate;
/* 2 */ msg varchar(50);
/* 3 */ begin if v = 'apple' then msg = 'fruit' elseif v = 'carrot' then msg = 'vegetable' elseif v = 'cow' then msg = 'animal' elseif v = 'truck' then msg = 'mineral' else msg = 'dunno' endif;
/* 4 */ select :d = date('now');
/* 5 */ RETURN ROW(:d, :msg);
/* 6 */ end
Permissions on procedure p_hidden are:
Permission 2:
grant execute on procedure "ingres".p_hidden to public
* select * from iiprocedures where procedure_name = 'p_hidden'\g
|procedure_name|procedure_owner |create_date |proc_s|text_sequence |text_segment
|p_hidden |ingres |2021_05_01 06:37:08 GMT |N | 1|create procedure p_hidden(v varchar(16)) result row game(d ingresdate, msg varchar(50) not null with default) as declare d ingresdat
e; msg varchar(50); begin if v = 'apple' then msg = 'fruit' elseif v = 'carrot' then msg = 'vegetable' else|U |Y
|p_hidden |ingres |2021_05_01 06:37:08 GMT |N | 2|if v = 'cow' then msg = 'animal' elseif v = 'truck' then msg = 'mineral' else msg = 'dunno' endif; select :d = date('now'); RETURN RO
W(:d, :msg); end
(2 rows)
* select top 1 * from iirelation\g
Executing . . .
E_PS035A iirelation is a system catalog.
You do not have appropriate privileges to directly query system catalogs
(Sat May 1 17:07:35 2021)
* select top 1 * from iitables\g
|table_name |table_owner |create_date |alter_date |table_|table_|tab
|ii_dbd_identifiers |$ingres |2019_07_01 01:23:04 GMT |2020_02_04 20:14:17 GMT |T |N |II1
(1 row)
</pre>
<pre>
Via Operating system user pwhite</pre>
<pre>$ copydb testreet -with_proc
INGRES COPYDB Copyright 2019 Actian Corporation
Unload directory is '/home/pwhite'.
Reload directory is '/home/pwhite'.
E_PS035A iirelation is a system catalog.
You do not have appropriate privileges to directly query system catalogs
(Sat May 1 17:45:23 2021)
There are 9 tables owned by user 'pwhite'.
E_PS035A iiprocedure is a system catalog.
You do not have appropriate privileges to directly query system catalogs
(Sat May 1 17:45:23 2021)
There are 0 procedures owned by user 'pwhite'.
</pre>
<p><br>
</p>
<p>Paul<br>
</p>
</body>
</html>