Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

Releasing database access to others

kenny chia
Regular Advisor

Releasing database access to others

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
All Your Bases Are Belong To Us!
6 REPLIES
Sanjay Kumar Suri
Honored Contributor

Re: Releasing database access to others

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
A rigid mind is very sure, but often wrong. A flexible mind is generally unsure, but often right.
Eric Antunes
Honored Contributor

Re: Releasing database access to others

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 ;
Each and every day is a good day to learn.
Sanjay Kumar Suri
Honored Contributor

Re: Releasing database access to others

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
A rigid mind is very sure, but often wrong. A flexible mind is generally unsure, but often right.
Jean-Luc Oudart
Honored Contributor

Re: Releasing database access to others

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

Regards,
Jean-Luc
fiat lux
Arturo Galbiati
Esteemed Contributor

Re: Releasing database access to others

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

Re: Releasing database access to others

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.
"Only he who attempts the absurd is capable of achieving the impossible