One Schema with several people using it 2

[florida1987]

In our Oracle 9i Database we have a Project that uses one Schema called ProjectOne where several people need to be able to create tables, create,edit and delete records, create and delete triggers and sequences in the ProjectOne schema.

Our DBA creates a user account for each person so they have a different username and password with priviledges to the ProjectOne Schema. The DBA doesnt want us to use the ProjectOne Schema username and password. Is this a standard practice with Oracle where each person needs their own username and password to login into the Database and view and edit one Schema (ProjectOne)?

 

[Thargy]

flo,

yes and no.

Not allowing users to login to the main schema is usually good practice. For example, if the DBA creates schema BANANA, password BANANA, then this account should be used to create all the items needed for the BANANA application, i.e. tables, triggers, sequences, indexes etc.

Developers who are writing code against the BANANA schema should be allowed to access tables, but never ever be able to issue DML against the schema. Otherwise, how can you know that one developer hasn't created a table, only to have another drop it?

Having multiple user accounts able to access a schema is fine. Allowing these accounts to issue DML is insanity. How on earth does your DBA control alterations to the structure of the schema?

Can you provide more info flo?

Regards

Tharg

Grinding away at things Oracular

 

[florida1987]

Thanks for info.

In this case there are 3 of us building tables and editing records. All 3 of us work together and plan what each person is going to do to the schema. The schema is the back end to be used for Cold Fusion front end web development.

 

[SantaMufasa]

...several people need to be able to create tables, create,edit and delete records, create and delete triggers and sequences in the ProjectOne schema.

Florida, I cannot emphasise enough that it is a dangerous decision to allow your non-DBA users to perform Data Definition (DDL) commands that can alter or destroy application objects (significant information assets) such as CREATE TABLEs, and to CREATE/DROP triggers, and sequences in your production ProjectOne schema. Allowing them to do so destroys order and accountability for your production environment. (It is similar to deputising all drivers on the road to cite other drivers for disobeying traffic rules...not a healthy decision.)

It may be reasonable to give permission to ProjectOne users to perform Data Manipulation (DML) commands such as SELECT, INSERT, UPDATE, and DELETE, which can affect the row and/or column contents (data values) of your application data, instead of their affecting the structures of your application repository. Triggers can audit DML changes, but triggers do not typically audit DDL changes.

Here is the standard practice for allowing access to production tables:

1) CREATE ROLE <role name>;
2) GRANT <role name> TO <user-1>, <user-2>,...<user-n>;
3) GRANT <DML privilege(s)> ON <table name 1> TO <role name>;
(repeat this process on as many ProjectOne tables as appropriate)

This provides quick, easy, approprate access management to your production tables.

Do Not GRANT to application users DDL privileges (indiscriminately) on production objects.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

Sorry for my cross-posting...I was composing my response (and eating my supper) while Tharg was posting his excellent response (at 1:44 a.m. his time...good on ya', Tharg).

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Thargy]

flo,

Santa corrected an erratum in my post. Where I mentioned DML, it should of course have been DDL, as per Santa's post.

Users are supposed to be able to alter data, but NOT the structure of the tables etc.

Although you plan your changes and agree them, this is doomed to eventual failure. What happens when the team grows to a dozen developers, and one is off sick, 2 are on holiday etc. A change is agreed and made, and then someone comes back from holiday, and needs it to be reversed?

When you've lost a man week of effort for the third time, you'll have a deep and intimate understanding of the problems caused by not having a single point of control over db changes.

Can you offer more information to describe your situation, or would you like some recommendations about how to go about this? Santa's post is a good starting point, but you also need to formalise change control, so that you can track, justify from business requirements, audit and reverse changes, as required.

Regards

Tharg

Grinding away at things Oracular

 

[florida1987]

Thanks!