ChemAxon aa7c50abf8
21-09-2004 14:53:48
Eric,
The JChem Cartridge owner can use the JChem Cartridge, if he/she has CONNECT and RESOURCE roles. (To create such a user, take the Oracle Enterprise Manager Console, go to Network/Databases/<your-database>/Security/Users, right click on SCOTT and select "Create like...")
If JChem Cartridge is going to be used by users not owning the schema where JChem Cartridge has been installed, you have to grant a few privileges. You can find below a Unix shell script which does a fairly good job of granting what is needed. This script grants EXECUTE privileges on all FUNCTION, OPERATOR, TYPE, INDEXTYPE listed in the drop.sql script plus on the jc_idxtype TYPE and creates a few synonyms.
Peter
#####################################################################
# This script will help you enable users to use the cartridge
# if it has been installed in a schema different from that of the
# user of the cartridge. It grants the necessary rights and creates
# the necessary synonyms.
#####################################################################
#####################################################################
# DATABASE
#####################################################################
dbname=pdb
#####################################################################
# The schema where the cartridge has been installed.
#####################################################################
owner=jchem
#####################################################################
# The schema which needs to use the cartridge.
#####################################################################
user=jchemuser
#####################################################################
# Connect string for the cartridge owner.
#####################################################################
# owner_connstr="jchem/tiger@pdb as sysdba"
owner_connstr="$owner/tiger@$dbname"
#####################################################################
# Connect string for the schema which needs to use the cartridge.
#####################################################################
user_connstr="$user/tiger@$dbname"
[ -f grant_privs.sql ] && rm grant_privs.sql;
[ -f create_syns.sql ] && rm create_syns.sql;
cat drop.sql | while read LINE
do
case $LINE in
\#*) ;; #comment-line
*)
obj_type=`echo $LINE | awk '{print $2}'`
if [ "$obj_type" = "function" \
-o "$obj_type" = "operator" \
-o "$obj_type" = "type" \
-o "$obj_type" = "indextype" ];
then
obj_name=`echo $LINE | awk '{print $3}' | tr -d ';'`
echo "GRANT EXECUTE ON ${owner}.${obj_name} TO $user" >> grant_privs.sql;
echo "/" >> grant_privs.sql;
echo "show errors;" >> grant_privs.sql;
echo "CREATE OR REPLACE SYNONYM $obj_name FOR $owner.$obj_name" >> create_syns.sql
echo "/" >> create_syns.sql
echo "show errors;" >> create_syns.sql
fi
esac;
done
echo "GRANT EXECUTE ON jc_idxtype TO $user" >> grant_privs.sql
echo "/" >> grant_privs.sql;
echo "show errors;" >> grant_privs.sql;
echo "CREATE OR REPLACE SYNONYM jc_idxtype FOR $owner.jc_idxtype" >> create_syns.sql
echo "/" >> create_syns.sql;
echo "show errors;" >> create_syns.sql;
echo quit >> grant_privs.sql
echo quit >> create_syns.sql
set -x
sqlplus "$owner_connstr" @grant_privs.sql
sqlplus "$user_connstr" @create_syns.sql
set +x