Results 1 to 14 of 14
  1. #1
    Join Date
    Jul 2015
    Posts
    7

    Unanswered: grant compile to other users package gives insufficient privilages

    I have 2 users..U1 and U2.......... U1 contains some packages... I want U2 to compile U1 packages....... I have given Execute and debug grants to U2 but it says insufficient privilages and I can not give 'alter any procedure' grant as it will have access to compile any package which will be security issue.Do anyone have perfect solution on this? as i have tried almost all suggestions mentioned in the blogs.

    Here, the other side of my requirement 'why owner should not compile procedure' is



    We have U1 (example) user/schema which has all objects(table/package/proc/function) and currently everyone is logging in with U1 and doing their tasks.

    So this make complete sense by logging in as owner and compile for any changes in that proc.

    But the actual reason is we have audit requirements where every user should login by their own userid say U2/U3/U4/%...so on



    Now those all users wants to access U1 users objects (table/package/proc/function)



    So even though they are doing same task what they were doing earlier as owner ....now they are doing as separate users...so everything on their account is audited.

    I know in oracle we do have many options to audit by tracing machine/terminal name even though there is only one user but still this is our audit requirement.



    So any changes in table or proc may need to compile that object and every separate user should do that.

    As UTLRP will do all DB objects compile which is not needed.



    And at the same time tose users should not have access to any other schema objects too apart from U1

    Please suggest...!!

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down This a no-no

    Quote Originally Posted by banteswapnil View Post
    I have 2 users..U1 and U2.......... U1
    . . .
    But the actual reason is we have audit requirements where every user should login by their own userid say U2/U3/U4/%...so on

    Now those all users wants to access U1 users objects (table/package/proc/function)

    So even though they are doing same task what they were doing earlier as owner ....now they are doing as separate users...so everything on their account is audited.
    . . . e t c
    So any changes in table or proc may need to compile that object and every separate user should do that.
    . . . e t c
    As UTLRP will do all DB objects compile which is not needed.

    Please suggest...!!
    You are wrong or your deployment procedures are wrong!
    No users/developers should touch production code, the correct procedure is all fixes/changes/new code begins in the development environment, moves to QA and after acceptance is deployed to production by authorized persons only.
    Users/Developers should only have (if necessary) the ability to just read the code. not change it.
    The only grant they may need is: SELECT ANY DICTIONARY and perhaps DEBUG ANY PROCEDURE.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Jul 2015
    Posts
    7
    Quote Originally Posted by LKBrwn_DBA View Post
    You are wrong or your deployment procedures are wrong!
    No users/developers should touch production code, the correct procedure is all fixes/changes/new code begins in the development environment, moves to QA and after acceptance is deployed to production by authorized persons only.
    Users/Developers should only have (if necessary) the ability to just read the code. not change it.
    The only grant they may need is: SELECT ANY DICTIONARY and perhaps DEBUG ANY PROCEDURE.


    Hello...Thank you for your response on this.
    Just to let you know this is not a production environment...This is development.

    The requirement is for audit purpose.

    Currently everyone is using only one schema from their machine and in our organization they don't want same schema should be used by many people.

    So we are creating many individual schemas who can touch base schema and do the work which they were doing.

    As I said 'Any Dictionary will give grants to edit other objects also in database other than base schema ...so this is not safe approach and debug will not allow compile other users package.

    Let me know if you have anything for this.

    I have raised same question in other forum as well.

    https://community.oracle.com/thread/3758566

    You may get some idea through it.

  5. #5
    Join Date
    Jul 2015
    Posts
    7
    Quote Originally Posted by anacedent View Post
    Hello Sir,

    This thread is opened by me only in oracle community ...but I haven't came to any perfect solution for this so far.
    Let me know if you have anything for this.

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    The requirement is for audit purpose.
    And what is the purpose of auditing development?
    Just to add restrictions and make life more difficult for the programmers?

    1) You may need to put all code into a source(version)-control manager utility.
    2) Developers check out the module, change/test and compile in their schema.
    3) Module is check-in and moved to QA for acceptance.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  7. #7
    Join Date
    Jul 2015
    Posts
    7
    Quote Originally Posted by LKBrwn_DBA View Post
    And what is the purpose of auditing development?
    Just to add restrictions and make life more difficult for the programmers?

    1) You may need to put all code into a source(version)-control manager utility.
    2) Developers check out the module, change/test and compile in their schema.
    3) Module is check-in and moved to QA for acceptance.
    In fact... even I had the same question...'what is purpose of auditing development?'
    But it's clients requirement and it can not be changed ...so need to work on..out of standard way but the real requirement.
    Also this suggestion was good...but in that case it will be every time DBA has to deploy their edited code in actual schema especially on weekends.

    With our requirement we are giving them authority to do task on their own even when they are working from home or weekends.
    Last edited by banteswapnil; 07-21-15 at 14:32.

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Grant the ability to select,update,delete,insert on all the tables in the application to a ROLE. Then assign the ROLE to the individual users.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    Jul 2015
    Posts
    7
    Quote Originally Posted by beilstwh View Post
    Grant the ability to select,update,delete,insert on all the tables in the application to a ROLE. Then assign the ROLE to the individual users.
    Hi,

    I think you have not read the requirement completely.
    My requirement is more on related to procs/packages/functions
    You might get idea about the requirement mentioned in link in above update.
    Anyways thanks for taking a look in this.

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    have schema U1 do as below
    CREATE OR REPLACE PROCEDURE COMPILE_ME
    IS
    BEGIN
    dbms_utility.compile_schema(USER);
    END;
    /
    GRANT EXECUTE ON COMPILE_ME TO U2;
    now U2 can compile every procedure owned by U1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  11. #11
    Join Date
    Jul 2015
    Posts
    7
    Quote Originally Posted by anacedent View Post
    have schema U1 do as below
    CREATE OR REPLACE PROCEDURE COMPILE_ME
    IS
    BEGIN
    dbms_utility.compile_schema(USER);
    END;
    /
    GRANT EXECUTE ON COMPILE_ME TO U2;
    now U2 can compile every procedure owned by U1


    I tried this..by U1 user I created proc

    SQL> CREATE OR REPLACE PROCEDURE COMPILE_ME
    2 IS
    3 BEGIN
    4 dbms_utility.compile_schema(USER);
    5 END;
    6 /

    Procedure created.

    SQL> GRANT EXECUTE ON COMPILE_ME TO U2;

    Grant succeeded.

    and then by U2 tried to compile:

    SQL> alter package u1.test_pkg compile package;
    alter package u1.test_pkg compile package
    *
    ERROR at line 1:
    ORA-01031: insufficient privileges

  12. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    How sad.
    You can lead some folks to knowledge, but you can't make them think.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  13. #13
    Join Date
    Jul 2015
    Posts
    7
    Quote Originally Posted by anacedent View Post
    How sad.
    You can lead some folks to knowledge, but you can't make them think.
    Yes it is.

    Also it doesn't satisfies the requirement of compiling only one package at a time.
    compile_schema will allow to compile schema .... It may have risk to get other objects damaged(invalid)

    Instead this will allow me to execute single package name which I provide in proc:


    CREATE PROCEDURE COMPILE_PROC(pkg_name VARCHAR2)
    IS
    BEGIN
    EXECUTE IMMEDIATE 'ALTER PACKAGE '||pkg_name|| ' COMPILE PACKAGE';
    END;

    but when I run this thru U2 user and in case any error is there it wont show in 'show err' command in u2 user

  14. #14
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >but when I run this thru U2 user and in case any error is there it wont show in 'show err' command in u2 user

    create another procedure SHOW_ERRORS owned by U1 that does SELECT * FROM USER_ERRORS;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •