Re: Security for sqlplus--

You can use PRODUCT_USER_PROFILE table to disable SQL and SQL*PLUS commands for a particular product (i.e SQL*PLUS, FORMS). Through this you can disable SELECT, INSERT, UPDATE etc for individual users (or for all users ) when they connected to your database using SQL*PLUS.

To disable SELECT for the user SCOTT you insert following values in the PRODUCT USER PROFILE table. Query your data dictionary for PRODUCT_USER_PROFILE table. It will be there

INSERT INTO PRODUCT_USER_PROFILE (product, userid, attribute, char_value) values ('SQL*Plus', 'SCOTT', 'SELECT', 'DISABLED');

Please note that the values in the above statement are case sensitive. Product value should be exactly 'SQL*Plus'. It will not work if you insert 'SQL*PLUS' or 'sql*plus' etc.

Using PRODUCT_USER_PROFILE ,You can disable commands for SQL*Plus. But, if user's access the database using ODBC, PRODUCT_USER_PROFILE will not disable these commands for the user.         

        The best thing might be, disable INSERT, UPDATE, DELETE, ALTER, etc for all your developers for the product SQL*PLUS. Please note that if you disable these commands, They even will not be able to SELECT, UPDATE, DELETE, ALTER data from their own tables. So, You may want to create a common userid or separate userid's (additional) with only SELECT privilege on your application tables, and don't disable any commands for these ID's. When they connect to Oracle using this ID they will be having only SELECT privielge on the application tables.

Barry Schader <barry.schader_at_medtronic.com> wrote in article <32C86641.782C_at_medtronic.com>...
> 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 Wed Jan 01 1997 - 00:00:00 CST