Re: Security Question

In <4jade5$o10_at_ixnews2.ix.netcom.com>, bjonnard_at_ix.netcom.com (Bill Jonnard) writes:
>How can I prevent a group of users from performing any kind of manual
>adjustments to the data, while still allowing them to use SQL*Plus to
>do ad hoc queries? Currently, I have multiple users, but only one
>Oracle user ID, so once somebody gets into SQL*Plus, I can t prevent
>them from performing manual inserts, etc. which I want to do.
>
>If I set up a second Oracle ID that uses synonyms into the main
>database (and is only granted select privs on all the tables), the
>user could easily defeat this measure by logging into the "view-only"
>Oracle ID, and then running the "connect ID/password" command from the
>SQL prompt to get into the "main" database. Obviously, this isn t
>much of a solution. It would be useful to be able to somehow disable
>the connect command, and thus prevent someone from switching into one
>database from another, but I do not know how (or if) this is possible.
>
>Similarly, it would appear that even if I were to set up separate
>Oracle IDs/roles for each specific user, someone could still run the
>"Connect ID/Password" command, and I d be back where I started.
>Again, I am unaware of any means to restrict a user to using a certain
>Oracle ID specified at the Operating System level, or how to somehow
>disallow "write access" to the database while still allowing "read
>access". (The OS is VMS)
>
>Is there any way to let someone use SQL*Plus, but to absolutely
>prevent them from modifying the data unless they have been given the
>explicit "GRANT INSERT/DELETE/UPDATE" privs on that table? If anyone
>could give me a few pointers as to how to best solve these problems,
>I d appreciate it very much. Thanks in advance for any help.
>

You can restrict your user's 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 only privs to the select role
6. Grant appropriate table privs to the all role (select, update, 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 Wed Mar 27 1996 - 00:00:00 CET