Security for Application Developers doing PL/SQL work in Oracle

Question

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:

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 to the detriment of 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, 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.

3. 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.

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 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?

Answer 1

Back in the days when I worked in an Oracle shop, we had a specific 'dev' (development) server, which had different security restrictions than the 'prod' (production) server. Developers could do whatever they needed, and then we'd hand off the necessary scripts to the DBA to apply to the production server.

In the case of our critical systems (SCT Banner, for tracking classes & students, and Oracle Financials), there were also 'test' and 'seed' servers. Test was for user acceptance testing before stuff migrated from dev to prod; 'seed' was stock install of the software, so should we find a bug, we could verify if it was something we had introduced or came from SCT or Oracle's software.

Answer 2

Use roles to associate collections of objects, then grant access to the roles

The GRANT statement allows the DBA to:

Object privileges for a particular object to users, roles, and PUBLIC. Table 18-2 lists object privileges and the operations that they authorize.

As object privileges can be granted to a role, it is relatively easy to grant a role access to all tables in a schema:

sql> spool privs.sql
sql>select 'grant select on scott.'||table_name||' to role_select;' from dba_tables where owner='SCOTT';
sql>@privs.sql
sql> grant role_select to john,sam,peter;

This, combined with GRANT CREATE TABLE issueed by the appropriate schema-user to the role means that developers can select and create tables. It's not perfect as a created table requires the script to be run again, but WITH GRANT OPTION suggests that each developer can then grant access to the table they created to the appropriate role.

This suggests that you can create DDL level triggers that can execute the appropriate granting process, though significant amounts of testing will obviously be necessary, it should be possible to make the create table statement automatically grant appropriate permissions to appropriate roles.

Edit --

According to GRANT, the CREATE TABLE privilege:

Create a table in the grantee's schema.

Thus, by giving them create table, alter table, etc.. from the correct user, they should be able to access that user's schema as if they were the appropriate user.