And while you at that, why not simpl grant him DBA privilege? And perhaps SYSDBA on top of that? Or, to make it even more simple, why not simply all your user know the SYS password and let them all allways connect as SYS?
Granting select any table to a user doesn't mean one is giving all the DBA privileges to a user. One is giving just read only privilege on the v$tables.
Developer need to check how many cursors are opened by his application while running, one way is to access the corresponding v$tables to close the cursors that are open. Otherwise everytime he/she has to request the DBA to provide the respective information.
Its entirely depends upon the situation and request.
shandj, granting 'select any table' to all developers will greatly compromise security if you or your organisations have 'secure' tables, ie. database tables you don't want everyone to see. If you don't have that problem of security, go ahead. But also think of the future/ future DBAs in your organisation.
The very concept of privileges in Oracle is to provide 'granular' security, ie provide only those privileges which are absolutely essential for the user.
Hence, in this case, 'select catalog role' or 'select any dictionary' is the right choice.
Originally posted by shandj Granting select any table to a user doesn't mean one is giving all the DBA privileges to a user.
Obviously I wasn't sarcastic enough in my previous reply. Sorry about that.
It is more than evident that your proposed solution gives much, much, much wider privileges to the end user than original poster was asking for. The original question was very simple: given the user's existing privileges, what do I have to do to enable him access to V$ and DBA_* family of views. In this aspect, your proposal was way to generous. By using your suggestion, the user will have select permition in any peace of data written into the database. I doubt this is what original poster was looking for.
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Originally posted by nagarjuna Why nobody is considering my solution???
I think the reason that no one is considering your solution is that it turns off any new security added after 7.3.4. Another solution might be to have the users spend more time looking at the user views. If they can get what they want with a user view then they already have that privilege they need. If someone had a good reason to do a select on a v$ view then I would do an explicit grant on the object they need for that user. However, I don't know why a non-privileged user would have a need to look at any sys object.
Originally posted by jmodic And while you at that, why not simpl grant him DBA privilege? And perhaps SYSDBA on top of that? Or, to make it even more simple, why not simply all your user know the SYS password and let them all allways connect as SYS? You know, as DBA you'll have far less troubles with user managenment this way!
If you really want to give users access to a database why don't you just grant sysdba to the public role.
Originally posted by nagarjuna grant SELECT_CATALOG_ROLE to user****;
and also set the folowing init.ora parameter to true.
Stupid solution again
We dont need to set O7_DICTIONARY_ACCESSIBILITY to true when we give SELECT_CATALOG_ROLE to user to access v$ and dba_ views. We have to set O7_DICTIONARY_ACCESSIBILITY to true only when we give select privileges on v$ and dba_ views individually.. Stupid solution again