Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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 Jan 6 '11 at 16:48
add comment

2 Answers

up vote 9 down vote accepted

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.

share|improve this answer
    
+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) –  Leigh Riffel Jan 5 '11 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 Jan 6 '11 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 Jan 6 '11 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? –  Leigh Riffel Jan 6 '11 at 16:11
    
@Leigh : they might also be a heavy enough user of Oracle to have gotten a site license, and you only have to backup the scripts to make changes, not the whole database ... my issue would be that it'd be unlikely for the dev instances to have the same data in it as production, so it'd be impossible to run benchmarks on their local systems. –  Joe Jan 6 '11 at 18:19
show 1 more comment

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.

share|improve this answer
    
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? –  Leigh Riffel Jan 6 '11 at 16:28
    
@Leigh Updated with details. –  Brian Ballsun-Stanton Jan 7 '11 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. –  Leigh Riffel Jan 7 '11 at 15:22
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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