RE: table level privilege
Hack this script as required.
This script has already been hacked by me but the original came from
www.wfsoftware.com/informix
#!/bin/ksh
##################################################################
#
# Filename: grantperms
# Last changed: 5/13/98 14:49:08
#
##########################################################################
function usage
{
echo " Usage: grantperms [-r] <dbname> <usrname>"
echo " -r resource"
exit 1
}
RESOURCE=${FALSE}
while [[ `echo $1 | cut -c1` = "-" ]]
do
case `echo $1 | cut -c2` in
r)
RESOURCE=${TRUE}
;;
*)
# If unknown flag then report usage
usage
;;
esac
shift
done
if [ ${#*} != 2 ]
then
usage
fi
DBNAME=$1
USERNAME=$2
# Revoke old permissions
echo " Revoking all previous permissions"
dbaccess ${DBNAME} - <<EOF >/dev/null 2>&1
revoke connect from ${USERNAME};
revoke resource from ${USERNAME};
revoke dba from ${USERNAME};EOF
# Grant database level permissions
echo " Granting connect to database"
dbaccess ${DBNAME} - <<EOF >/dev/null 2>&1
grant connect to ${USERNAME};EOF
if [ $? != 0 ]
then
echo "Unable to grant connect permission -- exiting"
exit 1
fi
if [ ${RESOURCE} -eq ${TRUE} ]
then
# Grant database level permissions
echo " Granting resource to database"
dbaccess ${DBNAME} - <<-EOF >/dev/null 2>&1
grant resource to ${USERNAME}; EOF
if [ $? != 0 ]
then
echo "Unable to grant resource permission -- exiting"
exit 1
fi
fi
# Grant table level permissions
echo " Granting table level access"
dbaccess ${DBNAME} - <<EOF 2>/dev/null | awk '/ /{
if ($1 != "tabname")
{
print "grant select on "$1" to public;"
print "grant delete on "$1" to public;"
print "grant update on "$1" to public;"
print "grant insert on "$1" to public;"
}
}' | dbaccess ${DBNAME} >/dev/null 2>&1
select tabname
from systables
where tabid > 99
and tabtype = "T";EOF
if [ $? != 0 ]
then
echo "Unable to grant table permissions -- exiting"
exit 1
fi
# Grant view level permissions
echo " Granting view level access"
dbaccess ${DBNAME} - <<EOF 2>/dev/null | awk '/ /{
if ($1 != "tabname")
{
print "grant select on "$1" to public;"
}
}' | dbaccess ${DBNAME} >/dev/null 2>&1
select tabname
from systables
where tabid > 99
and tabtype = "V";EOF
if [ $? != 0 ]
then
echo "Unable to grant view permissions -- exiting"
exit 1
fi
# Grant procedure level permissions
echo " Granting procedure level access"
dbaccess ${DBNAME} - <<EOF 2>/dev/null | awk '/ /{
if ($1 != "procname")
{
print "grant execute on "$1" to public;"
}
}' | dbaccess ${DBNAME} >/dev/null 2>&1
select procname
from sysprocedures
where mode != "P";EOF
if [ $? != 0 ]
then
echo "Unable to grant execute on procedures -- exiting"
exit 1
fi
> -----Original Message-----
> From: pamela [mailto:pamela_twNOpaSPAM@yahoo.com.invalid]
> Sent: 28 July 2000 09:04
> To: informix-list@iiug.org
> Subject: table level privilege
>
>
> What is the most easiest way to grant users privilege for all
> tables in the database ?
>
>
> -----------------------------------------------------------
>
> Got questions? Get answers over the phone at Keen.com.
> Up to 100 minutes free!
> http://www.keen.com
>
___________________________________________________________________________
This email is confidential and intended solely for the use of the
individual to whom it is addressed. Any views or opinions presented are
solely those of the author and do not necessarily represent those of
Sema Group.
If you are not the intended recipient, be advised that you have received this
email in error and that any use, dissemination, forwarding, printing, or
copying of this email is strictly prohibited.
If you have received this email in error please notify the Sema Group
Helpdesk by telephone on +44 (0) 121 627 5600.
___________________________________________________________________________