How do you handle the lack of Schema level privileges in Oracle? Oracle’s 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 schema. Here are some of my options with their downsides:
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 to the detriment of database performance. I want the programmers to develop in the database, but this method would greatly discourage it.
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, 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.
Grant programmers proxy authentication privileges on each schema they need to do development for. This keeps them logged in as themselves without having to grant them privileges other than the proxy privilege. Disadvantages include programmers having to maintain separate connections for each schema they proxy for, cross schema development is more cumbersome as connections have to be constantly changes, and packages using public database links with passed authentication won't compile inside proxy connections.
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 to 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 using one connection. 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?