Stored Procedures privileges report
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