Folks as it is very urgent pls get me an idea on this
We have a schema owner called' pv'.and i created a role called 'pvapp' with grant select on xxx to pvapp.
And now i created a user 'pvappserver' with only connect role and also i granted with 'pvapp' role which i created .
Now when i login as pvappserver and try to select from xxx table it returns me with table or view not found.
I think im missing something while creation of role..or ..
could u pls let me know on this ..very urgent.
thnks oracle on 8.1.5 and on solaris 2.7
Did you issue select * from pv.xxx; ? I mean did you mantion schema prefix in your select statement?
one more thing if i give grant to user 'pvappserver' itself it is working..but if thro role its not working..i hope my problem is understood..by now..guys this is very imp..for me..
Do you select data from the table in sqlplus command prompt or using someone stored procedure? I had issues before with grants on objects thru a role in stored procedures. It does not work at all! Only direct grants to a procedure owner works fine.
Here are Few Concerns Regarding Those Roles.
1- Make sure that after assigning the role, User logins, otherwise those Roles are not defaulted to that User, who is already logged in.
2- If your Roles are not Authenticated, They will be defaulted role for the next time the User Log In.
3- There are few things that you can assign through Roles, Like creating a view, you need select Priviliges but this will not work with Roles, so you have to assign this Priv Directly.
I suppose, you have not connecte the user after assigning that Role. Assign That and connect, and make sure that you Use DOT NOTATION... Username.ObjectmName, It should be fine.
Check in tha session, Select * from Session_Privileges... Hope this will help.
I think you're forgetting to make the newly created role a member of the user's default role.
Try this, I'm sure it will solve your problem.
Grant "pvapp" to "pvappserver";
Alter user "pvappserver" default role all;