This discussion is archived
12 Replies Latest reply: Jun 13, 2012 1:12 PM by user10160672 RSS

OWM security setup different in 11g than 10g?

user10160672 Newbie
Currently Being Moderated
Hi there,

In 11.2.0.3 I'm encountering errors trying to grant workspace privs through package. For example, user SHACHE is the owner of workspace '104149843', the following grant works running as user SHACHE:

EXEC dbms_wm.GrantWorkspacePriv('ACCESS_WORKSPACE,MERGE_WORKSPACE,CREATE_WORKSPACE,REMOVE_WORKSPACE,ROLLBACK_WORKSPACE,FREEZE_WORKSPACE','104149843','AILXU','NO',FALSE);

However if the above grant is coded in a procedure/package:

CREATE OR REPLACE PROCEDURE slimadm.test_revsetgrant
IS
begin
dbms_wm.GrantWorkspacePriv('ACCESS_WORKSPACE,MERGE_WORKSPACE,CREATE_WORKSPACE,REMOVE_WORKSPACE,ROLLBACK_WORKSPACE,FREEZE_WORKSPACE','104149843','AILXU','NO',FALSE);
end;

grant execute on slimadm.test_revsetgrant to shache;

I get error trying to run this with user SHACHE:
exec slimadm.test_revsetgrant;

Error message:

[Error] Execution (30: 1): ORA-20076: insufficient privileges to grant ACCESS_WORKSPACE
ORA-06512: at "WMSYS.LT", line 10116
ORA-06512: at "SLIMADM.TEST_REVSETGRANT", line 4
ORA-06512: at line 1


The above testing works fine in 10.2.0.4.

Does OWM change security setup? Please advice.

Thanks,
Sharon
  • 1. Re: OWM security setup different in 11g than 10g?
    user10160672 Newbie
    Currently Being Moderated
    I made a mistake in the above example. I should have used AUTHID CURRENT_USER to define the procedure so that the grantor is the workspace owner "SHACHE". Although because because the procedure owner "SLIMADM" has WM_ADMIN_ROLE so even the procedure runs under SLIMADM it still works in 10g.

    I verify the role grants. I do have the same setup in 11g (just finished a in place upgrade) - means I should be able to do workspace grant with either SHACHE, who is the owner of the workspace, or SLIMADM, who is WM_ADMIN_ROLE.

    Any idea? Any help will be very appreciated!!

    Sharon
  • 2. Re: OWM security setup different in 11g than 10g?
    Ben Speckhard Pro
    Currently Being Moderated
    There was a fix so that executed procedures are run with the appropriate privileges. For current_user procedures, it is run as the user executing the procedure. And for definer's rights procedures, it is run as the owner of the procedure. Essentially, as would be expected. Previously, it was run with the privileges of the executing user, regardless of the type of procedure/function. When I try your example, it works fine with current_user, but there is a error when it is defined as definer's rights. This error is to be expected based on the change in behavior.

    Also, remember that roles are not enabled within executed procedures as is typical for the database.

    Regards,
    Ben
  • 3. Re: OWM security setup different in 11g than 10g?
    user10160672 Newbie
    Currently Being Moderated
    Thank you very much Ben. Your answer was what I was suspecting from my own tests.

    You mentioned there was a fix for the appropriate privileges when executes procedure, do you know where I can find the detail document about this fix?

    This fix is a huge impact to our current system because we have tons of triggers to call packages to perform some actions. Currently in 10g all these actions are run as the end user who issues the call. This fix will change the whole behavior of our system because the actions will be run as the trigger owners... More details are great!

    Thanks again Ben!

    Sharon
  • 4. Re: OWM security setup different in 11g than 10g?
    user10160672 Newbie
    Currently Being Moderated
    Hi Ben,

    I'm still not clear about how this works.

    I did more tests with the above example.
    -- User1 SHACHE is the owner of workspace
    -- User2 SLIMADM is the owner of package doing the grant. SLIMADM has WM_ADMIN_ROLE, and direct grant by :
    exec dbms_wm.grantsystempriv('ACCESS_ANY_WORKSPACE,CREATE_ANY_WORKSPACE,MERGE_ANY_WORKSPACE, ROLLBACK_ANY_WORKSPACE, REMOVE_ANY_WORKSPACE,ROLLBACK_ANY_WORKSPACE,FREEZE_ANY_WORKSPACE','SLIMADM','YES',TRUE);

    Verify the grants:
    SELECT * FROM WMSYS.WM$WORKSPACE_PRIV_TABLE where grantee IN ('SLIMADM');
    GRANTEE GRANTOR PRIV ADMIN
    SLIMADM          GDOSYS     AA     1
    SLIMADM          GDOSYS     MA     1
    SLIMADM          GDOSYS     CA     1
    SLIMADM          GDOSYS     RA     1
    SLIMADM          GDOSYS     DA     1
    SLIMADM          GDOSYS     FA     1

    -- If the package is defined as AUTHID CURRENT_USER then both USER1 and USER2 can do the grant successfully
    -- If the package is defined as definer, then neither user can do the grant through the package.


    So what grants do I need to do to SLIMADM to make the package works as definer?


    In my system the package is defined as AUTHID CURRENT_USER. However we have a middle layer in between the workspace owner and the package, so the package is run under the 3rd user.
    1. User 1 create a workspace
    2. A row insert into a metadata table in User 2. A trigger is setup on this table to call a package to share workspace with people who is in the same role
    3. So the package is run as User 2 but inside a trigger. With WM_ADMIN_ROLE and all direct grant same as the above sample, User2 failed to call a package defined as AUTHID CURRENT_USER to do the grant.

    This works in 10.2.0.4. It seems to me the fix was made to OWM security control.

    Help is very greatly appreciated.

    Thanks,
    Sharon


    Authid current user then Still using , I need to do the grant with SLIMADM, instead of the owner of the workspace.
  • 5. Re: OWM security setup different in 11g than 10g?
    Ben Speckhard Pro
    Currently Being Moderated
    Hi Sharon,

    There has been at least one bug fix related to this, that you may to be running into. The definer's rights procedure should work as slimadm has the ANY_* privileges. WM_ADMIN_ROLE is irrelevant as roles are not active within DR procedures. Previously, the majority of the views were using USER, and disregarding the definer's rights/current user setting of the procedure. I would suggest filing an SR in order to verify if you are running into the same issue. Also, there isn't a document related to this bug fix.

    Regards,
    Ben
  • 6. Re: OWM security setup different in 11g than 10g?
    user10160672 Newbie
    Currently Being Moderated
    Thanks Ben for the reply!!

    Yes I'm about to file a SR to Oracle. I was hoping that I could get answer from this helpful forum :-)

    I do grant slimadm the ANY_* privileges but it didn't help...

    Sharon
  • 7. Re: OWM security setup different in 11g than 10g?
    Ben Speckhard Pro
    Currently Being Moderated
    Hi Sharon,

    I took a closer look at this, and the errors when executing the definer's rights procedure are expected. Since WM_ADMIN_ROLE is not active during a DR procedure, and SLIMADM does not have the ACCESS_WORKSPACE privilege on the workspace with the grant_option, the grant cannot be performed. The existence of the ACCESS_ANY_WORKSPACE privilege does not explicitly grant the ability for the user to grant ACCESS_WORKSPACE on a specific workspace. Similar to how a user with the CREATE ANY TABLE privilege cannot grant CREATE TABLE.

    So, SLIMADM needs to be granted the privileges explicitly when being done from a DR procedure.

    Regards,
    Ben
  • 8. Re: OWM security setup different in 11g than 10g?
    user10160672 Newbie
    Currently Being Moderated
    Hi Ben,

    Thank you very much for follow up with this thread. Your last response makes perfectly sense. So a workspace is considered as any other database objects that you need object level grants to access.

    As I mentioned in this thread:
    In my system the package is defined as AUTHID CURRENT_USER. However we have a middle layer in between the workspace owner and the package, so the package is run under the 3rd user.
    1. User 1 create a workspace
    2. A row insert into a metadata table in User 2. A trigger is setup on this table to call a package to share workspace with people who is in the same role
    3. So the package is run as User 2 but inside a trigger. With WM_ADMIN_ROLE and all direct grant same as the above sample, User2 failed to call a package defined as AUTHID CURRENT_USER to do the grant.

    Since Triggers are always run as DR, so I have no work around to make this work... I don't have a place to run this grant package as current user.

    Talking about triggers running as DR, I somehow think it can cause security leak. User1 has insert permission to User2.Table1 only, nothing else under User2. But the trigger on User2 can do anything inside the schema of User2 because it runs as the schema owner. It's not making too much sense to me.

    Again, can't thank you more for the reply. If you have a work around idea for my case it will be greatly appreciated!!!

    Sharon
  • 9. Re: OWM security setup different in 11g than 10g?
    Ben Speckhard Pro
    Currently Being Moderated
    Hi,

    Would it be possible for user1 to grant the privileges directly to user2 with grant option? I was unsure how the insert into the user2 metadata table was initiated? It seems to be a discrete step after the creation of the workspace and not somehow triggered by the creation of the workspace? If so, you could just slide the grant between the 2 steps. Otherwise, you would need to create a current_user procedure in user2 and execute that directly instead of relying on the trigger.

    Regards,
    Ben
  • 10. Re: OWM security setup different in 11g than 10g?
    user10160672 Newbie
    Currently Being Moderated
    Hi Ben,

    We use GeoMedia, so when the user create a workspace it has to be through GeoMedia's interface, where I have no way to plug code in to do the grant as current user. When GeoMedia creates a workspace it adds the info about this workspace plus a whole bunch of other info could be used for the product later to a metadata table, so the grant code was plugged in to this table's trigger. I've been trying to find another place to plug the code in but their packages are wrapped so I can't. It seems there's no other way...

    Sharon
  • 11. Re: OWM security setup different in 11g than 10g?
    Ben Speckhard Pro
    Currently Being Moderated
    Sharon,

    Can this be done asynchronously? If so, one option might be to use the event framework within Workspace manager. You could register a callback for the CREATE_WORKSPACE event. Otherwise, you would need to create a procedure in the schema that created the workspace and execute that from either the trigger or the package in the slimadm schema.

    Regards,
    Ben
  • 12. Re: OWM security setup different in 11g than 10g?
    user10160672 Newbie
    Currently Being Moderated
    Hi Ben,

    I provided two test cases to the SR for this OWM security setting change. It's a bug that when the procedure/package is created as AUTHID DEFINER then it fails to grant OWM permission, no matter the definer is the owner of the workspace or have any other system permission.

    I had a work around by submitting a job under the current user, which is the owner of the workspace, also use dbms_ijob.change_env to force to use log_user and priv_user to be the owner of the workspace, then the grant works.

    Thanks for your help all along!

    Sharon

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points