Getting ORA-01031: insufficient privileges

[dbalearner]

Hi,

As a user with "DBA" privilege and quota on system tablesapce I am trying to create a view but I am getting ORA-01031 error. I can select from sys.dba_data_files and can create this view as "sys" user. Any ideas? Thanks

1 create or replace view view_data_files as
2 select tablespace_name, sum(bytes) bytes
3 from sys.dba_data_files
4* group by tablespace_name;
> /
from sys.dba_data_files
*
ERROR at line 3:
ORA-01031: insufficient privileges

I have these roles:

1* select * from user_role_privs
/

USERNAME GRANTED_ROLE ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
SYS_DBALEARNER AQ_ADMINISTRATOR_ROLE YES YES NO
SYS_DBALEARNER CONNECT YES YES NO
SYS_DBALEARNER DBA YES YES NO
SYS_DBALEARNER RESOURCE YES YES NO
SYS_DBALEARNER SALES_HISTORY_ROLE YES YES NO

 

[stefanhei]

When you create a stored object (like a view or a procedure) which accesses an object in a differnt schema you need to be granted access on that object itself and not through a role.
Grant select-privilege on dba_data_files to your user and you will be able to create your view.

Stefan

 

[dbalearner]

Thanks Stefan. My default tablesapce is system so I assume this view will be created in system tablespace (correct?). Is there an easier way of granting select on data dictionary tables in order for me to create these views? I mean I can use sys logins because I create and drop these views afterwards anyway. In short can I do this operation under my user without granting selects?

Many thanks

 

[stefanhei]

Both questions have to be answered:
Yes, but not the way you think.

* default tablespace: views have no physical appearance outside the data dictionary. The definition of all views are stored in the data dictionary, no matter what's your default tablespace.
And finally the data dictionary resides in tablespace SYSTEM.

* granting selects: You may grant SYSDBA to your user. Then you can issue

connect SYS_DBALEARNER/yourPassword as sysdba;

But when you do so you are connected as SYS and not as your user.
If there is another way I am not aware of it.

Stefan

 

[SantaMufasa]

In addition to Stefan's excellent advice, I will assert that from an organisational (and performance) perspective, there is no good reason (i.e., it is poor form) to allow any objects to reside in the SYSTEM tablespace besides those which Oracle installs there for database-management purposes. Placing user-owned/managed objects in the SYSTEM tablespace contributed to unwanted fragmentation in that tablespace. Therefore, I highly recommend your moving any objects that you placed in the SYSTEM tablespace to a tablespace that houses user-related objects.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[dbalearner]

Thanks Stefan and SantaMufas for your comments. Does this imply that anytime a user with "DBA" privilege wants to create a view based on a table/view on another schema (say sys), that user needs to be granted select on that table? I understand that granting a usewr "sysdba" role effectively means when the user logs in it becomes sys user. This may not be acceptable from the audit/security point of view. So my last question is if there is a more secure workaround for this issue?

Many thanks

 

[SantaMufasa]

Learner,

Anyone with DBA role membership, has, as part of the DBA privileges, the "SELECT ANY TABLE" privilege. This means that a DBA does not need explicit GRANTs to access anyone else's tables or views.

Also, you should be VERY reluctant to grant SYSDBA privileges to any user since this privilege is a "Super-DBA" role that allows many risky maneuvers such as STARTUP and SHUTDOWN of the database.

I believe that all you are intending to grant to someone is perhaps DBA privileges (which, again is a risk-filled proposition since a DBA has god-like powers on a database). For DBA privileges, all you need to say is "GRANT DBA TO username;" Also, subsequent to doing that grant, when such a user connects to the database, then are not connecting to the SYS schema; they are connecting as themselves. This, then, is the "more secure workaround for this issue."

But back to your intial question: you do not need to (and should not) grant DBA privileges to someone just to access another's tables. Those GRANTs to a user should accrue via either explicit GRANTs or by membership in a role that provides explict GRANTs to the tables in question. You should not "give away the ranch" just because someone needs to "drink from the well."

Does these suggestions resolve your questions?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[dbalearner]

Thanks SantaMufasa. Is there such a role that I can grant myself that role (login as sys) so that I do not need explicit select grant on individual dba_% tables to create these views? With the current approach (explicit grant select on individual dba tables), I have to repeat the grant for each view that needs to access dba_% tables. It looks to me not really the most efficient approach.

many thanks

 

[SantaMufasa]

Yes, you will be pleased to know of this time-saving device:

GRANT select_catalog_role to dbalearner;

This give read privileges on the "DBA_..." and all other data dictionary views.

Let us know how this changes your life.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[dbalearner]

I tried this. I am afraid it does not work

GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
SYS_DBALEARNER DBA YES YES
SYS_DBALEARNER CONNECT YES YES
SYS_DBALEARNER RESOURCE YES YES
SYS_DBALEARNER SALES_HISTORY_ROLE YES YES
SYS_DBALEARNER SELECT_CATALOG_ROLE YES YES
SYS_DBALEARNER EXECUTE_CATALOG_ROLE YES YES
SYS_DBALEARNER AQ_ADMINISTRATOR_ROLE YES YES

The only way seems to be direct grant. I do not seem to be able to go through role. Stefan I believe mentioned this as well.

thanks

 

[SantaMufasa]

Learner,

Could you please post your code that show the symptoms that you are encountering? As a user with the same privileges you list above, I just created a view from a "DBA_..." table to which I do not have explicit privileges:

create view louis_view
as select substr(owner||'.'||table_name,1,40) tabname
   ,grantee
   ,privilege
from dba_tab_privs;

View created.

SQL> desc louis_view
 Name                    Null?    Type
 ----------------------- -------- ------------
 TABNAME                          VARCHAR2(40)
 GRANTEE                 NOT NULL VARCHAR2(30)
 PRIVILEGE               NOT NULL VARCHAR2(40)

select * from louis_view where rownum <= 5;

TABNAME                                  GRANTEE                        PRIVILEGE
---------------------------------------- ------------------------------ ---------
SYSTEM.DEF$_ERROR                        SYS                            SELECT
SYSTEM.DEF$_DESTINATION                  SYS                            SELECT
SYSTEM.DEF$_CALLDEST                     SYS                            SELECT
SYSTEM.DEF$_LOB                          SYS                            SELECT
SYSTEM.DEF$_TEMP$LOB                     SYS                            DELETE

So, can you please post the code that is giving you trouble?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[dbalearner]

Thanks Santa. I tried it with creating a new dba user and granting the catalog role to this user. It worked. I believe in this case my user has a profile (for security reasons) which stops me from creating the view. I think I need to change my dba user profile to DEFAULT. Does adding a profile automatically restrict access of a user to data dictionary tables? For example with my DBA user with catalog access but having this profile I can select from DBA tables but cannot create the view.

Thanks

 

[SantaMufasa]

Learner,

I am not aware of any profile setting that would, in and of itself, proscribe your creating the view.

I am glad to hear that you finally got things working to build the VIEW.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[dbalearner]

Santa,

It appears that in 9i the case will be that the role will not be sufficient. Your views please?

From ASK tom

http://asktom.oracle.com/pls/ask/f?...P8_DISPLAYID,F4950_P8_CRITERIA:22541885338185

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

http://asktom.oracle.com/~tkyte/Misc/RolesAndProcedures.html

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)

PS. I will try it on prod box

 

[SantaMufasa]

Learner,

Tom Kyte is God when it comes to anything Oracle. My view is, If Tom says it, you can depend upon it. His posting does seem to offer some workarounds for this apparent restriction.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.