How do you handle the lack of Schema level privileges in Oracle? Oracles security architecture works well for applications that only need object level privileges and it works well for DBAs that need few restrictions. However, there seems to be a big gaping hole in the architecture for programmers doing development with a front end application and PL/SQL in multiple schemas. Here are some of my options with their downsides:
1. Make each programmer do development in their own schema. The DBA will grant object level privileges to programmers needing them. Any package development must be done by a DBA. The major downside is that programmers will use the database like a bit bucket thereby killing database performance. I want the programmers to develop in the database, but this method would greatly discourage it.
2. Give each programmer the username/password for the dozen or so schema they need to do development in. Grant these application schema permission to create procedures, create tables, etc. Some of the disadvantages with this approach are that programmers have to maintain multiple logins and are seldom logged in as themselves. Cross schema development is also difficult. As sydoracle said, proxy authentication can help with some of these problems.
4. Give each programmer DBA privileges. The downside here is security. No schema programmer can be kept out of any schema and any programmer can impersonate any other programmer (DBA).
There seems to be a missing option #3.
3. Grant each programmer SELECT/INSERT/CREATE/etc. privileges on the schema they need to do development in. They login as themselves to do their work. New objects in the schema they have access to are immediately available.
Am I missing something? How do you handle application programmers that do PL/SQL development?