CODEWITHSUNDEEP
oracle-databasearchitectureschemadevelopment-environmentoracle11gr2
dante deo
dante deo

Reputation: 63

Development architecture for Oracle database

We have several schemas dedicated to modules (finance, stock, crm etc.) in an Oracle DB 11gR2. We want to establish an architecture for developers such that:

  1. Developers will not have schema owner users' password.
  2. Developers will have time-limited access to DB objects, according to their level, in 2 ways:
    • Top-level senior developers will have unlimited priviliges on a spesific schema when they were authorized for a limited period.
    • Junior developers will have limited priviliges on a spesific schema when they were authorized for a limited period.

In brief, we want to organize our way of development in Oracle DB. How can we build that structure? If I am not clear enough, I can give detailed info to your questions.

Thanks,

Edit: I have an intuition that the case is not so clear. Let me give a simple scenario:

Assume that I have a schema (user with objects) named 'DUMMY' which has a table named 'DummyTbl' and a function named 'DummyFunc'. Obviously the user 'DUMMY' can manage all of those objects in an unlimited way, since she is the owner. However, I do not want developers to use the generic user 'DUMMY' and I want them to logon the database with their own username.

Because, I cannot differentiate the privilige level of developers when I gave them the user 'DUMMY's password. All of the developers can behave unlimitedly. In contrary, I want the senior developer named 'DummySenior' to be able to create,alter,execute objects and also do CRUD operations on 'DummyTbl'.

But, I want the junior developer named 'DummyJunior' only to reference the objects and not carry out CRUD operations on tables. The most obvious way of achieving this is to accompany roles. However, we have several issues with configuring the required roles (ie. afaik for 'DummySenior' to be able to create a table under 'DUMMY' schema, he must have 'create any table' privilige.

However, when 'DummySenior' has that privilige, he can create table under 'DUMMY2' schema too. That is an obvious security breach.)

Upvotes: 3

Views: 233

Answers (1)

Jon Heller
Jon Heller

Reputation: 36922

For the senior developer you can create a definer's rights procedure that simply runs any command passed in. Assuming that DUMMY has the relevant CREATE TABLE/VIEW/... privileges, this effectively gives senior developers all SELECT ANY privileges but only for that one schema.

create or replace procedure dummy.execute_any(p_code in clob) authid definer is
begin
    execute immediate p_code;
end;
/

--For a procedure this powerful, grant it directly to a user.
--That keeps the privilege "obvious", it won't get buried in layers of roles.
grant execute on dummy to senior_developer;

For junior developers, create a role and dynamically grant it the relevant SELECT privileges. You may want to schedule this procedure or run it ad-hoc.

--Create and populate role with some lesser object privileges on a schema.
create role junior_developer_role;

begin
    --Repeat this structure for other objects, like sequences.
    for tables in
    (
        select owner, table_name
        from dba_tables
        where owner = 'DUMMY'
    ) loop
        execute immediate 'grant select on '||tables.owner||'.'||tables.table_name||
            ' to junior_developer_role';
    end loop;
end;
/

Upvotes: 1

Related Questions