This content has been marked as final. Show 5 replies
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.
The users the is the owner of the tables in that schema.
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
SQL> create or replace trigger drop_trig
2 before drop on scott.schema
4 raise_application_error(-20200,'Operation not allowed');
SQL> conn scott/tiger
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
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.