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?

  • 3
    +1 great question - this, coupled with the lack of integrated source control, is a major issue with Oracle in a multi-developer environment.
    – ScottCher
    Commented Jan 6, 2011 at 16:48

2 Answers 2


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.

  • +1 With our general use database, developers work on very diverse projects, so the principle of least privilege would not give them complete accesses to even the development server. (en.wikipedia.org/wiki/Principle_of_least_privilege) Commented Jan 5, 2011 at 22:40
  • @Leigh -- I probably should've clarified ... the dev server fell under what you had as #1 for the most part, not #4
    – Joe
    Commented Jan 6, 2011 at 3:06
  • 1
    I remember having DEV databases cloned from Production, then complicated grants to enable developers to work without restrictions. In the end it was easier to give each developer their own database and DBA access. Would then feed changes into Dev via a release cycle. Should be easier now with virtualisation.
    – Sumnibot
    Commented Jan 6, 2011 at 13:37
  • @Sumnibot - Easier to install maintain, backup, etc. a separate database for every developer than to grant them permissions!?! In addition to the time required to keep each of these up to date it would seem like the licensing costs would be considerable or did you not give them the enterprise version? Commented Jan 6, 2011 at 16:11
  • 1
    Does not contain an concrete answer for me.
    – Michael-O
    Commented Feb 17, 2012 at 15:16

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

  • I’ve seen the methodology you reference attempted without much success; however, I believe you are correct that it could be done with extensive testing. The problem is that this only allows developers select access on tables. Granting create table directly or through a role only gives them create table privileges on their own schema. They still cannot create tables, procedures, packages, triggers or any other objects in any schema except their own, or is your point that they should only be creating objects in their own schema even on development? Commented Jan 6, 2011 at 16:28
  • @Leigh Updated with details. Commented Jan 7, 2011 at 8:26
  • This just isn't how Oracle works. Try this: create user u1 identified by "ThisIsMy1Password"; create user u2 identified by "ThisIsMy1Password"; grant dba to u1; grant connect to u2; connect u1/"ThisIsMy1Password"@db; grant create table to u2; connect u2/"ThisIsMy1Password"@db; create table u1.t1 (c1 varchar2(10)); The last step fails with insufficient privileges. Commented Jan 7, 2011 at 15:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.