RE: Security/Accounts in an Oracle development environment
- From: Kurt Franke <Kurt-Franke@xxxxxx>
- To: oracle-l@xxxxxxxxxxxxx, paulastankus@xxxxxxxxx
- Date: Fri, 28 Dec 2007 15:08:38 +0100
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
