Home » SQL & PL/SQL » SQL & PL/SQL » procedure fails due to role (11G Linux)
procedure fails due to role [message #423995] Tue, 29 September 2009 17:46 Go to next message
rkl1
Messages: 97
Registered: June 2005
Member
Dear All,
Please forgive me first for not explaining my problem well but here how it goes.

1. Schema_A is the parent schema and got several tables. There is role created which gives the SELECT/insert/update/, create procedure to several developers for this schema. Since the privilege is given by role, the procedure fails when the developer create one which uses a select (cursor) statement for the tables in Schema_A.

To solve this problem: we may give the explicit select, insert etc privilege to the group of users who could then write the procedure. However there are too many of them and it would be doable but not very practical. Or we may create a single shared user and give explicit select ,insert privilege to workout but we don't know the outcome of this sharing experiences (some one may drop procedures, functions etc : who knows).

Is there any other way to work it out ? Any help, advise would be highly appreciated.

Re: procedure fails due to role [message #423996 is a reply to message #423995] Tue, 29 September 2009 17:59 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
If the procedures are owned by SCHEMA_A, no errors would occur.
Re: procedure fails due to role [message #424008 is a reply to message #423995] Tue, 29 September 2009 21:25 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
once again we have a post which says "I get an error" and then fails to actually post the error. I for one know what error you are talking about, why you are getting it, and how to deal with it as apparently so do you, but others may not. If you expect to get help from these forums you will need to do more work on your part for this forum and its users. We like helping but we demand a minimum amount of information sharing for the benefit of all who read these posts and you have failed us on that point.

Let me show you what you should have posted.

SQL> grant connect to user1 identified by user1;

Grant succeeded.

SQL> grant dba to data1 identified by data1;

Grant succeeded.

SQL> connect data1/data1
Connected.
SQL> create table a (a number);

Table created.

SQL> create role role_data1;

Role created.

SQL> grant select,insert,update,delete on a to role_data1;

Grant succeeded.

SQL> grant create procedure to role_data1;

Grant succeeded.

SQL> grant role_data1 to user1;

Grant succeeded.

SQL> connect user1/user1
Connected.
SQL> select * from data1.a;

no rows selected

SQL> create or replace procedure p1
  2  as
  3     cursor c1 is select * from data1.a;
  4  begin null;
  5  end;
  6  /

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE P1:

LINE/COL ERROR
-------- --------------------------------------------------------
3/17     PL/SQL: SQL Statement ignored
3/37     PL/SQL: ORA-00942: table or view does not exist

SQL> declare
  2     cursor c1 is select * from data1.a;
  3  begin null;
  4  end;
  5  /

PL/SQL procedure successfully completed.

SQL> connect data1/data1
Connected.
SQL> grant select,insert,update,delete on a to user1;

Grant succeeded.

SQL> connect user1/user1
Connected.
SQL> create or replace procedure p1
  2  as
  3     cursor c1 is select * from data1.a;
  4  begin null;
  5  end;
  6  /

Procedure created.


Hmm... the user can select from the table via sqlplus, can use the table in an anonymous plsql block, but cannot compile the plsql code as a procedure. This is the classic privileges via a role issue. But after granting direct privileges on the object to the user, the procedure will compile. This is the correct behavior and is done so for security and correctness reasons.

BlackSwan, correct as usual also points out an alternative:

SQL> connect data1/data1
Connected.
SQL> revoke select,insert,update,delete on a from user1;

Revoke succeeded.

SQL> grant create any procedure to user1;

Grant succeeded.

SQL> connect user1/user1
Connected.
SQL> create or replace procedure data1.p1
  2  as
  3     cursor c1 is select * from data1.a;
  4  begin null;
  5  end;
  6  /

Procedure created.


This works too, but is not recommended. The recipient of create any procedure can do just that which can lead to nasty mistakes.

My recommendation: do the specific grants on specific objects to specific developers. It is part of the job of a DBA to manage this. Set up a process for it (forms with the right authorizations) so you can maintain the paper trail, and stick to it.

Next time you want help, make sure you create a working test case that is reproducable for people to study. If you make reference to errors, show the error via sqlplus.

The answer to your problem depends. It depends upon what you want. The reason for having different user IDS is so that you can track what each ID does. If you do not care about which user ID does what then you don't need multiple IDs. The issue of multiple developers in the same schema is nothing new. All of use deal with it every day.

I suggest you talk to your corporate security team/division/group and ask them what you should do. Use of multiple schemas is often a security issue promoted by security areas. They should be able to tell you what your system architectures should look like from development->qa->integration->manufacturing->production. They should be able to tell you how much of this progression you need, how to implement it, and how to manage multiple developers.

Good luck, Kevin
Re: procedure fails due to role [message #424047 is a reply to message #424008] Wed, 30 September 2009 01:23 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I don't think I agree with Kevin here, but I am on BlackSwan's wagon. Since the final product will be owned by a single schema-owner, all developers should do their coding as that schema-owner.
In my opinion it does not make sense if I develop a procedure in my own schema, where it will be owned by another schema once it goes into AcceptanceTest / Production.
Re: procedure fails due to role [message #424114 is a reply to message #423995] Wed, 30 September 2009 04:55 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Obviously Frank did not understand what I said or he would have agreed with me. I never actually made any recommendation on which way to go.

I said:

1) ask your security team how they want you to handle multiple developers in the same database.

Almost invariably the answer will be "each developer must have their own schema in which they do their own work". Indeed, after Sarbanes-Oxley Act of 2002 and other Acts of Congress, many companies (financial institutions in particular) have paid more attention to full spectrum security. Most interpret these Laws to include requirements for audit trails of how code moves from development to production. This is hard to do without independent schemas.

2) don't give system privileges out like candy.

Let me ask you Frank, do you give CREATE ANY PROCEDURE to develoers in your environments?

In the end one schema will be required; a place where all develpment must come together for "DEVELOPER'S INTEGRATION EVENTS". But that is different from keeping developers from stomping all over themselves.

This is why I advised seeking direction from control groups like security teams to whom the responsibility falls to see that development areas follow certain rules. Clearly how multiple developers in the same database should operate falls within this realm.

Kevin
Re: procedure fails due to role [message #424251 is a reply to message #424114] Thu, 01 October 2009 02:01 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The laws you refer to are US laws (if I am correct) and do not apply to other parts of the world.
The vast majority of projects I have worked on so far all used the same way of working: all developers use the owner-account during development. In fact, I cannot recall a single project where this was not the case.
Each developer however has his/her own account in whatever SourceVersion control tool used, so it is possible to track changes made per developer.
Of course this only goes for dev-environments, not prod.
Re: procedure fails due to role [message #424323 is a reply to message #423995] Thu, 01 October 2009 09:54 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Yes you are correct, these laws apply to any business doing business in the US. You want to work or sell your products or services in the US, you musy comply with these laws, particularly where you maintain financial data or personnaly identifiable data.

As your post shows, different places do it differently. Thus the reason to consult those local to yourself, to whom it falls the responsibility for this decision.

Kevin
Previous Topic: Can this be possible through sql ?
Next Topic: Accent insensitive comparing problem
Goto Forum:
  


Current Time: Tue Dec 06 12:29:18 CST 2016

Total time taken to generate the page: 0.08584 seconds