Execute GRANT SELECT ON Xyz - Insufficient Privileges?

Sep 30, 2011

have an automated process which runs on an Oracle 8i database server as user abc. This process creates views/tables in other schemas, on the same database server, which point to objects owned by the abc user.

The issue I'm getting is that when I try to execute GRANT SELECT ON xyz.view123 TO PUBLIC as the abc user, I get an insufficient privileges.I should add that the abc user created the xyz.view123 table/view.

What grants/priviliges or whatever do I have to do to the abc schema?

View 2 Replies


ADVERTISEMENT

Schema Permissions - Execute GRANT SELECT ON Xyz - Insufficient Privileges?

Sep 30, 2011

I have an automated process which runs on an Oracle 8i database server as user abc.This process creates views/tables in other schemas, on the same database server, which point to objects owned by the abc user.

The issue I'm getting is that when I try to execute GRANT SELECT ON xyz.view123 TO PUBLIC as the abc user, I get an insufficient privileges.I should add that the abc user created the xyz.view123 table/view.

What grants/priviliges or whatever do I have to do to the abc schema?

View 1 Replies View Related

SQL & PL/SQL :: Insufficient Privileges When Creating Tables Using Execute Immediate?

Apr 30, 2010

SQL> connect sys as sysdba
Enter password:
Connected.
SQL> grant dba to temp;

Grant succeeded.

SQL> connect temp
Enter password:
Connected.
SQL>
SQL> create or replace procedure tempproc
2 is
3 begin
4 execute immediate 'create table temp_table(a varchar2(10))';
5 end;
6 /

Procedure created.

SQL> exec tempproc;
BEGIN tempproc; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "TEMP.TEMPPROC", line 4
ORA-06512: at line 1

SQL>
SQL> declare
2 begin
3 execute immediate 'create table temp_table(a varchar2(10))';
4 end;
5 /

PL/SQL procedure successfully completed.

SQL> desc temp_table;
Name Null? Type
----------------------------------------- -------- ----------------------------
A VARCHAR2(10)

Why am I not able to create a table from procedure using execute immediate?

View 3 Replies View Related

SQL & PL/SQL :: Grant Privileges For Select On View From Different Schemes

Oct 27, 2011

My need is to grant priveleges for select on view without granting on nested tables.

Actual problem is that I can grant privileges for particular user, but can't grant privileges for user group.

View 4 Replies View Related

Insufficient Privileges To Start DB?

May 25, 2011

I have installed oracle on my linux machne recently as an oracle2 user. To day i found my listener up when start my as database ( by logging in to the sqlplus ) sqlplus /nolog - i get into sqlplus

When i startup the database

SQL>startup - I get this error ORA-01031: insufficient privileges

I tried to check if my oracle user is in the dba and oinstall group. it wasn't

I added the user to the group by doing usermod -G dba,oinstall oracle2

i chek in the group to see if the user has been added to the group i see the user added but i still get the same error ORA-01031: insufficient privileges when i try to start the database.

I read some where that you can try to uncomment the SQLNET.AUTHENTICATION_SERVICES line on sqlnet.ora file but on by my file (SQLNET.ORA) there is only one line written (NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT))

View 7 Replies View Related

ORA-01031 - Insufficient Privileges

Jul 14, 2010

i have three scheme in one instance for example

x,y and z

i create view in x as x_view

next step create view in y as y_view select from x_view ;

and finished, create another view in z as z_view select from y_view;

i gave grants to all user , but when i try this query with x user as select * from z.z_view get this message ORA-01031: insufficient privileges;

attention that connect as x and try select * from z.z_view

View 3 Replies View Related

SQL & PL/SQL :: ORA-01031 / Insufficient Privileges

Dec 28, 2007

i have created a context from schema 'Schema1' using "accessed globally" option and created on package to set the values for conext. It is working fine.

When i deploy the same package on schema2 (remember the context is on schema1) and try to execute it on schema2 then it is giving me the following error.

Connected to:
Oracle Database 10g Release 10.2.0.1.0 - Production
SQL> exec krishna_pkg.MyProcedure
BEGIN krishna_pkg.MyProcedure; END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 90
ORA-06512: at "LGE2008DEV.KRISHNA_PKG", line 6
ORA-06512: at line 1

View 14 Replies View Related

PL/SQL :: Insufficient Privileges / Set ORACLE_SID

Aug 28, 2013

I installed 11g in my system.

Then I used to login as username@admin/password.

It returns error as ORA-12154: TNS:could not resolve the connect identifier specified. 

After that I tried sqlplus "/ as sysdba"

it returns as ORA-01031: insufficient privileges

and then i set ORACLE_SID and then try sqlplus "/ as sysdba"

it returns ORA-12560: TNS:protocol adapter error. 

View 1 Replies View Related

Error / ORA-01031 / Insufficient Privileges

Sep 27, 2013

I am facing problems during login through sqlplus prompt.

It occurs in all the Linux boxes, with 10g and 11g databases, whereas in Windows it works good.

Sqlplus / as sysdba works good ( So there is no problem in OS Authentication)

Sqlplus sys/xxxx@SID as sysdba works good( Ther is no problem with password file)

But Sqlplus /@SID as sysdba is not working, It gives me an error message whereas in my windows box it works without any problems.

Error: ORA-01031 : Insufficient priveleges

I verified the service id entry in tnsnames.ora, i did not find any problem.

View 1 Replies View Related

SQL & PL/SQL :: Getting ORA-01031 / Insufficient Privileges Error

Aug 2, 2012

I've some metadata tables on one schema (abcref@db1) and users updates this table everyday. I need to replicate these tables to other schemas on same database (defdev@db1) and different database (deftest@db2) everyday. So i created materialized views on target schemas and refresh these through a pl/sql procedure. Now i'm getting error while refreshing the materialized view from defdev@db1 and the materialized view refreshes successfully from deftest@db2.

Granted select access on abcref@db1 tables to defdev@db1 and deftest@db2 tables via role.

Getting below error from defdev@db1 schema.

ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SNAPSHOT_UTL", line 361
ORA-06512: at "SYS.DBMS_SNAPSHOT_UTL", line 443
ORA-06512: at line 1

Table on abcref@db1 schema
===========================
SQL> create table REF_PROD_CD
2 (
3 prod_cd VARCHAR2(5),
4 division_cd VARCHAR2(1),
5 brand_cd VARCHAR2(2)
6 );

[code]....

Now Using below procedure to refresh materialized views from Target schemas.

SQL> CREATE OR REPLACE PROCEDURE PR_REFRESH_MV IS
2 BEGIN
3 DBMS_MVIEW.REFRESH('REF_PROD_CD','C');
4 END;
5 /
Procedure created

View 4 Replies View Related

Login To Oracle 10g - Insufficient Privileges For Sys?

Jun 29, 2012

i am unable to login to oracle 10g as 'sys' as sysdba it is throwing an error like ORA-01031 insufficient privilidges for sys.but when i connect to database with username=system and password = oracle i am able to connect to database.

i have installed oracle 10g database on windows.so how should i resolve this error so that i can connect to the database as 'sys as sysdba'.

View 5 Replies View Related

SQL & PL/SQL :: Cannot Create Materialized View - Insufficient Privileges

Aug 3, 2010

i am trying to create materialized view but it give me insufficient privilege i give it all grant and i run it from sys it give the same.

View 3 Replies View Related

SQL & PL/SQL :: Unable To Query Dba_tab_cols / Insufficient Privileges?

Oct 29, 2010

I have installed oracle 10g express edition, and created 2 users

1. DBA
2. HR -- is the normal application developement user.

When i issue the following query from DBA user, it works.

select * from dba_tab_cols; -- works fine for DBA user
select * from dba_tab_cols; -- doesnot work for HR user - invalid table name

When i try to issue the previage from DBA user like,

grant select on DBA_TAB_COLS to hr; -- DBA user says like, insufficient privileges.

View 6 Replies View Related

Server Administration :: ORA-01031 - Insufficient Privileges?

Feb 7, 2012

I can not login my db using tnsname,how can i do?

SQL> connect / as sysdba
Connected.

SQL> connect sys/sys@dup_oracl as sysdba
ERROR:
ORA-01031: insufficient privileges

View 1 Replies View Related

Server Administration :: Insufficient Privileges To Other User

May 21, 2011

WHEN I CONNECT TO SCOTT OR ANY KIND OF USER (EXCLUDE SYS)I CONNECT EASILY BUT PROBLEM WITH SCOTT OR OTHER THEY CANNOT BE
SHUTDOWN THE DATABASE AT THAT TIME THE ERROR ARISES ARE

ORA-01031: insufficient privileges SO OUR DATABASE CANT BE SHUT IT DOWN.

View 5 Replies View Related

PL/SQL :: Getting ORA-01031 / Insufficient Privileges Error When Created MV?

Jun 21, 2012

I am getting a strange error while trying to create an MV.

<b><p>
Error report:
SQL Error: ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SNAPSHOT_UTL", line 1613
ORA-06512: at line 1
01031. 00000 - "insufficient privileges"

*Cause:    An attempt was made to change the current username or password without the appropriate privilege. This error also occurs if

[code]....

View 4 Replies View Related

SQL & PL/SQL :: Procedure To Get Any DDL Done Against Sample HR Schema - Insufficient Privileges

Jul 8, 2012

I am new to Dynamic SQL..I create a procedure to get any DDL done against sample HR schema as follows.it goes well! Now when i try to test my procedure with some DDL command passing to the procedure i've created..strange! oracle throws an error as in the /*ERROR!!!!*/ block..

I don't understand why i am facing such an error..

/* Product an Version on my machine */
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
[code]...

View 7 Replies View Related

SQL & PL/SQL :: Grant Privileges Remotely

May 25, 2010

Say we have db1 and db2 two databases installed on two different servers.For internal needs, I have to insert some data from a table t1 on db1 to a table t2 in db2. This can be done by issuing from a user on db1 :

insert into t2@dblink2
select *
from t1;

where dblink2 is a correct database link that points to u2 (the t2 owner for example) on db2.

Now what I want to do is to grant privileges remotely.Is there a way to issue somthing like

execute@dblink2 'grant select on t2 to public';

It would be like u2 has issued the statment...

I don't want to use OS scripts (.sh or .bat).

View 14 Replies View Related

Insufficient Privileges Error While Updating Through Stored Procedure

Oct 2, 2008

I have a update statement that works fine when its run in SQL window but when i try it as part of a Stored Procedure its giving me the Insufficient privileges error on that table.

If i dont have privilege to update that table wont it not let me update while doing it outside the stored procedure as well?

View 4 Replies View Related

Server Administration :: Insufficient Privileges While Connecting (sys As Sysdba)

Apr 2, 2013

SQL> conn sys as sysdba
Enter password:
ERROR:
ORA-01031: insufficient privileges

The oracle 11g installed in eucalyptus cloud..

In the same server I can connect as a different user 'd6' but not as sysdba.

View 7 Replies View Related

SQL & PL/SQL :: Error When Create Synonym / ORA-01031 / Insufficient Privileges

Mar 13, 2013

from sysdba i grant to my user hospital creat any synonym but give me this error why ?

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> grant create any synonym to hospital
2 ;

Grant succeeded.

SQL> conn hospital
Enter password:
Connected.
SQL> create public synonym Sur
2 for surgeries;
create public synonym Sur
*
ERROR at line 1:
ORA-01031: insufficient privileges

View 9 Replies View Related

Replication :: Create Materialized View - Insufficient Privileges

Feb 14, 2008

CREATE MATERIALIZED VIEW Matview1
NOLOGGING
NOCACHE
NOPARALLEL
REFRESH COMPLETE ON DEMAND
START WITH sysdate
NEXT sysdate + 1
WITH ROWID
ENABLE QUERY REWRITE AS
select Query;

if i run select query it works fine .. also the user has create materialized view and query rewrite privs .. not sure why i am getting insufficient privileges error still ..

View 1 Replies View Related

SQL & PL/SQL :: Getting ORA-01031 / Insufficient Privileges Error When Create A View?

Mar 12, 2013

create view TodaySurgeries
as select s.surgery "Surgery", p.full_name"Patient Name",e.full_name"Doctor Name",
f.floor_id"FloorID",r.room_id"RoomID",
to_char(s.surgery_date,'dd-mm-yyyy hh24:mi:ss')"Surgery Date"
from floors f,rooms r,employees e ,patients p, surgeries s
where f.floor_id=r.floor_id
and p.patient_id= s.patient_id
and r.room_id= s.room_id
and s.doctor_id= e.employee_id

I had been granted to the user grant session and resource

but the error is

create view TodaySurgeries
*
ERROR at line 1:
ORA-01031: insufficient privileges

View 7 Replies View Related

PL/SQL :: Error While Compiling Procedure / ORA-01031 / Insufficient Privileges

Jan 22, 2013

how to grant compile access for stored procedures..? I got execute access so that i can execute procedure from another user but not able to save/modify once i edited.

i am trying to compile using SQL developer. Error Message :Error: ORA-01031: insufficient privileges

View 5 Replies View Related

Data Guard :: Insufficient Privileges While Connecting To Standby?

Aug 28, 2012

i am trying to configure a standby on grid infrstructure, on standby machine when i copied password file from primary i gave it the name orapwdg2 as dg2 is my sid for standby, on primary sid is dg1 and passwd file there is orapwdg1 why it is saying insufficient privileges, i am easily able to connect to primary with dg1 on primary, but not here on standby, everything is set oracle home sid everything but why insufficient privileges

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

[code]....

View 16 Replies View Related

Security :: ORA-01929 / No Privileges To GRANT

Jun 28, 2011

When i try to grant all privilege to a object i get error

SQL> GRANT ALL PRIVILEGES ON tab1 TO PUBLIC;
GRANT ALL PRIVILEGES ON tab1 TO PUBLIC
*
ERROR at line 1:ORA-01929: no privileges to GRANT

how to check the user has privilege to use grant privilege or grant all privileges to object and what privileges exist in ALL PRIVILEGES.

View 5 Replies View Related

Security :: Grant Privileges To A User?

Dec 10, 2010

I am a java developer and would like to install data locally in my computer for testing purpose.

I login as sysdba in sqlplus, created a user.

In the installation documentation it said :

This user should have at least the following privileges.

CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE PUBLIC SYNONYM, CREATE USER, CREATE ROLE, CREATE SEQUENCE, CREATE SESSION CREATE TRIGGER, AND CREATE TYPE.

If you choose, you can grant DBA role to this user.

Note: Coeus Schema owner should have CREATE USER and ALTER USER right explicitly granted to it, not through a role like DBA.

1)How to grant all this prvileges to that user ?

2)How this user will have CREATE USER and ALTER USER right explicitly granted to it, not through a role like DBA ?

View 5 Replies View Related

Client Tools :: Insufficient Privileges Error While Using Copy Command

May 11, 2012

When i try to copy a table within the database i get insufficient priv error.

SQL> COPY FROM JACK@JACK -
> CREATE T -
> USING SELECT * FROM JACK.T;
Enter FROM password:

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)

ERROR:
ORA-01031: insufficient privileges

what privilege is required to perform this copy command ?

View 2 Replies View Related

PL/SQL :: Privilege Required For (as Of Timestamp) Clause / ORA-01031 / Insufficient Privileges

Jan 28, 2013

When I try following SQL to retrieve records in some table XXXXX in the past point in time:

select * from XXXXX as of timestamp systimestamp-(6/24);

I am receiving following error:

ORA-01031: insufficient privileges

Without "as of timestamp" clause, it does run fine.Which privilege do I have to receive for above to work?

View 3 Replies View Related

SQL & PL/SQL :: Grant Privileges To Subprogram Via Role - Should Not Work

Jun 8, 2010

I bought Selftestsoftware for 1z0-147 for 9i and 10g. Selftestsoftware is endorsed by Oracle, should be high quality.

But its below sample question and answer seem to be wrong: It says that privilege for subprogram can be granted via role. But from Urman 9i book: the grant the execute privilege must be done explicitly and not through a role.

Did Selftestsoftware made a mistake? Or the question did not mention or assume that the subprogram is based on invoker rights not definer right?

Quote:
Question: All users in the HR_EMP role have UPDATE privileges on the EMPLOYEE table. You create the UPDATE_EMPLOYEE procedure. HR_EMP users should only be able to update the EMPLOYEE table using this procedure. Which two statements should you execute? (Choose two.)

GRANT UPDATE ON employee TO hr_emp;
GRANT SELECT ON employee to hr_emp;
REVOKE UPDATE ON employee FROM hr_emp;
REVOKE UPDATE ON employee FROM public;
GRANT EXECUTE ON update_employee TO hr_emp;

Explanation:
The two statements you should execute are:
REVOKE UPDATE ON employee FROM hr_emp;
GRANT EXECUTE ON update_employee TO hr_emp;

Unless you are the owner of the PL/SQL construct, you must be granted the EXECUTE object privilege to run it or have the EXECUTE ANY PROCEDURE system privilege. By default, a PL/SQL procedure executes under the security domain of its owner. This means that a user can invoke the procedure without privileges on the procedures underlying objects. To allow HR_EMP users to execute the procedure, you must issue the GRANT EXECUTE ON update_employee TO hr_emp; statement. To prevent HR_EMP users from updating the EMPLOYEE table unless they are using the UPDATE_EMPLOYEE procedure, you must issue the REVOKE UPDATE ON employee FROM hr_emp;

All of the other options are incorrect because they will not meet the specified requirements.

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved