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.
To establish this kind of Hireachy, you have to establish a set of roles before granting them to the users. Say for example, that you have four roles created
In such case you can customize the privileges each role has. That is you could give the dba role to Level1_access, but be aware of the dba role, b'cos you are compromising a lot of things with this role. Insteas you could customize the Level1_access role by selectivly granting some system privileges that are in dba_role. For the Level2_access role and the others you could selectively grant access and then establish a hirachy by including the role of Level4_access in Level3_access and so on backwards.
Hope this would help you to solve the problen that you are having now.