Clarity

  • 1.  checking instance rights

    Posted Apr 15, 2011 10:37 AM
    I have been told we have too many users with too many instance rights, which will cause a huge performance issue.

    I would like to be able to find all the users with excessive instance rights. Is there anyone out there that would know if a query or something can be written to find this information. Maybe there is something inside Clarity that will show me this information.

    I am using Clarity 8.1.3 on Unix with an Oracle DB.


  • 2.  RE: checking instance rights

    Posted Apr 15, 2011 10:46 AM
    You can find out all the access rights on the database - the complex rights can be somewhat tricky to spot, but "instance" rights are fairly simple.

    There have been a number of threads on the boards concerning this so have a search around and you should find the SQL!


  • 3.  RE: checking instance rights

    Posted Apr 15, 2011 10:47 AM
    This is a query from one of the post I saved. Kudos to Senthil on these queries


  • 4.  RE: checking instance rights

    Posted Apr 15, 2011 11:38 AM
    Yep searched for instance rights and came up with all kinds of SQL samples. I don't know enough to know what to do with it.

    I tried to run the code in the "rights query.txt" file. The whole thing prints out as output with "Error at line 66
    ORA-00920: invalid relational operator" at the end.

    I am guessing all the samples given have to be customized to the given environment. Or a person has to have some kind of idea what they are looking at.


  • 5.  RE: checking instance rights

    Posted Apr 15, 2011 11:58 AM
    It was pretty tricky for me too so I had to get help to break it down little by little and not run the entire code


  • 6.  RE: checking instance rights
    Best Answer

    Posted Apr 15, 2011 12:02 PM
    Give this a try... just replace your users internal ID where is calls for the "principalID".

    --Kathy
    -----------------------------

    --Resource Instance Rights
    SELECT DISTINCT a.right_id, g.right_type, g.description description, o.name right_type_name,
    g.group_name right_name, g.group_code, g.is_automatic
    FROM cmn_sec_assgnd_obj_perm a, cmn_sec_groups_v g, odf_objects_v o
    WHERE a.principal_id = 5000108 --resource internal ID
    AND a.principal_type = 'USER'
    AND a.right_id = g.id
    AND g.right_type = o.right_code
    AND g.language_code = 'en'
    AND o.language_code = 'en'
    AND g.is_active=1
    ORDER BY right_name


  • 7.  RE: checking instance rights

    Posted Apr 15, 2011 12:03 PM
    You are the woman Kathy!! Thank you


  • 8.  RE: checking instance rights

    Posted Apr 15, 2011 02:13 PM
    Hi,

    The Rights access query (senthil one) is fine.It is just missing the equal to sign in an sql.i have changed and added.you can use it to get the access rights .(Group,Instance,OBS,Global,Auto Rights).

    cheers,
    sundar


  • 9.  RE: checking instance rights

    Posted Apr 15, 2011 04:15 PM
    Thank you Sundar!