From: lester@access.digex.net (Lester Knutsen)
Subject: Stored Procedures privileges report
Date: 27 Jan 1994 23:28:57 -0500
X-Informix-List-ID: <list.3417>


Hello, 

This is an ACE report that will print privileges for Stored Procedures.
Stored Procedures are a new feature of Informix release 5.0 Online and SE. 
Therefore, this program requires 5.0 to compile and run.  

This ACE program uses the following tmp tables, tmp_u_114, tmp_u_115 and
tmp_u_116.  There is a bug (Number 4586) in Informix 2.1 that
will delete a permanent table if it has the same name as a temp table in
an ACE program.  This was fixed in 4.0 and since this report requires 5.0
it should not be a problem. (However, please be careful.) 

Hopefully this report will be helpful in determining who has permission
to run what Stored Procedures as there is no info command in sql to
do this.  

Regards - Lester

{
:############################################################################
:
: Module:	@(#)privproc.ace	1.3	Date: 94/01/27
: Objective:	Print Stored Procedures privileges (Informix 5.0 or greater)
: Author: 	Lester B. Knutsen
:		Advanced DataTools Corporation
:		4510 Maxfield Drive, Annandale, VA 22003
:		Tel: 703-256-0267 or Email: lester@access.digex.net
:
:           	Copyright 1994 Advanced DataTools Corporation
:
: Discription:	This is an Informix ace report that will print all/selected 
:	users and their permissions for all/selected stored procedures.
:	The program creates a temp table of all users, owners, grantors 
:	and grantees from sysusers, sysprocedures and sysprocauth.  Then it 
:	creates a temp table of public privileges for each table in the
:	database. Finally, it joins all the users with all the tables to
:	show the permissions. If a user has been granted different privileges
:	by different people, both privileges will print.  
:
:	To compile this program type: saceprep privproc
:
:	The database section of the report points to the stores5
:	database, you may need to change this to get it to compile.
:
:	To run this type: sacego -d database privproc
:
:		where "database" is the name of the database to report on.
:
:############################################################################
}

database stores5 end 

define
	variable last_procid	integer
	variable last_grantor	char(8)
	variable last_procauth	char(1)
	variable p_username	char(20)
	variable p_procname	char(20)
end

input	prompt for p_username
	using "Enter username or press RETURN for ALL users: "
	prompt for p_procname
	using "Enter procedure or press RETURN for ALL tables: "
end
	
output
	left margin 0
	right margin 80
	top margin 3
	bottom margin 3
	page length 66
{	report to "privproc.rpt" }
	report to pipe "more"  
end

{############################################################################}
{ Select all users, table owners and anyone with any procedure permissions }
select 	username 	from sysusers
union
select 	owner 		from sysprocedures
union
select 	grantor 	from sysprocauth
union
select 	grantee 	from sysprocauth
union
select 	owner from 	systables
where	tabid > 99	{ skip the systems tables }
union
select 	grantor 	from systabauth
union
select 	grantee 	from systabauth
into temp tmp_u_114;

{ Select a unique list of users from the above select }
select 	tmp_u_114.username, 
	sysusers.usertype
from 	tmp_u_114, outer sysusers
where 	tmp_u_114.username = sysusers.username
and 	tmp_u_114.username not in ( "informix", " " )
and 	( $p_username = "" or $p_username matches tmp_u_114.username )
into temp tmp_u_115;

{############################################################################}
{ Select public permissions }
select 	sysprocauth.procid,
	sysprocauth.procauth 	pub_procauth,
	sysprocauth.grantor 	pub_grantor,
	sysusers.usertype 	pub_type
from 	sysprocauth, outer sysusers
where 	sysprocauth.grantee = "public"
and 	sysprocauth.grantee = username
into temp tmp_u_116;

{############################################################################}
{ Join the users with all tables in the database, their table }
{ permissions, and public permissions }
select 	tmp_u_115.username,
	tmp_u_115.usertype,
	sysprocedures.procname,
	sysprocedures.owner,
	sysprocedures.procid,
	sysprocedures.mode,
	sysprocauth.grantor,
	sysprocauth.grantee,
	sysprocauth.procauth,
	tmp_u_116.pub_procauth,
	tmp_u_116.pub_grantor,
	tmp_u_116.pub_type
from 	tmp_u_115, sysprocedures, outer sysprocauth, outer tmp_u_116
where 	sysprocedures.procid = sysprocauth.procid
and	tmp_u_115.username = sysprocauth.grantee
and	sysprocedures.procid = tmp_u_116.procid
and 	( $p_procname = "" or $p_procname matches sysprocedures.procname )
order by username, procname, grantor
end

{############################################################################}
format 
page header

print	column 1, "Date: ", today using "MM/DD/YY",
	column 22, "Stored Procedures Privileges by User",
	column 70, "Page:", pageno using "####"
print 	column 1, "----------------------------------------",
		"----------------------------------------"
skip 1 line

{############################################################################}
on last row
need 12 lines
skip 2 line
print 	column 1, "----------------------------------------",
		"----------------------------------------"
print 	column 1, "Procedure Owner - can execute and grant execute to others"
print 	column 1, "Execute - user can execute the procedure"
print 	column 1, "Execute and Grant - can execute and grant execute to others"
print 	column 1, "Public Execute  - procedure has public execute privileges"
print 	column 1, "None - user cannot execute the procedure"
print 	column 1, "----------------------------------------",
		"----------------------------------------"

print 	column 8,
	"Privproc.ace from Advanced DataTools Corporation - 703-256-0267"
print 	column 11,
	"Control your Informix Database security with DB Privileges"
print 	column 1, "----------------------------------------",
		"----------------------------------------"

{############################################################################}
before group of username
need 7 lines
let last_procid = 0
let last_grantor = "        "
let last_procauth = "        " 

print 	column 1, "User: ", username, 
	column 24,"DB Access: "; 
if ( usertype = "C" ) then print "Connect"
else if ( usertype = "R" ) then print "Resource"
else if ( usertype = "D" ) then print "DBA"
else if ( pub_type = "C" ) then print "Connect (Public)"
else if ( pub_type = "R" ) then print "Resource (Public)"
else if ( pub_type = "D" ) then print "DBA (Public)"
else print "None"

skip 1 line

print	column 1,  " Stored Procedure",
	column 24, "Grantor",
	column 38, "Mode",
	column 48, "    Privilege"
print	column 1,  "------------------",
	column 24, "--------",
	column 38, "-----",
	column 48, "------------------"

{############################################################################}
after group of username
print 	column 1, "----------------------------------------",
		"----------------------------------------"
skip 1 line

{############################################################################}
on every row
{ duplicate rows are created by the join with pub for tables where
  more then one grantor has granted public excute privilege. The 
  following check removes duplicated. 
}
if ( 	( last_procid = procid )
	and 	(( last_grantor = grantor and last_procauth = procauth )
		or ( username = owner ))
	) then
	begin
		{ duplicate row - do not do anything } 
		let last_procid = procid
		let last_grantor = grantor
		let last_procauth = procauth
	end
else 
	{########################################################}
begin
	let last_procid = procid
	let last_grantor = grantor
	let last_procauth = procauth

	print 	column 1, procname;
	
	{ There are 4 possible sources of privileges for every row
	1. the user is the procedure owner and has all privileges 
	2. the user has been granted privileges by one or more other users
	3. the user has not been granted privileges, or they have been revoked
		but public has been granted privileges. 
	4. the user has no privileges and public has no privileges
	}
	{########################################################}
	{ Check if the user is the table owner }
	if ( username = owner ) then
	begin
		print	column 24, username;
		if ( mode = "D" ) then print column 38, "DBA";
		else 	print column 38, "Owner";
		print	column 48, "Procedure Owner"
	end
	{########################################################}
	{ Check if the user has privileges ( sysprocauth.procauth is not null )
		and print user prvivileges
	  or
	  public does not have privileges  
		( sysprocauth.procauth is null where grantee = public)
		and print "N"s
	}
	else if ( procauth is not null or pub_procauth is null ) then
	begin
		print	column 24, grantor;
		if ( mode = "D" ) then print column 38, "DBA";
		else 	print column 38, "Owner";
		if ( procauth = "e" )  then 	print column 48,"Execute"
		else if ( procauth[1] = "E" ) then 
			print column 48,"Execute and Grant"
		else 	print column 48, "None"
	end
	else
	{########################################################}
	{ user has no privileges but public does so print them }
	begin
		print	column 24, pub_grantor;
		if ( mode = "D" ) then print column 38, "DBA";
		else 	print column 38, "Owner";
		if ( pub_procauth[1] = "e" )  then print column 48,"Public Execute"
		else if ( pub_procauth[1] = "E" ) then 
			print column 48,"Public Execute and Grant"
		else 	print column 48, "None"
	end
end
end 
{############################################################################}