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

In <564m7u$6l1_at_ds9.Dortmund.loca.net>, volker.koenig_at_Duesseldorf.netsurf.de (Volker Koenig) writes:
>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.
>
>This will make it possible - especially when using ODBC - to avoid people
>using tables with "non applications" like word processors or spreadsheets.
>
>Is this true or did someone "tell him about his horse", as we say in germany?
>
>
>
>Bis denne,
> Volker.
>
>----------------------------------------------------------------------
>Ich habe Londo das Leben gerettet, weil im All alles Leben heilig ist.
>Aber wenn derjenige, den man gerettet hat, diesen Glauben nicht mit
>einem teilt, hat man der Gegenwart gedient, indem man die Zukunft
>geopfert hat. (Lenier in Babylon 5)
>----------------------------------------------------------------------
>

I have never heard of granting table access "to programs". However, your colleague may be refering to granting access privileges to Oracle roles and then enabling/disabling these roles in a application program. This is one way to prevent users from accessing tables in "non applications". For example, let's assume that we want to let a group of users do things like delete, update, insert and select within a application but only want to let the same users have select access outside of the application.

To restrict table access permissions using roles:

1. Create two roles user_select_role user_all_role
2. revoke all current object privileges for the user id(s) in question
3. grant both roles to your application users
4. Make the user_select_role the default role for each application user
5. Grant table select privs to the select role
6. Grant appropriate table privs to the all role (update, delete, etc)
7. In your application startup enable the all role using: set role user_all_role;
8. Disable the role when exiting the application.

However, what prevents the user from issuing: "set role user_all_role" in sql*plus? At this point nothing. You could create the all role using a password and embedding the password in your application when you enable the role (i.e., set role user_all_role identfied by RolePw), or, you could use the product_user_profile table to disable the set role command in sqlplus.

Check out the Application Developers Guide for more information.

M.Landa Received on Sun Nov 10 1996 - 00:00:00 CET