Releasing database access to others

[kenny chia]

Hi all
I'm currently in charge of a critical database system (Oracle 7). I intent to release this database to other users for data extraction only. These users will run select statements in the database to extract data

Questions
1) How do I prevent these data extraction users from running DML and DDL statements?

2) How do I prevent users from hogging server CPU with poorly optimised SQL written by them?

Thanks for any suggestions

[Sanjay Kumar Suri]

1. Database can be opened in read only mode with the following:

SQL> alter database open read only;

2. Oracle 8 & 9 offer Resource Manager to control things like CPU resources. Not sure about Oracle 7.

sks

[Eric Antunes]

1) You must create a new Oracle user for them. Then you create views only for the tables you want them to extract data.

2) In Oracle 8 you can create a new profile (in Oracle Enterprise Manager) and assign this profile to the new user:

ALTER USER "" PROFILE ;

[Sanjay Kumar Suri]

You can also use following Oracle features to control access to DML/DDL statements:

1. System Privileges like create table.
2. Use Object Privileges on newly created views as suggested by Antunes.

sks

[Jean-Luc Oudart]

If you give them a dedicted listener, change the nice value so their oracle process will inherit the nice value.

Regards,
Jean-Luc

[Arturo Galbiati]

both your needs may be covered using an appropriate profile and assigning it to the users.
HTH,
Art

[R. Allan Hicks]

We handle the problem through user accounts and grants. As has already been mentioned, you can create one or more users for your people. Unless public synonyms exist, you can grant read access to the system objects to the account(s) and not allow insert or updates on the table. Oracle is pretty good about not giving a user anything that the dba doesn't say he can have. You can use roles to cluster previleges and assign a role to a user.

However, something more serious is that you have a critical database running Oracle 7. If memory serves, Oracle 7 is no longer supported. You may want to consider going to at least 9i.

Under 9i you can put users into consumer groups and control how much of the system resources they can grab based on time of day and other parameters.

Under 7, your best defense is (as others have stated) views, but even at that there is no guarantee that a bad sql statement can be applied. If you design the views right, you can eliminate or at least hopefully reduce the amount of ad hoc queries.