Grant access to run select views in SQLPlus

[BenjaminLim]

How do I grant access to a group (say PRODREL) and within the group there are 2 different types of users -> 1 type is application and the other type is users.

For the user type of application he is allowed to access SQLPlus to retrieve data from Oracle whereas the other group should not have access to SQLPlus way of retrieving information from Oracle.

I can't control the PC end as there is a standard build for the organisation and the general agreement is that no one should change the softwares installed.

Please advice whether how I can restrict the users access to Oracle via SQLPlus but still allow the application person to do so.

Thanks.

 

[stefanhei]

Hi.
One workaround I know is the USER_PRODUCT_PROFILE table(Found under topic DBA in the doku). You can specify the product (here SQL*Plus) and the command you want to disable.

Stefan

 

[BenjaminLim]

Hi,
I could not locate the USER_PRODUCT_PROFILE table -> could you advice what can be done next?

I am not holding the DBA role.

Benjamin

 

[sem]

Try to look for product_user_profile in system schema.
You may disable their ability to select/insert/delete via sql*plus:

insert into product_user_profile (product, userid, attribute, char_value)
values ('SQL*Plus', 'YOUR_USER_NAME', ['SELECT'|'UPDATE'|'DELETE'], 'DISABLED')

 

[stefanhei]

Hi.
The table is created in the pupbld.sql skript (in $ORACLE_HOME/sqlplus/admin for 7.3), which has to be run by user system. To insert you usualy need to hold DBA-privilegs.
Under Oracle <= 7 I don't know any other way.

Stefan