Re: Q: Column Level Security

In article <3jhusn$joi_at_server.st.usm.edu> Jonathan Wayne Ingram, jwingram_at_whale.st.usm.edu writes:
>: One ideas was:
>: Create roles with the specific privileges in the tables and using SET
 ROLE,
>: select the role specific to our application. The problem with this
 is that the
>: role is granted to the user, so he can activate the role manually and
>: have access to the tables.

I would suggest that you use password protect roles in conjunction with the DEFAULT ROLE clause on the CREATE/ALTER USER commands.

The DEFAULT ROLE clause determines which roles are automatically enabled when the user logs in. You should set that list to the list of non-password protect roles that the user has.

Grant the password protected role to the user, but don't tell them the password. It will not be enabled by default, and they can't enable it without knowing the password.

You application will know the password, either by hard-coding it in the application (make sure the user can't read it), or by storing it in encrypted form in the database, and knowing how to decrypt it. When your application logs the user in, it can read the password, decrypt it, then enable the role for the user.

If the user accesses the database in any way other than your program (sqlplus, ODBC, etc.) the role is not enabled, and they can't get to the data.

I have a sqlplus script that I wrote that builds the alter user commands to set each user's roles to the list of their non-password protected roles. I also have VMS DCL and UNIX C-shell scripts that run that sqlplus script. If you are interested, let me know and I'll e-mail them to you.

Bob Swisshelm | swisshelm_at_Lilly.com | 317 276 5472 Eli Lilly and Company | Lilly Corporate Center | Indianapolis, IN 46285 Received on Fri Mar 10 1995 - 12:18:54 CET