What is the NODEFDAC environment variable

Resolving The Problem

PROBLEM

How to prevent default table privileges from bring granted to PUBLIC


SOLUTION

What is the NODEFDAC?

When the NODEFDAC environment variable is set to yes, it prevents default table privileges (Select, Insert, Update, and Delete) from being granted to PUBLIC when a new table is created during the current session in a database that is not ANSI compliant

Where to set it?

The NODEFDAC setting takes effect for the current session, overriding any other setting of the database server for this environment variable.

What to set it to?

Example

$ export NODEFDAC=yes

yes prevents default table privileges from being granted to PUBLIC on new tables in a database that is not ANSI compliant.

When you create a table in a database that is not ANSI compliant, PUBLIC receives Select, Insert, Delete, Under, and Update privileges for that table and its synonyms.

The NODEFDAC environment variable, when set to yes, prevents PUBLIC from automatically receiving these table-level privileges.

This setting also prevents the Execute privilege for a new user-defined routine from being granted to PUBLIC by default when the routine is created in Owner mode.
The yes setting is case sensitive, and is also sensitive to leading and trailing blank spaces. Including uppercase letters or blank spaces in the setting is equivalent to leaving NODEFDAC unset. When NODEFDAC is not set, or if it is set to any value besides yes, default privileges on tables and Owner-mode UDRs are granted to PUBLIC by default when the table or UDR is created in a database that is not ANSI-compliant.