Re: Security / ODBC Drivers

David/Collin/Jonathan I have been playing round with this idea of protecting the database from ODBC users. Here are some things I came up with. 1) Grant all users read-only rights to the database by REVOKEing UPDATE, INSERT and DELETE from all tables. 2) For the application users, create a seperate login by doing some kind of transform on the login id's. For instance, you could put a _a at the end. Thus if my login id was sujit, I would have a new login called sujit_a. 3) At this point, the DBA and the SA know the passwords for the application users like sujit_a. 4) The DBA would log in and change the password for each of the application users. At this point, only the DBA knows the passwords for the application users. 5) Encrypt the passwords using a 2-way transform such as the Unix crypt command (not the crypt() system call), using a key known only to yourself and store it in a database table. This encryption key is the same for all the passwords encrypted. The plaintext password for each of the application users are the same as the Unix password. 6) The DBA provides an object file (secureconnect.o say) which has a function int secureconnect(dbname, username), which transforms the login name to the transformed name, looks up the encrypted password in the database, runs the crypt command to decrypt the encrypted password to its plaintext form using the same encryption key used in step 5. Then it connects as the new application user by issuing the connect command (ESQL/C). CONNECT to :dbname USER :appuser USING :password 7) Replace all CONNECT TO :database with a call to the function secureconnect(dbname, getlogin()) This way the obscurity part is limited to the encryption key in the secureconnect.o program. If the DBA has written the secureconnect.ec program himself, then the system security can only be compromised by the DBA, the janitor who has the keys to the DBA's drawer (assuming the DBA kept a list of plaintext passwords of the application users in his drawer), and the really smart hacker-user armed with the crypt breaker's workbench. If the DBA used a programmer to code the above, the programmer would be added to the list of people who can compromise the system. If it is acceptable to have the application prompt for a password, we can use the crypt() system call to generate an encrypted password, which is more secure. As before, the int secureconnect(dbname) can change the username to the application username, and validate the entered password against the one stored in the database by encrypting it with the secret encryption key. Since the user maintains his own passwords (after the DBA "creates" the user by adding a row to the passwords table), this is also more secure, since the DBA no longer knows the user's passwords, and neither can he decrypt it since crypt() works only one-way. A good (or bad) thing is that you are also protecting the database from users with dbaccess as well as ODBC users. Please let me know what you all think. If you can find holes in this setup, I would appreciate knowing. Thanks Sujit Jonathan Leffler <jleffler@earthlink.net> on 10/01/99 09:46:43 PM Please respond to jleffler@earthlink.net To: informix-list@iiug.org cc: (bcc: Sujit Pal) Subject: Re: Security / ODBC Drivers Colin M McGrath wrote: > You might be able to control this by revoking add/update/remove privileges > from public, then add those privileges to a special user, and do a setuid > on your 4gl startup menu program to cause your 4GL users to become the > one user with those privileges. But beware that Informix normally takes the real UID rather than the effective UID (for hysterical raisins, as usual). This makes it necessary to use a SUID root program to do the UID-setting. That opens up a whole bag of different security issues. > David Killough wrote: > > We have set up every user on our system to be able to update, delete, > > and insert into any table in our database. Our 4GL programs have been > > the only interface our users have had with the database, so we have been > > able to control what is changed. Now we are trying to give users > > reporting capabilties through ODBC drivers and have not been able to > > restict the access to read-only. They link the tables into MS-Access > > and can change anything they want to!! I can't change the database > > permissions because the 4GL programs depend on the user and the current > > permissions. I need to find out if there's a way to keep the ODBC > > driver access read-only while keeping the 4GL access read-write. This a perennial problem. You want to be able to identify both the user and the program which is accessing the database, and to grant the user certain privileges when using certain applications, and you want to grant the same user different privileges when using other programs. That level of granularity is not supported. Nor is it easy to see how to add it, desirable though it undoubtedly is. You might be best off writing the modify (INSERT, DELETE, UPDATE) code in DBA-privileged stored procedures, and only allowing the 4GL code to use those (and hence deny users the option of modifying the tables). However, this is still security through obscurity. Ditto for using ROLES. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.62 -- see http://www.perl.com/CPAN #include <disclaimer.h>