Re: Security for sqlplus--

Brian Spears wrote:
>
> 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

Brian,

We had exactly the same problem with an application that used Oracle Forms 4.5 on the PC as the front end. The users had to have all CRUD privileges on the database tables to use the front end, but they also had direct access to SQL*Plus for queries and started to screw up the tables! And telling them to stop didn't work.

We used the PRODUCT_USER_PROFILE for a time. It did work, but was a little maintenance intensive (about 3-4 INSERTs each time you create a user) and just seemed a bit kludgy.

The approach we use now was recommended in the Oracle DBA class:

1. Create a role SELECT_ROLE and give it all necessary SELECT privs.
2. Create a role UPDATE_ROLE and give it all necessary UPDATE/INSERT/etc privs. Create it with a password.
3. When you create a user, grant the user both SELECT_ROLE and UPDATE_ROLE, but make SELECT_ROLE that user's only DEFAULT role (ALTER USER x DEFAULT ROLE ...). (SELECT_ROLE has to be granted directly to do this -- ie -- not indirectly through UPDATE_ROLE.)
4. In your front-end application, just after connecting to the database, send the SQL command: "SET ROLE UPDATE_ROLE IDENTIFIED BY password". In Forms 4.5, you can use Forms_DDL to do this. I also took some simple steps to hide this password in case some enterprising user went poking around in the form-executable file. In Forms 4.5, you also have to be careful to catch all of the places where a new connection might implicitly be made (ie - at the start of each new form module).
5. Don't give the users the role password, so that they can't use the SET ROLE command themselves within SQL*Plus.

Hope this helps.

Barry Schader Received on Mon Dec 30 1996 - 00:00:00 CST