Q: How can I make a database be strict about accepting valid data?
A: In the config file for the server, find and edit to include
'STRICT_TRANS_TABLES' or 'STRICT_ALL_TABLES':
# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_E NGINE_SUBSTITUTION"
This will be the default mode for all new connections.

Q: How can I override these settings on a per-session (per-client, per
connection) basis?
A: Issue this statement from the client:
SET @@sql-mode=[Whatever wanted, but don't include STRICT_x_TABLES]

Q: How can the database server administrator prevent the setting of this
variable by any client/user?

A: ????


--
Remove INVALID from e-mail address.

Brian Smither
Smither Consulting

On Dec 25, 4:54*pm, Brian Smither <bhsmitherINVA... (AT) INVALIDgmail (DOT) com>
wrote:

First of all, why in the world would you allow "users" direct access
to the database from a sql client? If you allow direct access, they
can do pretty much whatever they want. They should be connecting via
some form or other application that does not allow direct sql access
to the database...

You can use ENUM fields, you can have triggers that check for valid
data, you can have foreign keys that check for valid data... -
providing you are using a db engine that uses it (Innodb for
example).

Another good place to start is:
http://dev.mysql.com/doc/refman/5.1/en/grant.html