Scrip compilation in databasea with DBA privilege

652811

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 ?

Surachart Opun

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.

sb92075

We can't debug code we can not see.

privileges acquired via ROLE do NOT apply within PL/SQL procedures!

652811

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

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

>

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

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

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

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 !!!!!!!!!!!