I need to get the expiry date from DBA_USERS for an application on our system.
When I query the view i got no problem, but when i put the query in a function i've got the error ORA-00942: table or view does not exist. The function will be created in a schema called "SISTEMAS"
The question is, we need to execute grant select on the view for the schema sistemas? or there is another solution for this issue?
Thanks for your replies...
The owner of the procedure or package must have been explicitly granted the necessary object privileges for all objects referenced within the body of the code; the owner cannot have obtained required privileges through roles.
You can check if you have access by doing this.
SQL> set role none;
SQL> "statement you want to test to see if it'll work in a function"
Frank / TSharma
In this case it seems that the privileges it's only by role.
I don't have the SYS password, the DBA is an external operator and only answer questions by mail.
So... this is the only way to create the function?
It's the only reasonable way. If the people in charge haven't given you privileges to use the view in a function, then you can't use the view in a function. That's how security is designed to work.
You might be able replicate the relevant parts of DBA_USERS in a table in the sistemas schema. You would have to do this using a SQL command (outside of PL/SQL) or an anonymous block. Of course, the data in that table would become stale (that is, it would reflect the expiry_date as of when the table was populated, not when the function was called).
As suggested earlier, are you only interested in the caller's expiry_date? If so, you could query USER_USERS instead of DBA_USERS. The pseudo-user PUBLIC (almost certainly) already has SELECT privileges on USER_USERS, so you're not depending on any role. You'll need to make the function AUTHID CURRENT_USER.
If sistemas really needs to use DBA_USERS in a function, then the best thing is to wait until sistemas has beed granted the necessary privileges.