V$ Views

[jhutchin]

I looked at this issue with v$ views and became annoyed.

I have a script that creates a table based on a v$ view that is meant to be run by a 'normal' user.

So my question is can this user pretend to be SYS?

Or is there another way around this issue?

[Mr.Hanky]

I'm not sure I understand what you are tring to do here.
Can't you just do a CREATE as SELECT, than grant all the rights on the new table?

MH

[jhutchin]

This is the user creation script;

drop user ORACLE_GW cascade;

CREATE USER ORACLE_GW IDENTIFIED BY ORACLE_GW
DEFAULT TABLESPACE ORACLE_GW
TEMPORARY TABLESPACE ORACLE_GW_TEMP
PROFILE DEFAULT ACCOUNT UNLOCK;
GRANT CONNECT TO ORACLE_GW;
GRANT RESOURCE TO ORACLE_GW;
GRANT SELECT_CATALOG_ROLE TO ORACLE_GW;
GRANT SELECT ANY TABLE TO ORACLE_GW;
GRANT UNLIMITED TABLESPACE TO ORACLE_GW;

CONNECT internal

GRANT EXECUTE ON DBMS_ALERT TO ORACLE_GW ;
GRANT EXECUTE ON DBMS_JOB TO ORACLE_GW;

[jhutchin]

This user then has to run another script that uses V$ tables;

-- Create the database monitor table;
Drop Table DBMONITOR_TABLE cascade constraints;

Create Table DBMONITOR_TABLE
as
Select Username, Sid, Serial#,Client_info
From v$session
/

The problem is that they cannot see these V$ tables.

[Mr.Hanky]

It works for me in version 9.2.0.3.0


SQL> CREATE USER tester IDENTIFIED BY tester
2 DEFAULT TABLESPACE user01
3 TEMPORARY TABLESPACE temp01
4 PROFILE DEFAULT ACCOUNT UNLOCK;

User created.

SQL> GRANT CONNECT TO tester;

Grant succeeded.

SQL> GRANT RESOURCE TO tester;

Grant succeeded.

SQL> GRANT SELECT_CATALOG_ROLE TO tester;

Grant succeeded.

SQL> GRANT SELECT ANY TABLE TO tester;

Grant succeeded.

SQL> GRANT UNLIMITED TABLESPACE TO tester;

Grant succeeded.

SQL> GRANT EXECUTE ON DBMS_ALERT TO tester ;

Grant succeeded.

SQL> GRANT EXECUTE ON DBMS_JOB TO tester;

Grant succeeded.

SQL> conn tester@my_instance
Enter password: ******
Connected.
SQL> desc v$parameter
Name
-----------------------------------
NUM
NAME
TYPE
VALUE
ISDEFAULT
ISSES_MODIFIABLE
ISSYS_MODIFIABLE
ISMODIFIED
ISADJUSTED
DESCRIPTION
UPDATE_COMMENT

SQL> Create Table DBMONITOR_TABLE
2 as
3 Select Username, Sid, Serial#,Client_info
4 From v$session
5 /

Table created.

SQL> select * from DBMONITOR_TABLE
2 where rownum<5;

USERNAME SID SERIAL# CLIENT_INFO
------------------------------ ---------- ---------- --------------
1 1
2 1
3 1
4 1

SQL> desc DBMONITOR_TABLE
Name
------------------------------------------------------------------
USERNAME
SID
SERIAL#
CLIENT_INFO

MH

[jhutchin]

Thanks!...I'll have a go.

btw my DB is Oracle 8i (8.1.5).

I'll let you know how it goes.

[jhutchin]

Well it sort of worked.

SYS.V_$SESSION can be viewed.

Question:
Is there a way to allow a user to be the SYS user?

This is possible in Sybase but can it be done in Oracle?

[Mr.Hanky]

Originally posted by jhutchin
Well it sort of worked.

SYS.V_$SESSION can be viewed.

Question:
Is there a way to allow a user to be the SYS user?

This is possible in Sybase but can it be done in Oracle?

Get out your asbestos suit because hellfire may come raining down on you for this one!

The words "user" and "sys" should NEVER be used in the same sentence.
"USER" is a 4 letter word!

Don't ever give them God's rights to any database, even developement.

MH

[jhutchin]

Get out your asbestos suit because hellfire may come raining down on you for this one!

I take it that is a no then!

[davey23uk]

Works on 8.1.7

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production

SQL> CREATE USER tester IDENTIFIED BY tester
2 /

User created.

SQL> grant connect to tester
2 /

Grant succeeded.

SQL> grant resource to tester
2 /

Grant succeeded.

SQL> grant select_catalog_role to tester;

Grant succeeded.

SQL> grant select any table to tester;

Grant succeeded.

SQL> grant unlimited tablespace to tester;

Grant succeeded.

SQL> grant execute on dbms_alert to tester;

Grant succeeded.

SQL> grant execute on dbms_job to tester;

Grant succeeded.

SQL> connect tester/tester@re1
Connected.

SQL> desc v$parameter
Name Null? Type
----------------------------------------- -------- ---------------------------

NUM NUMBER
NAME VARCHAR2(64)
TYPE NUMBER
VALUE VARCHAR2(512)
ISDEFAULT VARCHAR2(9)
ISSES_MODIFIABLE VARCHAR2(5)
ISSYS_MODIFIABLE VARCHAR2(9)
ISMODIFIED VARCHAR2(10)
ISADJUSTED VARCHAR2(5)
DESCRIPTION VARCHAR2(64)

SQL> 2 as
SQL> 3 select username, sid, serial#, client_info
SQL> 4 from v$session;
SQL> /

Table created.


1* select * from dbmonitor_table where rownum < 5
SQL> /

USERNAME SID SERIAL#
------------------------------------- ---------- ----------
CLIENT_INFO
----------------------------------------------------------------
1 1


2 1


3 1



USERNAME SID SERIAL#
------------------------------ ---------- ----------
CLIENT_INFO
----------------------------------------------------------------
4 1



SQL> desc dbmonitor_table;
Name Null? Type
----------------------------------------- -------- ---------------------------

USERNAME VARCHAR2(30)
SID NUMBER
SERIAL# NUMBER
CLIENT_INFO VARCHAR2(64)

SQL>