Database privileges
I am testing a security product that works with Informix database
privileges. In testing, I developed the following ace report and thought it
might be useful to others. I also would like to make sure I have covered
all possible situtions. (Currently I am not reporting on column level
privileges or reference privileges)
I need to know all the users that can access a database and what their
privileges (or lack of) will be for each table. The "info privileges for
tablename" does not show who cannot access a table or who can access it because
of public privileges or table ownership. I have created the following
ace report to show all users and their privileges ( or lack of) for
all tables. It can be a long report if you have a lot of tables or users.
I am using Informix 4.0 and 5.0, SE and Online.
Please let me know if you see anything I may have left out or have any
suggestions. Thanks
#############################################################################
# Lester Knutsen lester@access.digex.net #
# Advanced DataTools Corporation Voice: 703-256-0267 #
#############################################################################
---------Cut Here-Begin------------------------------------------------------
{
:############################################################################
:
: Module: @(#)privileg.ace 1.4 Date: 93/06/05
: Author: Lester B. Knutsen
:
: Discription: This is an Informix ace report that will print all known
: users in a database and their permissions for each table.
: The program first creates a temp table of all users,
: owners, grantors and grantees from sysusers,systables and
: systabauth. Then the report 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.
: The permissions for each table are: Select, Update,
: Columns, Insert, Delete, Index, and Alter. For each
: privilege the following is shown:
:
: Y - the user has been granted this privilege
: N - the user has not been granted this privilege
: O - the user is the table owner and has this privilege
: P - the user has this privilege because public has this
: privilege
:
: If a user has been granted different privileges by different
: people, both privileges will print.
:
: To compile this program type:
: saceprep privilege
:
: The database section of the report points to the stores
: database, you may need to change this to get it to compile.
:
: To run this type:
: sacego -d database privilege
:
: where "database" is the name of the database to
: report on.
:
: Please leave me a message or give me a call if you
: have any questions.
:
: Lester Knutsen
: Advanced DataTools Corporation
: 4510 Maxfield Drive
: Annandale, VA 22003
: 703-256-0267
: lester@access.digex.net
:############################################################################
:let copyright="Copyright 1993 Advanced DataTools Corporation"
:let version = "@(#)privileg.ace 1.4"
}
database stores end
define
variable last_tabid integer
variable last_grantor char(8)
variable last_tabauth char(8)
end
output
left margin 0
right margin 80
top margin 3
bottom margin 3
page length 66
report to "privilege.rpt"
{ report to pipe "more"}
end
{############################################################################}
{ Select all users, table owners and anyone with any table permissions }
select unique username
from sysusers
union
select unique owner
from systables
where systables.tabid > 99 { skip systems tables }
union
select unique grantor
from systabauth
union
select unique grantee
from systabauth
into temp users1;
{ Select a unique list of users from the above select }
select unique users1.username,
sysusers.usertype
from users1, outer sysusers
where users1.username = sysusers.username
and users1.username != "informix"
into temp users;
{############################################################################}
{ Select public permissions }
select systabauth.tabid,
systabauth.tabauth pub_tabauth,
systabauth.grantor pub_grantor,
sysusers.usertype pub_type
from systabauth, outer sysusers
where systabauth.grantee = "public"
and systabauth.grantee = username
into temp pub;
{############################################################################}
{ Join the users with all tables in the database, their table }
{ permissions, and public permissions }
select users.username,
users.usertype,
systables.tabname,
systables.owner,
systables.tabid,
systabauth.grantor,
systabauth.grantee,
systabauth.tabauth,
pub.pub_tabauth,
pub.pub_grantor,
pub.pub_type
from users, systables, outer systabauth, outer pub
where systables.tabid = systabauth.tabid
and users.username = systabauth.grantee
and systables.tabid = pub.tabid
and systables.tabid > 99 { Skip system tables }
and tabname not in
("sysmenus","sysmenuitems","syscolatt","syscolval",
"dtgroup", "dttabauth", "dtuser" )
order by username, tabname, grantor
end
{############################################################################}
format
page header
print column 1, "Date: ", today using "MM/DD/YY",
column 26, "DataTools - User Privileges",
column 70, "Page:", pageno using "####"
print column 1, "----------------------------------------",
"----------------------------------------"
skip 1 line
{############################################################################}
page trailer
skip 1 line
print column 1, "----------------------------------------",
"----------------------------------------"
print
"Y-Privilege granted N-No privilege O-Table Owner P-Public privilege"
{############################################################################}
on last row
need 10 lines
skip 5 line
print column 1, "----------------------------------------",
"----------------------------------------"
print
"Privilege.ace by Lester Knutsen, Advanced DataTools Corporation 703-256-0267"
print column 1, "----------------------------------------",
"----------------------------------------"
{############################################################################}
before group of username
need 7 lines
let last_tabid = 0
let last_grantor = " "
let last_tabauth = " "
print column 1, "User login: ", username,
column 22,"DB Access : ";
if ( usertype = "C" ) then print "Connect"
else if ( usertype = "R" ) then print "Resource"
el