Am confused....thinking of going back to MS SQL Server.

[abhaysk]

PS. Am confused.

Version :
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
PL/SQL Release 9.2.0.3.0 - Production
CORE 9.2.0.3.0 Production
TNS for 32-bit Windows: Version 9.2.0.3.0 - Production
NLSRTL Version 9.2.0.3.0 - Production

SQL> conn sys as sysdba
Enter password: 
Connected.
SQL> grant dba, resource, connect to abhay;

Grant succeeded.

SQL> conn abhay
Enter password: *****
Connected.
SQL> create trigger test_v_session after alter on schema
  2  begin
  3  select * from v$session;
  4  end;
  5  .
SQL> ed
Wrote file afiedt.buf

  1  create or replace trigger test_v_session after alter on schema
  2  begin
  3  select * from v$session; -- Oracle says Table/View does not exist
  4* end;
SQL> /

Warning: Trigger created with compilation errors.

SQL> show errors
Errors for TRIGGER TEST_V_SESSION:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/1      PL/SQL: SQL Statement ignored
2/15     PL/SQL: ORA-00942: table or view does not exist


SQL> select * from v$session; -- Ahh now oracle feels table/view is there.

SADDR                     SID              SERIAL#               AUDSID PADDR                   USER# USERNAME          
-------- -------------------- -------------------- -------------------- -------- -------------------- -------------
7ADB3708                    1                    1                    0 7AD90C3C                    0                   
7ADB4020                    2                    1                    0 7AD90F90                    0                   
7ADB4938                    3                    1                    0 7AD912E4                    0                   
7ADB5250                    4                    1                    0 7AD91638                    0                   
7ADB5B68                    5                    1                    0 7AD9198C                    0                   
7ADB6480                    6                    1                    0 7AD91CE0                    0                   
7ADB6D98                    7                 1176                  521 7AD92034                   25 ABHAY             
7ADB76B0                    8                  319                  518 7AD926DC                   18 SCOTT             
7ADB88E0                   10                  355                  519 7AD92388                   18 SCOTT             

9 rows selected.

-- We upgraded our ACP DB to 9i today and here oracle behaves like the senario as above ( Above test case is on my local DB 9i-> simulated the problem of a DB event trigger in ACP )

-- I dont understand Oracle now a days.

Some times i get feeling, Oracle is becoming like MS SQL Server.


Any thoughts or is it bug on 9i?

Thanks
Abhay.

[jovery]

You will need to grant select access to v_$session to your user to access that view in a procedure/trigger.

Regards

[abhaysk]

Originally posted by jovery
You will need to grant select access to v_$session to your user to access that view in a procedure/trigger.

Regards

I thought this would have taken care of that

SQL> ed
Wrote file afiedt.buf

  1* select * from role_sys_privs where pRIVILEGE like 'SELECT ANY TABLE'
SQL> /

ROLE                           PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
DBA                           SELECT ANY TABLE                         YES 
EXP_FULL_DATABASE              SELECT ANY TABLE                         NO
IMP_FULL_DATABASE              SELECT ANY TABLE                         NO

Any specific reason to explicitly grant a table/view that is being used by user in PROC/Trigger?

Abhay.

[jovery]

Privileges granted via the roles are disabled inside the procedure, take a look at the following doc for more info:

http://support.oracle.co.uk/metalink...&p_id=168168.1

HTH

[pando]

http://metalink.oracle.com/metalink/...p_id=1031418.6

security and dependency model

[abhaysk]

Originally posted by jovery
Privileges granted via the roles are disabled inside the procedure, take a look at the following doc for more info:

http://support.oracle.co.uk/metalink...&p_id=168168.1

HTH

Thanks.

But now 1 more question.

Before upgrade to 9i, all the required permissions were ther.
How come after upgrade to 9i these permissions have gone?

I think Oracle ( During install of new version ) would sense existing DBs and ask if the DBs need to be upgraded to new versions.

Then it should take care what was ther before should be retained.
why has'nt it been able to do.

I see in our development DB which was upgraded 2 days ago, had all permissions or watever before and now.

Is it possible that during upgrade somthings might change?

[adewri]

Originally posted by abhaysk
Thanks.

But now 1 more question.

Before upgrade to 9i, all the required permissions were ther.
How come after upgrade to 9i these permissions have gone?

I have upgraded so many oracle databases (may be more than 50) and between all versions from 7.3.4 till 9i and no, never, not even a single grant was missing. Here i would say you are speculating things up.

Do it properly and there wont be any err.

[jmodic]

I hope you realise that after you sort out your problems related to grants, that trigger of yours will never compile (unless you correct it syntacticaly, ofcourse)....

[abhaysk]

Originally posted by adewri
Do it properly and there wont be any err.

Its our PRO DBAs who do upgrades...not me.

Well wat i told is exactly correct(infact wat i saw) that

Dev DB

Upgraded 2 days ago - No issues.

Only triggers(2 logon triggers) went invalid due to some incompatibility or i could say wrong coding.
I corrected those in Dev today and they had V$session object using in triggers. They got compiled correctly and are working fine.

ACP DB

Upgraded today.

When i went to correct the triggers ( same two triggers as in DEV )
i got the errors of Ora - 00942.

Now when this trigger was working properly when it where 8i, it obiviously had permission on v$session. Why not after upgrade.

And to my notice no errors were reported during upgrade.

Thanks
Abhay.

[julian]

Before you upgraded to 9i, did you read its new features? No? What do you expect?

SQL> show parameter O7_DICTIONARY_ACCESSIBILITY

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY boolean FALSE