Forum Stats

  • 3,817,447 Users
  • 2,259,334 Discussions
  • 7,893,776 Comments

Discussions

Scrip compilation in databasea with DBA privilege

652811
652811 Member Posts: 124
edited Jul 22, 2009 4:29PM in General Database Discussions
Hi Gurus,

I was compiling two procedures using one application user in database. These two procedures are in valid state in production.
I was doing in testing enviornment .
The application user in test env is same as in production withh all proper privileges.

In one procedure script , v$sqltext,v$session and v$process are being used and in other DBA_USERS is being used.
I tried many times for compilation script but error was like table or view is not exist .

But same user was able to query on those views .
To solve the problem , i gave select privilege on v_$<<three >> to application user and then compiled and i was successful.

But my question is user has DBA privilege and able to query on above views but procedure was not created ?

Best Answer

  • Surachart Opun
    Surachart Opun Member Posts: 1,662
    Answer ✓
    oramnts wrote:
    My problem has come when block had dba_users ,v$..s

    I mentioed i compiled all the procedures but these procedures have dba_ v$ .


    Thanks & Regards
    When you grant table to role and grant role to user.

    that user can query... But on pl/sql no find that table.

    http://surachartopun.com/2008/09/ora-00942-table-or-view-does-not-exist.html

    If you need pl/sql ... to query v$ or tables + views anyway. YOU HAVE TO GRANT DIRECTLY!

    Roles are not meant to be used by application developers, because the privileges to access schema objects within stored programmatic constructs must be granted directly.

Answers

  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond
    We can't debug code we can not see.

    privileges acquired via ROLE do NOT apply within PL/SQL procedures!
  • 652811
    652811 Member Posts: 124
    All database objects are being created by one application user .
    Accoring to applications different privilages are given to different roles and then roles are assigned to different user.

    I have taken code from production database and compiling in test enviornment .

    As i said about object creation application user has all privileges . but why separate privilege was requried to compile procedures (only 2)

    Thanks & Regards
  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond
    As i said about object creation application user has all privileges .
    If above was REALLY, REALLY true, then you would not have had to do additional GRANT as stated below
    but why separate privilege was requried to compile procedures (only 2)
    privileges acquired via ROLE do not apply within PL/SQL procedures.
  • Surachart Opun
    Surachart Opun Member Posts: 1,662
    edited Jul 22, 2009 1:55PM
    >
    But my question is user has DBA privilege and able to query on above views but procedure was not created ?
    SQL> Grant dba to user1

    DBA role -> can query dictionary views (v$... , dba_...), and can create procedure/function/package.'

    SQL> select * from DBA_ROLE_PRIVS where GRANTEE='DBA';

    GRANTEE GRANTED_ROLE ADM DEF
    ------------------------------
    DBA SCHEDULER_ADMIN YES YES
    DBA DELETE_CATALOG_ROLE YES YES
    DBA EXECUTE_CATALOG_ROLE YES YES
    DBA WM_ADMIN_ROLE NO YES
    DBA EXP_FULL_DATABASE NO YES
    DBA SELECT_CATALOG_ROLE YES YES
    DBA JAVA_DEPLOY NO YES
    DBA GATHER_SYSTEM_STATISTICS NO YES
    DBA JAVA_ADMIN NO YES
    DBA XDBADMIN NO YES
    DBA IMP_FULL_DATABASE NO YES
    DBA XDBWEBSERVICES NO YES

    Example:

    SQL> create user user1 identified by user1;

    User created.

    SQL> grant dba to user1;

    Grant succeeded.

    SQL> connect user1/user1
    Connected.

    SQL> CREATE PROCEDURE prc_test(i IN NUMBER) AS
    BEGIN
    null;
    END prc_test;
    /

    Procedure created.

    SQL> show user;
    USER is "USER1"

    Edited by: Surachart Opun (HunterX) on Jul 23, 2009 12:54 AM
  • 652811
    652811 Member Posts: 124
    My problem has come when block had dba_users ,v$..s

    I mentioed i compiled all the procedures but these procedures have dba_ v$ .


    Thanks & Regards
  • Surachart Opun
    Surachart Opun Member Posts: 1,662
    Answer ✓
    oramnts wrote:
    My problem has come when block had dba_users ,v$..s

    I mentioed i compiled all the procedures but these procedures have dba_ v$ .


    Thanks & Regards
    When you grant table to role and grant role to user.

    that user can query... But on pl/sql no find that table.

    http://surachartopun.com/2008/09/ora-00942-table-or-view-does-not-exist.html

    If you need pl/sql ... to query v$ or tables + views anyway. YOU HAVE TO GRANT DIRECTLY!

    Roles are not meant to be used by application developers, because the privileges to access schema objects within stored programmatic constructs must be granted directly.
  • 652811
    652811 Member Posts: 124
    Thats true ...
    i granted privileges to user then executed ..

    My problem was solved and wanted to know why it didn't work at that time ...

    Many thanks !!!!!!!!!!!
This discussion has been closed.