This is a multi-part message in MIME format.
Hi Ingres gurus.
I would like to hide or encrypt the source code for a specific database procedure.
I think it would involve disabling HELP PROCEDURE and restricting access
to the iiprocedures system catalog.
I would like to hide the rest of the schema as well.
I think there is some capacity to block more system catalog tables as
indicated by copydb behaviour (see below)
I can restrict connection with a role and password, but once connected,
there are several methods to extract schema and source from the database. Documentation on Encryption in the database indicates the feature is for
data stored in user tables.
example I have a row producing procedure:
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)
Via Operating system user pwhite
$ 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'.
Paul
<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>
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)