I wish I had known this 2 years ago
Karl Veselic, August 21, 2004 - 6:11 pm UTC
I spent hours coming up with my workaround, when
all I needed to know was to
grant select on v_$session to whomever
But it's still not clear to me why
grant select any table to whomever
isn't more powerful than your grant.
Thanks!
Karl
By the way, your script is wonderful; I shall
make much use of it.
August 21, 2004 - 8:15 pm UTC
because SYS owned objects are protected "especially well" by default in 9i for security.
grant select any dictionary to my_user ;
pasko, August 23, 2004 - 3:50 am UTC
Hi Tom,
could we also use this to grant select on V$_* Views ?
"grant select any dictionary to .....; "
August 23, 2004 - 7:57 am UTC
a@ORA9IR2> @connect "/ as sysdba"
a@ORA9IR2> set termout off
sys@ORA9IR2> set termout on
sys@ORA9IR2>
sys@ORA9IR2> drop user a cascade;
User dropped.
sys@ORA9IR2>
sys@ORA9IR2> create user a identified by a;
User created.
sys@ORA9IR2>
sys@ORA9IR2> grant create session to a;
Grant succeeded.
sys@ORA9IR2> grant select any dictionary to a;
Grant succeeded.
sys@ORA9IR2> grant create procedure to a;
Grant succeeded.
sys@ORA9IR2>
sys@ORA9IR2> @connect a/a
sys@ORA9IR2> set termout off
a@ORA9IR2> set termout on
a@ORA9IR2>
a@ORA9IR2> create or replace procedure p
2 as
3 begin
4 for x in ( select * from v$session ) loop null; end loop;
5 end;
6 /
Procedure created.
a@ORA9IR2>
good idea, shorter than my approach :)
select_catalog_role
Arun Gupta, August 23, 2004 - 8:53 am UTC
Tom,
What is the difference between select_catalog_role and select any dictionary system privilege? I read the Metalink note but could not figure out the difference.
Thanks.
August 23, 2004 - 9:00 am UTC
select catalog role is a role, roles are not enabled during the compilation of plsql/views and are not ever enabled during the execution of definers rights procedures
</code>
http://asktom.oracle.com/Misc/RolesAndProcedures.html <code>
so, select catalog role would let you write queries in sqlplus for example, but not create a procedure (as select any dictionary, the system privilege, does)
A reader, September 15, 2004 - 10:45 am UTC
Prasad, November 29, 2004 - 1:03 am UTC
A reader, January 17, 2005 - 4:02 pm UTC
v$session - any security risk?
Tim, March 17, 2005 - 1:35 pm UTC
Our DBA is refusing to grant our application's Oracle username access to v$session. He is citing security as a reason. (Reason for request is to see what the program/module initiating the database session is. We know this is easy to spoof - but it helps a little bit until we can go to something secure such as proxy authentication.)
I have suggested creating a view in a schema which does have a direct grant to v$session and only granting our application user a grant to this new view.
However, this was also called a security risk. Do you have thoughts on what security risks this would cause (if any) and if so, can this information be obtained in another manner which would not be a security risk?
This is on an Oracle 9iR2 database.
The view which I suggested is as follows:
CREATE OR REPLACE VIEW v_session_info (
audsid,
process,
program,
module,
logon_time)
AS
select
audsid,
process,
program,
module,
logon_time
from v$session
where sys_context('userenv','sessionid') = audsid;
/
Thanks.
March 17, 2005 - 2:04 pm UTC
i would use a where clause of
where sid = (select sid from v$mystat where rownum=1)
myself, but tell the dba unless you get this view, you HAVE a security risk and if he cannot specify what the security risk involved in letting you see your sessions record is, you'll just have to tell you management chain that "no, we cannot do what you asked for the dba won't help us accomplish the goal you gave us"
select any dictionary...
Kashif, June 17, 2005 - 5:09 pm UTC
Hey Tom -
Isn't this system privilege a tad too powerful? I mean it essentially circumvents the security provided by O7_DICTIONARY_ACCESSIBILITY, plus if you just needed access to a finite number of V$ views (like the original poster did) then wouldn't it be better to simply grant privileges on those views individually (as you had originally suggested)? I'm trying to provide some developers with similar privileges for some views they need to create on top of some V$ views, and was wondering what the best approach was. Thanks for any feedback.
Kashif
June 17, 2005 - 5:11 pm UTC
not all of the security provided. (no access to sys.link$ for example)
It is safe for development environment where the developers should be able to see all of the DBA views, the V$ views.
Hmm.
Kashif, June 17, 2005 - 5:31 pm UTC
Thanks Tom. I don't think O7_DICTIONARY_ACCESSIBILITY provides access to sys.link$ either, though I'm itching to get out of here and won't be able to test it till later, it's way too nice to be indoors... ;)
Anyway, I think I still prefer the grant-as-you-need method of granting access to the data dictionary, select any dictionary seems more than what is required, at least in this scenario.
Have a good one.
Kashif
June 17, 2005 - 6:53 pm UTC
it doesn't - my point was select any dictionary won't either.
select on few data dictionary views ora-01031
pjp, May 18, 2006 - 2:44 am UTC
Hi Tom,
When I am trying to give grant "select" on few data dictionary views I am getting following error
SQL> grant select on v$session to parag;
grant select on v$session to parag;
*
ERROR at line 1:
ORA-01031: insufficient privileges
How can I do this ? I do not want to give "select any dictionary" priv.
This question is for my learning only.
thanks & regards
pjp
May 19, 2006 - 9:03 am UTC
you don't have the privilege to grant select on that object is what that means.
you do not say who you are logged in as, nor the version, nor much of any sort of detail.
ops$tkyte@ORA10GR2> grant select on v$session to scott;
grant select on v$session to scott
*
ERROR at line 1:
ORA-01031: insufficient privileges
ops$tkyte@ORA10GR2> connect / as sysdba;
Connected.
sys@ORA10GR2> grant select on v_$session to ops$tkyte with grant option;
Grant succeeded.
sys@ORA10GR2> connect /
Connected.
ops$tkyte@ORA10GR2> grant select on v$session to scott;
Grant succeeded.
something different?
Mark Wooldridge, July 31, 2006 - 6:08 pm UTC
1* grant select on v$session to hdfs
SQL> /
grant select on v$session to hdfs
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views
SQL> select *
2 from v$instance
3 /
INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 hdfn
gitrdone
10.2.0.2.0 27-JUL-06 OPEN NO 1 STOPPED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO
July 31, 2006 - 9:04 pm UTC
v_$session can be used in that case
Privilege for USER_ dictionary views
Asim, July 12, 2007 - 10:47 am UTC
Hi Tom,
We have a stored procedure which does query to dba_tables, dba_sub_partitions, dba_indexes, dba_ind_columns etc.
Now we want to avoid the grants needed for production deployment. So I have tried using user_tables, user_sub_partitions, user_indexes etc inside the stored procedure and it works because I am looking for only information from those views which I own.
So just wanted to confirm with you that is there any specific grant needed to access "user" views from the owner schema inside the stored procedure? The stored procedure will remain in owner schema and it will be executed from an user schema?
Does authid matters for this case?
July 12, 2007 - 12:12 pm UTC
you only need to remember that the user views will return the current schema's information - so no matter what user runs that procedure, they will see the OWNER OF THAT PROCEDURE's data only.
USER dictionary views access
Asim, July 12, 2007 - 12:23 pm UTC
Hi Tom,
Thank you for confirming that.
These are the views which stored procedure is referring right now and we want to replace them with "user" views.
DBA_TAB_SUBPARTITIONS
DBA_TABLES
DBA_IND_COLUMNS
DBA_INDEXES
DBA_CONSTRAINTS
DBA_CONS_COLUMNS
Yes we do need only the information for the schema in which the stored procedure will be present. So I believe we should be ok.
Can you tell me what about DBMS_STATS.GATHER_TABLE_STATS?
I think we only need the privilege to run this from the owner schema for the owner table.
Thanks,
Asim
July 12, 2007 - 1:07 pm UTC
you can analyze your own tables using dbms_stats, yes.
grant select on SYS views to developers
Atta, September 11, 2013 - 12:37 pm UTC
Hello Tom,
I've seen above your quote 'It is safe for development environment where the developers should be able to see all of the DBA views, the V$ views.
I wonder why my DBA sees 'availability, correctness and consistency' of development database compromised and asks to me which specific views I need to see. Of course to do properly my job, I can't know exactly in advance which views from sys I will need.
Do you see any real reasons for not being allowed to see any v$ view ?
My guess is that he just doesn't know which are the views to stay hidden ( are they ? ), but at the end we speak about Development.
I personally assume also he's never been a good developer to understand that necessity some developers have for not saying a good DBA.
September 23, 2013 - 5:47 pm UTC
I do don't see why developers should not have access to the v$ information in test/dev - including full access to ASH and AWR.
v$ view access over dblink from package
Ian, December 05, 2014 - 2:48 pm UTC
Hi Tom
Welcome back!
I have a similar issue. I am trying to access gv$instance over a dblink from within a package.
I have granted select to gv_$instance to the dblink end user.
I can select in SQL*Plus from gv$instance@my_dblink with no problem.
Initially I could also do the same from an anonymous PL/SQL block and even a package.
I then added gv$active_session_history (same grant to gv_$active_session_history, same ability to select from SQL*Plus) to the anonymous PL/SQL block but got the following errors:
ORA-04052: error occurred when looking up remote object SYS.GV_$ACTIVE_SESSION_HISTORY@XXPX_TEMP_P1
ORA-02030: can only select from fixed tables/views
Now this is where it gets odd. I went back to the original anonymous block (just gv$instance) and got the same errors.
I then double checked I could use SQL*Plus to select from the gv$ view - no problem.
So it used to work in PL/SQL with gv$instance - but now it doesn't.
NB - The dblink is a loopback to the same database.
Any ideas? I found one hit on this site from someone else getting intermittent problems with gv$ views over dblinks - but there was no follow up. Nothing relevant on Metalink.
Regards
Ian
December 07, 2014 - 12:22 pm UTC
I'm sorry, I cannot reproduce, I tried...
I'll have to refer you to support (an SR). They can help you enable some tracing that might be of assistance.
One workaround to try would be to create a view of the remote object and select from the view - can you try that?
A reader, September 17, 2016 - 11:29 am UTC
Suppose I have a three procedure like proc_1, proc_2 ans proc3.
but they also have a different code but following query is a common of them.
Query :- " select * from employee e where e.emp_id=:proc_argument "
so when procedure calls.
sql statement appears in a v$sql and v$session.
but I want ti identify which procedure is call like proc_1 or proc_2 or proc_3?
September 17, 2016 - 3:05 pm UTC