How do I allow integrated logins at database startup?

[VolkerBarth]

For integrated logins, the docs do recommend to use "Temporary public options for added security", namely to use the following statement:

SET TEMPORARY OPTION PUBLIC.login_mode = 'Standard,Integrated';

(or Kerberos likewise).

But they do not clearly tell when/where to specify that statement - except for the rather vague "each time the server is started". I do understand that a DBA can start (or initially connect to) a database with its default PUBLIC.login_mode = 'Standard" by specifying his database credentials and then issue the above statement explicitly.

However, how do I achieve the same purpose when databases are started automatically by a service, and there might be no chance for a DBA user to issue such a statement initially before a regular user needs to make use of an integrated login?

I'm quite sure issuing that automatically in a DatabaseStart event or in a login_procedure would do the trick, however, that would probably work for a copied database on a different box, too, and would then reduce that temporary option to absurdity.

What point do I have missed here?

[MarkCulp · Accepted solution]

I don't think you are missing anything. If you want to keep your data protected then you either (a) need to not have intergrated login (or kerberos or PAMUA) turned on by default OR (b) you need to physically secure the database file so that it cannot be copied.

If you are starting your database server (and database) automatically by a service then the only realistic option would be to physically secure the database file. If you were to turn on integrated login automatically then anyone that can copy the database could get access to the data (through methods that are left as an exercise to the reader).

You could attempt to create a database start event that somehow verified that the database is physically located where it is suppose to be (e.g. using hostname, NIC MAC addresses, ...) and once verified then set the temporary option, but even doing this could get spoofed and bypassed by a determined hacker.

BUT I'll raise another point: If you are not physically securing the database file(s) and are not encrypting the database then your data is not secure anyway... so if you are concerned about security (and don't want to encrypt the database) then physically securing the database resolves both issues.

HTH

[VolkerBarth · Accepted solution]

Well, database encryption and pysically securing the database files are means we already use, so we might enable integrated login by default.

But just to understand: So then the cited SET TEMPORARY OPTION statement is primarily meant to be explicitly executed by a DBA after the database has been started?

[MarkCulp · Accepted solution]

Re: "... explicitly executed by a DBA after the database has been started". This is correct - it needs to be either explicitly executed by a DBA (a real person) or by some external entity (i.e. some external event/process that runs outside of the database).