Re: Security for sqlplus--

Brian Spears <brian_spears_at_dsi.bc.ca> wrote in article <32C01FD6.3ABA_at_dsi.bc.ca>...
> HI,
>
> Does anyone know how to limit how SQLPLUS is used. Ie. All users
> have all privs to schema through an application's standard use of
> ORACLE. I want to allow users to use sql*plus with select privilege
> only. Can I control who can access sql*plus? If so I could create
> a user with no quota and only select privs to access sql*plus. This
> solution would do if I can control user access to the tool.
>
> If I can not control users - tool, does anyone know of another
> way to save the database. I know of a 3rd party tool but I
> want to avoid this if possible for the present time.
>
> Brian Spears
> DBA , Dynapro
>

It sounds like you want your users to be able to manipulate table data (insert,update,delete)
while in an application but not while using sql*plus. One way to do this is using roles to limit
schema privileges based on access method. For example:

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 (select, update, delete, insert)

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 Tue Dec 31 1996 - 00:00:00 CST