Re: Grant privileges to "a program"...?

Volker Koenig (volker.koenig_at_Duesseldorf.netsurf.de) wrote:
: Hi there!
 

: A collegue just claimed it was possible to grant table privileges to "a
: program" in oracle, e.g., not the id of the user is checked against the
: table-privileges but the name or any "hidden" id of the application program
: itself.

<snip>

I think what is being talked about here is using the dbms_application package to register client info. I use it to verify only apps thatare developed with in our division update crutial tables. Here is how it works:

1. Every apps is assigned a 30-40 random character password(client info).
2. A table holds all of the valid client info passwords, select on by only a particular user(a secure user in our case);
3. Each app executes dbms_application.set_client_info passing in the client info password.
4. a trigger is written(NOT for each row) upon update,delete,insert to berify the client info password against the set of recognized ones. if not legit app, raise application error, etc else let the transaction pass thru.
5. Yes i know it is not good to store a hardcoded password in an app, hence the random 30-40 characters.
6. This also give us the capability to know when someone is not using a current version of an app, since we change the "key" when sending out new versions.

Take a look at dbmsutil.sql under your oracle_home/rdbms/admin.

joe

-- 
Joseph S. Testa, Database Administrator, Ohio EPA
  N8XCT, Emergency Coordinator (EC) & Local Govt Liaison (LGL)
  Pickaway County, OH and Official Relay Station (ORS), message content
  are MY views and NOT my current employer.