please check the question

[srinivasm]

I have passed a question in the previous thread as follows

This is my question

i want to create a user and then under that user i have to create some other users and again under each user i have to create some other users .....like that hierarchical structure.

I am able to create this structure. Lets think there are four levels in the hierarchy. Then the information of the 1st level is not accessible for the rest 3 levels. And the information about the 2nd level user is not accessible for 3rd , 4th level users and the 3rd level is not accessible by 4th level.. like taht. This is the way i have to maintain the hierarchy.
But the problem is 4th level user is able to see the information of 1st level and same for the other levels also.
I think i faced this problem is because i granted dba role for 1st level. then only the 1st level user can create the users under him. If the user under 1st level user i.e. 2nd level user wants to create users under him. Then i have to grant the dba role to him also, if not he is unable to create users under him.
That's where the problem comes into picture. If i grant dba roles for each level , i can't maintain the security. The 4 th level user also can see the information of 1 level user.

so please anybody answer to myquestion that , can a user create another user without dba role ? if it is possible please give some information to me please.

SrinivasM


kishore kumar has answered to my question as follows


After the creating first user. Test1. By being in the system , grant him GRANT CREATE USER TO TEST1;

After granting that, connect as test1 and Create user test2... would work. However you cannot grant connect, resource to TEST2. So connect again as system and grant test2 the connect and resource. Good thing about this is, your hierarchical thing works. You can query the table of test2 by logging as test1 and cannot query the contents of test1 when logging as test2 and the process continues to test3 , test4 ..

Hope this helps...
__________________
Thanks
Kishore Kumar


Thankyou Kishorekumar for response, if possible please answer to this.


And then i tried exactly with what kishore
said. ok its working but again some problem. That is TEST1
information is not accessible by TEST2 ,its ok fine. TEST1 should be able to see the information of TEST2. But it is not able to. why it is not working ?
for that which type of privilege to be granted to TEST1?


I am using oracle8i on windowsNT

SrinivasM

[sreddy]

To my understanding all you have given to test2 as test1 is 'CREATE USER' sytem privilege. until you grant the object privileges of test1, test2 can't see the contents/objects of test2.

Take practical example in day to day life at work, system user will not be having object privileges of schema/application owner unless otherwise you give explicitely...

So, test1,test2 setup also works the same way. You are talking about only system privileges, you forgot here object privileges. If you have enterprise manager go and see how the privileges set up for those 2 users you have created. Otherwise try to query the privileges of test1 and test2 and see, you get the clear picture. Correct me If Iam wrong...