RE: Security/Accounts in an Oracle development environment

Hi Paula,

> This is driving me crazy and I cannot help thinking that SQL Server provides an easier security model.....
>
> In our development environment we set up schemas. Those schemas, of course, own all the objects associated with an application in a database.
>
> So, to provide read/write access (i.e. the ability to modify objects in that schema) the choices are:
>
> A-give that schema usercode/password (what if multiple developers?????) - we appoint an "application dba"
>
> B-give that person the ability to create any table, .... or any of the *** any privileges which to me is even worse.
>
> Is there an add-on, another option?

I see you also missing a sql clause like

"GRANT CREATE TABLE ON SCHEMA shared_schema TO scott"

and of course for other objects and for DROP / TRUNCATE statements.

Handling those privileges on schema base would allow some people work together for a specific project in one schema.

From our specialist divisions there was requests to do this for some projects. the first requests was satisfied by communicate the password to all the users who participate in the project.
No need to say this is not a real solution.

Now we use a definer rights package installed in the shared schema which has an utility to copy objects from the login user schema of the calluser into this shared schema. Because there are no dba privileges on the shared schema the users first must grant privileges on the objects created first in its own schema to the shared schema directly to allow access it via this package.
This package copies tables via CTAS and build views, procedures, functions, package declarations via reading the source from all_views/all_source and then just create it. Objects not grantable like triggers or database links are not possible with this mechanism. For database links we added a simple create utility to the package. Triggers (and some other object types) would need a definer rights utility in a high privileged schema with own acces control to handle the copying. Exact building of a table like the template (i. e. partitions, storage clauses, etc.) in the users own schema may also cause some coding. Building a create statement from via reading the data dictionary may be nigthmare for tables and of course must be adapted for all new features with every new oracle version. dbms_metadata doesn't allow access to another schema in a definer rights package because the necessary role SELECT_CATALOG_ROLE is of course disabled like any other role in a definer rights utility - thus one have to build a wrapper on it which has its own privilege management for secure access. In special for developers you may need to have a mechanism for those building. For our specialist division users we only implement the simple solution because no more is needed.

And building code directly to handle all necessary SQL statements in a shared schema is a multiple of complex like the copy mechanism and also must be adapted for all new features with every new oracle version.

For simplified handling we bound a special role to a shared schema which is granted to each user who needs to work with the shared schema.

In the package after the copying is all privileges on the copied object are granted to this role and thus all co-workers have access to it.

For simple creation of such a shared schema we build an utility for creating it like an existing template shared schema, including a special profile, all privileges found and the special handling package. It creates the new shared schema with a random password which is discarded after creation. The new created shared schema is account locked also and has its password expired. The creation utility of course needs some dba privileges and is located in system schema which got some additional direct privileges to handle this.

regards

kf

--
http://www.freelists.org/webpage/oracle-l