[Info-ingres] Protect Ingres procedure source

[Paul White]

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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20210501/97024a71/attachment.html>