Sven K�hler wrote:
A server that the untrusted users must use to access the database, preferably not allowing sql at all, this way you write all of the sql and can hide schema changes from the other users...Maybe it would be a good idea to write a layer inbetween the untrusted users and the database?What do you mean with "layer"?
The layer could also be a liberary that the webapplication must use to access the database, it's also a good place to implement caching (this is what I do here and performance is much better with a warm cache than with a cold one:)...
I don't think you can add triggers or anything else to the metadata tables as they are owned by SYS afair, but if you write a simple script that checks the foreignkeys and let it run at some interval then you are almost in the clear.i should have known that there is table with the foreign-keys, but am i abled to define a trigger/constraint on that table? that way, i could insert a rule that the specific user can't create foreign-keys with the "ON DELETE RESTRICT" attribute.You could also write a simple script that keeps an eye on the "foreignkeycolumns" table and whenever something changes check that it's allowed (between untrusted tables, trusted tables or that the "on delete"-clause is ok if it's a untrusted table referencing a trusted one), this would solve your problem if it ever happened, but it could leave a short window where you might see trouble.
But will that brake anything of the SAPDB internals?
If you are able to detect when the other developers have ended a metadata editing transaction (maybe you control the tool that they use to edit metadata with?) then you can run the foreignkey-checker at that point.
You could simply make an agreement that if the other developers ever need to change metadata then they must use a script you provide that first runs their sql on the database and then does checking of the foreignkeys.
You might want to spare people the experience of working directly with dbmcli anyway, where sql statements end with line changes :-)
Something like this would be handy to have around:
-----------------------------------------------------------------
#!/usr/bin/perl -w
use strict;
die "syntax: runsql <user> <password> <filename>" unless @ARGV == 3;
my ($user,$pwd,$fn) = @ARGV;
my $sql = slurp($fn); # write slurp yourself:)
# Statements are ended with 'EXECUTE' on a line of it's own
$sql =~ s/\n/\t/g;
$sql =~ s/\tEXECUTE\t/\n/g;
open SQL, ">/tmp/sql$$";
print SQL $sql;
close SQL;
system("dbmcli -u DBM,DBM -uSQL $user,$pwd -d YOUDB < $sql");
if ($user ne 'TRUSTED_ADMIN') {
system("check_fk YOURDB");
}
-----------------------------------------------------------------
--
Regards Flemming Frandsen - http://dion.swamp.dk
PartyTicket.Net co founder & Yet Another Perl Hacker
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
