Question:

From SQL*Plus user's guide,I know there is a table : PRODUCT_USER_PROFILE.

I could restrict user's privileges by inserting  values properly
for example :
SCOTT could not issue ALTER xxx in SQL*Plus then insert :

insert into  product_user_profile (product,userid,attribute,char_value)
values ('SQL*Plus','SCOTT','ALTER','DISABLED')
/

After commit,SCOTT would never alter objects any more in SQL*Plus.
But  besides SQL*Plus,there are many tools could connect to Oracle database.
Like Schema manager,ODBC,VB,Power Builder.....etc.

Is it possible to disable other softwares to do the same thing?
I hope i could maintain all my tables through SQL*Plus,
i.e. CREATE / ALTER / DROP are only avaiable in SQL*Plus.

Could I ? and how??
Thanks for any help!!

Answer 1:

Very simple, provided you are not connected as the owner of the table, you
can't protect the owner against himself.
You need to create two roles, the first role has select privileges on all
objects, this role is enabled by default.
The second role has the normal privileges. This role is _not_ enabled by
default, you need to enable it manually or programmatically by the set role
command in any front end you can trust not to create havoc.

Hth,

Sybrand Bakker, Oracle

Question 2:

Thanks Sybrand!

I understand.

But I ever had a very bad experience :
someone connected to database and did bad sql
like delete table or drop table;
of course all rows were gone and had to recover database until time.

So I want to know if Oracle database could connected only by "SOME Softwares"
or only by "SOME IP-address".
(Maybe listener could do some filters.)

Changing password sometimes and setting privileges properly are fundamental
principles.

Answer 2:

To restrict the use of your database to certain IP-addresses, configure
protocol.ora on the server for invited_nodes and excluded_nodes.

Regards,

Sybrand Bakker, Oracle DBA