question

Leigh Riffel avatar image
Leigh Riffel asked

Balancing Security with Ease of Use

How do you handle the lack of Schema level privileges in Oracle? Oracles 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 schemas. 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 thereby killing 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, create 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. As sydoracle said, proxy authentication can help with some of these problems.

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

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

administrationplsqlschemadevelopersubjective
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1, I need to find a solution for my developers as well. I like #3 option. I'll take it for our 120 dev databases :) But seriously, if it is not available out of the box but I have been toying with the idea of a list of accounts that have 'peer' access to do what you are talking about... Maybe some creative use of a lookup table and some custom DDL triggers to satisfy the 'available immediately' part?
1 Like 1 ·
@dmann +1 We tried the custom DDL trigger route several years ago, but abandon it due to the complexity.
0 Likes 0 ·
sydoracle avatar image
sydoracle answered

There is a variation on (2) where you use proxy authentication (example here) so that developers use their own username/password to connect to another schema.

That doesn't help with cross-schema development, but the point of schemas is to separate discrete areas of functionality so they generally shouldn't be working in multiple schemas concurrently.

3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 Interesting. That does save us from having to give out a bunch of schema passwords and allows developers to be logged using their own credentials. It still requires programmers to maintain a bunch of connections in SQL Developer that will all have to be modified when they change their Oracle password.
0 Likes 0 ·
Regarding cross-schema development, discrete functionality on one level is shared functionality on another. We have schema for materialized views of other databases, for utility packages, for ldap imports, and for other purposes, that are all used by multiple schema. While each developer may not have access to all of these, they may have access to several and may need to create an index on a materialized view that will be used by the schema they are developing in as well as several other. Perhaps this isn't as big a problem as I suspect, but it is a concern.
0 Likes 0 ·
This was the most useful answer to me, so I am going to mark it as the answer. If anyone comes up with anything better I'd be glad to vote for it.
0 Likes 0 ·
Andrew Mobbs avatar image
Andrew Mobbs answered

I'd suggest working with the security model rather than against it.

Schemas are namespaces, and there should usually be a clear functional division between data in the schemas. In the usual case, data should provided from one schema to outside consumers via an API, not via direct access to the underlying objects. This can easily be managed with AUTHID DECLARER on PL/SQL subprograms.

Admittedly, this isn't always going to work, and sometimes you do need to open up an object to other users. There, roles make the management of privileges much easier, especially if there's a large matrix of users and permissions.

I'd take the view that if one user needs full and unfettered access to another user's schema, including to any objects that might be created there in the future, then something has gone badly wrong with your data model.

I suspect you know the basics, but to run through an example, first create two developer's accounts, and give them permission to create roles:

SQL> CREATE USER dev1 IDENTIFIED BY dev1 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;

User created.

SQL> CREATE USER dev2 IDENTIFIED BY dev2 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;

User created.

SQL> GRANT connect,resource TO dev1;

Grant succeeded.

SQL> GRANT connect,resource TO dev2;

Grant succeeded.

SQL> GRANT CREATE ROLE TO dev1;

Grant succeeded.

SQL> GRANT CREATE ROLE TO dev2;

Grant succeeded.

Setup a simple schema and a little data for dev1:

SQL> CONNECT dev1/dev1
Connected.
SQL> CREATE TABLE foo(a NUMBER, b VARCHAR2(200));

Table created.

SQL> CREATE TABLE bar (x NUMBER, y DATE);

Table created.

SQL> INSERT INTO foo VALUES (17,'wibble');

1 row created.

SQL> INSERT INTO bar VALUES (23,sysdate);

1 row created.

SQL> INSERT INTO bar VALUES (42,sysdate+1);

1 row created.

SQL> COMMIT;

Commit complete.

Now things get a little more interesting. We create a function with AUTHID DEFINER, and a couple of roles, give those roles appropriate permission and grant those roles to dev2 (remember, this is all being done as dev1, not as DBA).

SQL> CREATE OR REPLACE FUNCTION fun1(v_x NUMBER)
  2  RETURN DATE
  3  AUTHID DEFINER
  4  AS
  5   v_y DATE;
  6  BEGIN
  7  SELECT max(y) INTO v_y FROM bar WHERE x > v_x;
  8  RETURN v_y;
  9  END;
 10  /

Function created.

SQL> CREATE ROLE dev1exec;

Role created.

SQL> CREATE ROLE dev1sel;

Role created.

SQL> GRANT dev1sel TO dev2;

Grant succeeded.

SQL> GRANT dev1exec TO dev2;

Grant succeeded.

SQL> GRANT SELECT ON foo TO dev1sel;

Grant succeeded.

SQL> GRANT EXECUTE ON fun1 TO dev1exec;

Grant succeeded.

Now we connect as dev2 to see what effect this has had:

SQL> CONNECT dev2/dev2;
Connected.
SQL> COLUMN b FORMAT a10
SQL> SELECT * FROM dev1.foo;

         A B
---------- ----------
        17 wibble

SQL> SELECT * FROM dev1.bar;
SELECT * FROM dev1.bar
                   *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> SELECT dev1.fun1(30) FROM dual;

DEV1.FUN1(30)
---------------
08-APR-10

SQL>

Access to dev1.bar is purely through an API, which we've only been given execute permission on, but the AUTHID DEFINER clause means that we can see the data in dev1.bar even though we can't access the table directly.

Access to dev1.foo is via the role that was created and granted by dev1.

At no point did the DBA need to get involved in providing access to any objects. The developers can handle this themselves. A good database design should minimise the amount that you need unmanaged access to another schema. Where that permission does need to be given, it can be managed by the developers through well defined roles for who gets to access which objects.

In the extreme case, where all hope of good database design and user security has already been abandoned, rather than giving developers DBA access, you could have a stored procedure like:

CREATE PROCEDURE openaccess(username VARCHAR2)
AS
BEGIN
FOR i IN (SELECT owner,object_name FROM dba_objects WHERE owner=username AND object_type IN ('TABLE','VIEW')) LOOP
EXECUTE IMMEDIATE 'GRANT SELECT ON '||i.owner||'.'||i.object_name||' TO PUBLIC';
END LOOP;
FOR i IN (SELECT owner,object_name FROM dba_objects WHERE owner=username AND object_type IN ('PROCEDURE','FUNCTION')) LOOP
EXECUTE IMMEDIATE 'GRANT EXECUTE ON '||i.owner||'.'||i.object_name||' TO PUBLIC';
END LOOP;
END;
/

and arrange for it to be run whenever is appropriate.

10 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 Thank you for taking considerable time to provide a comprehensive answer. For various reasons it will take me a few days to respond, but I do have some questions about this approach.
0 Likes 0 ·
Indeed, there should be a clear functional division between schemas. I also agree that most data should be access through APIs. We are moving all new development strongly in that direction, but it will take time to re-write existing code. Between existing code and schemas that dont lend themselves to efficient internal APIs there is still a problem here to solve.
0 Likes 0 ·
Perhaps there is something wrong with our data model. I dont see it yet, but I am open to the possibility because I certainly dont understand a development model that doesnt require a developer to have access to new objects in schemas they do development for.
0 Likes 0 ·
Dev1 seems to be a data schema. As such a developer would be logging in as dev1 to create tables/functions/rolls and to grant permissions to dev2 (himself?). If he can already login as dev 1 why would he bother? Perhaps dev2 is an application that does need execute on the function in dev1. Of course then neither dev 1 nor dev2 are developer logins, but if both are developers the answer doesnt address how a developer would create objects/methods in the schema containing the data or how he would grant permissions on those to an application login.
0 Likes 0 ·
If a new object is created, surely a new API should also be created alongside it? I think of it as analogous to public/private methods in OOP, in Java methods aren't public by default, only if declared as such. If you need access to all future objects in another schema, it's worth asking why it's a separate schema at all, and if you can just merge the schemas.
0 Likes 0 ·
In the application login example; dev1 creates a role "appuser", that role gets granted to the application logins that want to use dev1's schema. When dev1's schema changes, appropriate permissions are granted to that role. All applications automatically get the right permissions.
0 Likes 0 ·
In another example, dev1 and dev2 are two interacting schemas for different applications, say a CRM and a billing system. Both need access to data in the other, but this should be via an API rather than the billing system querying. i.e. "EXEC crm.get_address()" rather than "SELECT * FROM crm.addresses". This abstraction allows the CRM to be upgraded, including the tables restructured, without affecting the billing system.
0 Likes 0 ·
In my opinion APIs are not as useful on the table level as they are on a functional level in which one API call may interact with one or more tables to perform a useful action. As such a new table could be created to be used by an existing API that would not require any changes to the API specifications. In any case programmers would need access to the new object and to any new API created.
0 Likes 0 ·
Programmers need access to all future objects in multiple other schema because they are doing programming. They are creating the objects and creating the API and creating the rolls to grant to applications.
0 Likes 0 ·
dev1=CRM, dev2=Billing helps a lot. Your statements make sense in that context and closely follow what we are doing. What I don't see is how this addresses my question (sentences 2 & 3 at the top). How are permissions best handled for Developers?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.