NODEFDAC environment variable

Enabling NODEFDAC applies the ANSI-compliant restrictions on default access privileges for the PUBLIC group when tables or Owner-mode user-defined routines are created in databases that are not ANSI-compliant.

In a database that is not ANSI-compliant, when the NODEFDAC environment variable enabled by setting it to yes,
  • the database server withholds default table access privileges from PUBLIC when a new table is created,
  • and also withholds the default Execute privilege from PUBLIC when an owner-privileged UDR is created.
Read syntax diagramSkip visual syntax diagram

prevents default table privileges (Select, Insert, Update, and Delete) from being granted to PUBLIC on new tables in a database that is not ANSI-compliant. This setting also prevents the Execute privilege from being granted to PUBLIC by default when a new user-defined 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. The setting YES, for example, disables NODEFDAC.

Enabling NODEFDAC has no effect in an ANSI-compliant databases.

Important: Enabling NODEFDAC withholds default table or routine privileges from PUBLIC when the object is created, but the NODEFDAC setting cannot prevent the PUBLIC group from being granted the same privileges by a user who holds the necessary access privileges on the new table or on the new UDR.

Copyright© 2020 HCL Technologies Limited