When I try to execute a view that includes tables from different schemas an ORA-001031 Insufficient privileges is thrown. These tables have execute permission for the schema where the view was created. If I execute the view’s SQL Statement it works. What am I missing?
asked Sep 26, 2008 at 16:47
Finally I got it to work. Steve’s answer is right but not for all cases. It fails when that view is being executed from a third schema. For that to work you have to add the grant option:
GRANT SELECT ON [TABLE_NAME] TO [READ_USERNAME] WITH GRANT OPTION;
That way, [READ_USERNAME] can also grant select privilege over the view to another schema
Fourat
2,3162 gold badges40 silver badges53 bronze badges
answered Sep 26, 2008 at 18:47
Igor ZelayaIgor Zelaya
4,1474 gold badges34 silver badges52 bronze badges
1
As the table owner you need to grant SELECT access on the underlying tables to the user you are running the SELECT statement as.
grant SELECT on TABLE_NAME to READ_USERNAME;
answered Sep 26, 2008 at 16:53
Steve KSteve K
19.3k6 gold badges51 silver badges50 bronze badges
Q. When is the «with grant option» required ?
A. when you have a view executed from a third schema.
Example:
schema DSDSW has a view called view_name
a) that view selects from a table in another schema (FDR.balance)
b) a third shema X_WORK tries to select from that view
Typical grants:
grant select on dsdw.view_name to dsdw_select_role;
grant dsdw_select_role to fdr;
But: fdr gets
select count(*) from dsdw.view_name;
ERROR at line 1:
ORA-01031: insufficient privileges
issue the grant:
grant select on fdr.balance to dsdw with grant option;
now fdr:
select count(*) from dsdw.view_name;
5 rows
answered Jul 15, 2009 at 18:27
Let me make a recap.
When you build a view containing object of different owners, those other owners have to grant «with grant option» to the owner of the view. So, the view owner can grant to other users or schemas….
Example:
User_a is the owner of a table called mine_a
User_b is the owner of a table called yours_b
Let’s say user_b wants to create a view with a join of mine_a and yours_b
For the view to work fine, user_a has to give «grant select on mine_a to user_b with grant option»
Then user_b can grant select on that view to everybody.
answered Apr 16, 2010 at 22:13
If the view is accessed via a stored procedure, the execute grant is insufficient to access the view. You must grant select explicitly.
answered Sep 26, 2008 at 17:07
dacracotdacracot
21.8k26 gold badges105 silver badges151 bronze badges
If the view is accessed via a stored procedure, the execute grant is insufficient to access the view. You must grant select explicitly.
simply type this
grant all on to public;
answered May 16, 2013 at 15:45
To use a view, the user must have the appropriate privileges but only for the view itself, not its underlying objects. However, if access privileges for the underlying objects of the view are removed, then the user no longer has access. This behavior occurs because the security domain that is used when a user queries the view is that of the definer of the view. If the privileges on the underlying objects are revoked from the view’s definer, then the view becomes invalid, and no one can use the view. Therefore, even if a user has been granted access to the view, the user may not be able to use the view if the definer’s rights have been revoked from the view’s underlying objects.
Oracle Documentation
http://docs.oracle.com/cd/B28359_01/network.111/b28531/authorization.htm#DBSEG98017
answered Mar 20, 2014 at 14:04
Van GoghVan Gogh
4651 gold badge7 silver badges8 bronze badges
you may also create view with schema name
for example create or replace view schema_name.view_name as select..
answered Sep 3, 2020 at 7:26
1
When I try to execute a view that includes tables from different schemas an ORA-001031 Insufficient privileges is thrown. These tables have execute permission for the schema where the view was created. If I execute the view’s SQL Statement it works. What am I missing?
asked Sep 26, 2008 at 16:47
Finally I got it to work. Steve’s answer is right but not for all cases. It fails when that view is being executed from a third schema. For that to work you have to add the grant option:
GRANT SELECT ON [TABLE_NAME] TO [READ_USERNAME] WITH GRANT OPTION;
That way, [READ_USERNAME] can also grant select privilege over the view to another schema
Fourat
2,3162 gold badges40 silver badges53 bronze badges
answered Sep 26, 2008 at 18:47
Igor ZelayaIgor Zelaya
4,1474 gold badges34 silver badges52 bronze badges
1
As the table owner you need to grant SELECT access on the underlying tables to the user you are running the SELECT statement as.
grant SELECT on TABLE_NAME to READ_USERNAME;
answered Sep 26, 2008 at 16:53
Steve KSteve K
19.3k6 gold badges51 silver badges50 bronze badges
Q. When is the «with grant option» required ?
A. when you have a view executed from a third schema.
Example:
schema DSDSW has a view called view_name
a) that view selects from a table in another schema (FDR.balance)
b) a third shema X_WORK tries to select from that view
Typical grants:
grant select on dsdw.view_name to dsdw_select_role;
grant dsdw_select_role to fdr;
But: fdr gets
select count(*) from dsdw.view_name;
ERROR at line 1:
ORA-01031: insufficient privileges
issue the grant:
grant select on fdr.balance to dsdw with grant option;
now fdr:
select count(*) from dsdw.view_name;
5 rows
answered Jul 15, 2009 at 18:27
Let me make a recap.
When you build a view containing object of different owners, those other owners have to grant «with grant option» to the owner of the view. So, the view owner can grant to other users or schemas….
Example:
User_a is the owner of a table called mine_a
User_b is the owner of a table called yours_b
Let’s say user_b wants to create a view with a join of mine_a and yours_b
For the view to work fine, user_a has to give «grant select on mine_a to user_b with grant option»
Then user_b can grant select on that view to everybody.
answered Apr 16, 2010 at 22:13
If the view is accessed via a stored procedure, the execute grant is insufficient to access the view. You must grant select explicitly.
answered Sep 26, 2008 at 17:07
dacracotdacracot
21.8k26 gold badges105 silver badges151 bronze badges
If the view is accessed via a stored procedure, the execute grant is insufficient to access the view. You must grant select explicitly.
simply type this
grant all on to public;
answered May 16, 2013 at 15:45
To use a view, the user must have the appropriate privileges but only for the view itself, not its underlying objects. However, if access privileges for the underlying objects of the view are removed, then the user no longer has access. This behavior occurs because the security domain that is used when a user queries the view is that of the definer of the view. If the privileges on the underlying objects are revoked from the view’s definer, then the view becomes invalid, and no one can use the view. Therefore, even if a user has been granted access to the view, the user may not be able to use the view if the definer’s rights have been revoked from the view’s underlying objects.
Oracle Documentation
http://docs.oracle.com/cd/B28359_01/network.111/b28531/authorization.htm#DBSEG98017
answered Mar 20, 2014 at 14:04
Van GoghVan Gogh
4651 gold badge7 silver badges8 bronze badges
you may also create view with schema name
for example create or replace view schema_name.view_name as select..
answered Sep 3, 2020 at 7:26
1
for ORA-01031: insufficient privileges. Some of the more common causes are:
- You tried to change an Oracle username or password without having the appropriate privileges.
- You tried to perform an
UPDATEto a table, but you only haveSELECTaccess to the table. - You tried to start up an Oracle database using
CONNECT INTERNAL. - You tried to install an Oracle database without having the appropriate privileges to the operating-system.
The option(s) to resolve this Oracle error are:
- You can have the Oracle DBA grant you the appropriate privileges that you are missing.
- You can have the Oracle DBA execute the operation for you.
- If you are having trouble starting up Oracle, you may need to add the Oracle user to the dba group.
For ORA-00942: table or view does not exist. You tried to execute a SQL statement that references a table or view that either does not exist, that you do not have access to, or that belongs to another schema and you didn’t reference the table by the schema name.
If this error occurred because the table or view does not exist, you will need to create the table or view.
You can check to see if the table exists in Oracle by executing the following SQL statement:
select *
from all_objects
where object_type in ('TABLE','VIEW')
and object_name = 'OBJECT_NAME';
For example, if you are looking for a suppliers table, you would execute:
select *
from all_objects
where object_type in ('TABLE','VIEW')
and object_name = 'SUPPLIERS';
OPTION #2
If this error occurred because you do not have access to the table or view, you will need to have the owner of the table/view, or a DBA grant you the appropriate privileges to this object.
OPTION #3
If this error occurred because the table/view belongs to another schema and you didn’t reference the table by the schema name, you will need to rewrite your SQL to include the schema name.
For example, you may have executed the following SQL statement:
select *
from suppliers;
But the suppliers table is not owned by you, but rather, it is owned by a schema called app, you could fix your SQL as follows:
select *
from app.suppliers;
If you do not know what schema the suppliers table/view belongs to, you can execute the following SQL to find out:
select owner
from all_objects
where object_type in ('TABLE','VIEW')
and object_name = 'SUPPLIERS';
This will return the schema name who owns the suppliers table.
I installed Oracle 11G on my Windows 2008 R2 Server. I also installed Oracle Client Libraries using separate installation media. After the client installation, when I try to log into the database using:
C:>sqlplus / as sysdba
I get a following error:
ORA-01031: insufficient privileges
This worked before the client installation. My account is on the ORA_DBA group. My account is also in the Administrator group. I don’t have ORACLE_SID set in my environment variables list. Does it have to be? This worked before the client installation, and I did not have that variable at that time either.
UAC is on at the lowest level, and I always run cmd as admin.
I have an sqlnet.ora file like this in the folder:
C:appmyaccountproduct11.2.0dbhome_1NETWORKADMIN
sqlnet.ora
# sqlnet.ora Network Configuration File: C:appmyaccountproduct11.2.0dbhome_1networkadminsqlnet.ora
# Generated by Oracle configuration tools.
# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
tnsnames.ora
# tnsnames.ora Network Configuration File: C:appmyaccountproduct11.2.0dbhome_1networkadmintnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.mydb.com)
)
)
listener.ora
# listener.ora Network Configuration File: C:appmyaccountproduct11.2.0dbhome_1networkadminlistener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = C:appmyaccountproduct11.2.0dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:C:appmyaccountproduct11.2.0dbhome_1binoraclr11.dll")
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)
ADR_BASE_LISTENER = C:appmyaccount
From the windows registry (using regedit), from the path Computer > HKEY_LOCAL_MACHINE > SOFTWARE > Wow6432Node > ORACLE I can find two keys:
KEY_OraClient11g_home1
KEY_OraDb11g_home1
And under those keys I can find ORACLE_HOME variables. So it seem that the client installation generated also ORACLE_HOME so that I have now two ORACLE_HOMEs. I don’t know if this has something to do with this problem?
How to resolve this error on my Windows machine?