Some queries for user management

[madhuusa]

Hi Nair, All,

We are using sql server 2000 (and debating to move to oracle).

By any chance do you have the query in which we can pull the following info.
a. Given an user name (or id) get all the objects (mainly folders, documents) to which he/she has access and the access level (see, see contents, etc)
b. Given a group id, find all the folders and documents to which the group has permission and the permission level.
c. query or oscript idea to find out whether a user is deleted and if yes then recursively remove him/her from all the livelink objects to which he/she has access.

we have close to 1000 users using the system and one of the main concern was whether doing any of the above will crash the db because of level of querying needed. Is this real.

Madhu.

 

[appnair]

a. Given an user name (or id) get all the objects (mainly folders, documents) to which he/she has access and the access level (see, see contents, etc)
Appnair:Livelink permissions are object based or in other word,you have a dataid,it will be represented in dtreeacl with a corresponding permission bitmask.An id or group does not get to see a object unless put on the object's ACL.You may not have realized this but that is how Windows,unix (ugo) works too.Users have privileges to login,public access ea al.What you could theoretically do or in oscript is find this out you start from a root object,you iterate its chilodren and find the permission set for that.Compare the permission sets with the userid/group and see what they have.I have a sample SQL where I start from a folder and find out the dtreacl (direct permissions) of the children.This unfortunately will not tell you who all can acatually see the object because I am not considering groups within groups.Since the tables are NDA protected,I will not post them in this public forum.

b. Given a group id, find all the folders and documents to which the group has permission and the permission level.
Appnair:See my above answer
c. query or oscript idea to find out whether a user is deleted and if yes then recursively remove him/her from all the livelink objects to which he/she has access.
Appnair:Visit the OT KB or sign and NDA with OT for getting a schema document.To find a user who is deleted is pretty easy,just do a select on an id that you know from kuaf.Save the values some where.Look at coulmns 'type' and 'deleted' especially.Now delete the user and run the select once again.That is how livelink keeps users/groups in a deleted condition.I belive an oscript module can be written to do what you are asking and may be faster also.Maybe I am thinking this theoretically but you may have a situation where a Groups may be the only link between two livelink groups,keeping some user form seeing an object.When your algorithm runs you will remove the group because it consisted of deleted users and the link is now broken.All hell now will break loose at your help desk.This may need to be tested.



Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937

 

[madhuusa]

Hello Nair,

Thanks for your quick reply. I will go thro' the answers and get back in case I have more questions.

Madhu