Privilege.ace - updated
Hello,
The following is the updated version of privileg.ace, an ace report to
print database, tables and views privileges by user. The updated version
allows you to select users or tables ( or all users and tables by
pressing return at the prompts ) and shows grant option privileges and
reference prvileges.
Its set up for the stores5 database, but since it only needs the
systemtables it will compile with any database, just change the
database section of the ace report. Once its compiled you
can run it with any database by: sacego -d database_name privileg
Where database_name is any database in your path
Regards - Lester
#############################################################################
# Lester Knutsen lester@access.digex.net #
# Advanced DataTools Corporation Voice: 703-256-0267 #
# Control you Informix database security with DB Privileges #
#############################################################################
---------cut here------cut here---------cut here-------cut here -------------
{
:############################################################################
:
: Module: @(#)privileg.ace 1.6 Date: 94/01/22
: 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 in a database and their permissions for all/selected tables.
: The program creates a temp table of all users, owners, grantors
: and grantees from sysusers, systables and systabauth. 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. The permissions for each table are: Select,
: Update, Columns, Insert, Delete, Index, Alter and Reference.
: For each privilege the following is shown:
:
: Y - the user has been granted this privilege
: G - the user has been granted this privilege and can grant it to others
: N - the user has not been granted this privilege
: O - the user ownes the table, has all privileges and can grant 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.
:
: Update Notes: This was updated in January 1994 to include selecting
: users and/or tables to report, showing Grant option privileges
: and printing reference prvileges.
:
:############################################################################
}
database stores5 end
define
variable last_tabid integer
variable last_grantor char(8)
variable last_tabauth char(8)
variable p_username char(20)
variable p_tabname char(20)
variable p_tabauth char(8)
end
input prompt for p_username
using "Enter username or press RETURN for ALL users: "
prompt for p_tabname
using "Enter table 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 "privilege.rpt"
{ report to pipe "more" }
end
{############################################################################}
{ Select all users, table owners and anyone with any table permissions }
select username
from sysusers
union
select owner
from systables
where systables.tabid > 99 { skip systems tables }
union
select grantor
from systabauth
union
select grantee
from systabauth
into temp users1;
{ Select a unique list of users from the above select }
select users1.username,
sysusers.usertype
from users1, outer sysusers
where users1.username = sysusers.username
and users1.username not in ( "informix", " " )
and ( $p_username = "" or $p_username matches users1.username )
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", "dtcolauth", "dtuser" )
and ( $p_tabname = "" or $p_tabname matches systables.tabname )
order by username, tabname, grantor
end
{############################################################################}
format
page header
print column 1, "Date: ", today using "MM/DD/YY",
column 23, "Database Table Privileges by User",
column 70, "Page:", pageno using "####"
print column 1, "----------------------------------------",
"----------------------------------------"
skip 1 line
{############################################################################}
page trailer
skip 1 line
print column 1, "----------------------------------------",
"----------------------------------------"
print
"Y-Privilege G-Privilege w/grant N-No privilege O-Table Owner P-Public"
{############################################################################}
on last row
need 12 lines
skip 2 line
print column 1, "----------------------------------------",
"----------------------------------------"
print column 1, "Y - User has privilege"
print column 1, "G - User has privilege and can grant it to others"
print column 1, "N - User does not have privilege"
print column 1, "O - User is table owner and has all privileges"
print column 1, "P - User has privilege because it is available to public"
print