Revoking Privileges

The users the is the owner of the tables in that schema.

Users can always ALTER, DROP etc their own tables. That's the meaning of ownership. If you need a stable schema then you have to have an application owning account that ordinary users can't fiddle with. To be honest this is an application/configuration management issue rather than a SQL one.

Cheers, APC

Hi,
As you said in your last post that you want to restrict the unauthorized DDL changes but teh owner of schema is always has rights to alter anything. Besides the owner of schema, for all other users you can restrict teh access and i don't see any reason to restrict the schema owner from firing DDL statements.

Please, clearify your requirment more clearly......

How about triggers ?

SQL> conn / as sysdba

Connected.

SQL> create or replace trigger drop_trig

  2  before drop on scott.schema

  3  begin

  4     raise_application_error(-20200,'Operation not allowed');

  5* end;

SQL> /



Trigger created.



SQL> conn scott/tiger

Connected.

SQL> drop table test;

drop table test

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-20200: Operation not allowed

ORA-06512: at line 2





SQL>

As I said in my previous post, the owner of the schema is the authoriser of DDL to their own tables. I can envisage two situations in which you are having problems: people putting changes into production in an ad hoc fashion or people destabilising a development environment.

The solution to the first problem is to revoke access to the production database from the anybody who shouldn't have it. If they want to deploy changes they must hand over a script to the DBA who runs it from their behalf. This is standard practice.

The solution to the second problem is to isolate the shared schema from other developers whilst it is being worked on. Usually this means having separate databases for development and an integration environment. Again, access to the integration database needs to be controlled.

To repeat, your problem is not a SQL issue, it's due to the way you organise and manage your application development and live environments. Schema owners need rights to alter their objects. What they don't need is the access to make those changes in[i] every database. I quite often have application owning users that lack the CONNECT privilege. This means changes to those schemas must be applied by some other authorised (i.e. DBA) account. Of course, this does mean that the applications cannot use those accounts to undertake business activity. But having appropriate user accounts for that sort of thing is also good practice.

If you don't find this advice has solved your problem then you need to give us more specifics about your situation.

Cheers, APC