I am not able to select from the VIEW while connecting with the Schema who is the owner itself:
FX@db > select * from B_UTIL
select * from B_UTIL
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-02063: preceding line from FXDB2TST
Describe works but select does not. Even I connected with SYSDBA and performed SELECT * FROM FX.B_UTL but it again gives the above errors. 11.2.0.2 on RHL.
When i want to connect inro database by Following:
# sqlplus /nolog
sql>conn / as sysdba
Insufficient privilege.
What are the reasons.
I want to create new database and i follow the following steps.
C:SET ORACLE_SID=ASIM
ORAPWD FILE=ORAPWDASIM.ORA PASSWORD=ASIM ENTRIES=6
ORADIM -NEW -SID ASIM -STARTMODE AUTO
THE TRY TO CONNECT TO SQLPLUS
SQLPLUS /NOLOG
AFTER THAT I WRITE THE CODE TO CONNECT THE SYS
Connect sys/ASIM as SYSDBA
BUT IT DISPLAY ERROR ORA-01030, INSUFICENT PREVILAGES.
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?
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]...
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?
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 RelatedCREATE 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 ..
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
I have 2 users in my oracle DB. They both have very different privileges and they both have too many different privileges. Now I want to grant user 1 the same privilege that user2 has while user1 keeps his existing privilege. How can this be done without manually comparing their difference and manually grant user 1 each privilege that he doesn't have? Or second option, can I override user 1's privilege with user 2's privileges?
View 2 Replies View RelatedAs we all know, privileges granted to a user through role are not visible from within a stored procedure. What is the reasoning behind this design?
Moreover, privileges granted through role are visible from anonymous PL/SQL block. Why such discrimination between anonymous PL/SQL and stored PL/SQL?
I have a view in schema 'sales', let say the view name is view_test.
This view sales.view_test uses two base tables, one from schema 'products' and other from its own schema 'sales'.
So in sales schema the view defination is,
create or replace view view_test
as
select * from t_test, products.t_products
where t_test.id=t_products.id
The view got created, I have granted select permisson with grant option on tables t_test, products.t_products and view_test to another user 'master'.But when i query the view from master schema I get an error saying that the view does not exists, upon analysis, I found one of the base table in the view defination has no schema name mentioned (select * from t_test, products.t_products --here only t_test mentioned rather sales.t_test), I feel because the table t_test belongs to the same schmea of the view, they did not mention the schema name. If not, does it mean that we need to always specify the schema names in the view defination for the base tables.
I've 2 schemas namely department, hrNow department schema is having one table EMP . And a view is created on that table with the name R_EMPSchema Name :
departmentTable Name : EMPView Name : R_EMPGRANT SELECT ON R_EMP TO HR ; In HR schema a synonym has been created on view R_EMP with the name R_EMP .CREATE OR REPLACE SYNONYM hr.R_EMP for department.R_EMPSchema Name : hr Synonym Name: R_EMP
Now I want to rename the view name R_EMP to V_EMP from HR schema only So, I tried the folloiwng syntax in HR schema (All the operations should be done from this schema only )[code] RENAME department.R_EMP to department.V_EMP ;
Error: ORA-01765: specifying table's owner name is not allowed[/code] How to change the syntax to make the statement work from HR schema .
Any tool or way to view the schema and relations between tables of an Oracle database?
View 1 Replies View RelatedI have a list of materialized views in schema A. I want to create a refresh group and then refresh it from Schema B (Dynamically-run time based on some criteria). What Grants are necessary on schema B in order for it to be able to create and refresh the groups on Materialized views in Schema A.
I know that one of the Options is to, GRANT ALTER ANY MATERIALIZED VIEW as a SYS user. But I do not have any SYS privileges.
I want to import a schema from one database schema to another schema b from db STBTST to STATST and from schema CMSSTAGINGB to CMSSTAGINGA
I first want to test this to my own schema (mvanmannekes) CMSSTAGINGA is filled at the moment.
So i've created a dump from STBTST-CMSTAGINGB For importing im using this statement:
impdp mvanmannekes/password schemas=cmsstagingb remap_tablespace=cmsliveb_data:cmslivea_data
remap_tablespace=cmsliveb_index:cmslivea_index
remap_schema=cmsstagingb:mvanmannekes directory=expdp_dir dumpfile=cmstagingb.dmp
I'm getting this:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "MVANMANNEKES"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "MVANMANNEKES"."SYS_IMPORT_SCHEMA_01": mvanmannekes/********
schemas=cmsstagingb remap_tablespace=cmsliveb_data:cmslivea_data
[code]....
A function returns the metadata of named objects (Directories, Users, Tablespace....) in the form of DDL. When i execute the function in the schema having the privileges of CONNECT, RESOURCE, DBA, SELECT ANY TABLE, UNDER ANY VIEW AND EXECUTE ANY PROCEDURE, function returns the empty clob without any error. But he same function created and executed in the User having SYSDBA privilege, we get output.how to get output without SYSDBA privilege ?
CREATE OR REPLACE FUNCTION SCHEMA.DBLINK
RETURN CLOB
AS
v_meta_handle NUMBER;
v_meta_handle_trans NUMBER;
V_DOC CLOB;
V_LOB CLOB;
[code]....
I have a stored procedure which when executed creates a user and grants some roles to the user and also makes certain roles DEFAULT using "alter user"
The issue comes when i execute the procedure.
I have a User who has a role and this role has the "alter user" privilege, for example lets say that the user1 has the role ABC, and role ABC has the "alter user" privilege.
SQL> grant alter user to ABC;
Grant succeeded.
SQL> grant ABC to user1;
Grant succeeded.
Now, when i run the stored procedure as the user1, it gives me an error on a line saying "insufficient privileges", when i check the line, its this line:
alter user user1 DEFAULT ROLE "ROLE1", "ROLE2"
But as far as i know the user1 has the "alter user" privilege
I want to make those two roles ROLE1, ROLE2 DEFAULT because i don't want the other roles ROLE3,ROLE4 to be default, as you know if there are many roles and if we alter user with default for certain roles other roles become DEFAULT=NO.
So i get the error at "alter user" statement though the user has the "alter user" privilege, what do you think might be wrong?
OR is there anyway to grant roles to the user with default=NO option?
I have created a role, when i try to grant privilege to that role, it give me insufficient privilege error. After granting privileges to this role, i have to grant this role to other. what type of privileges should i have.
Create Role cb_select;
Grant select on atable to cb_select; (Got error)
User Scott having DBA privilege
create or replace procedure pt is
l_count integer:=0;
v_sid varchar2(1000);
begin
SELECT name
INTO v_sid
FROM v$database;
dbms_output.put_line('SID='||v_sid);
end pt;
Warning: Procedure created with compilation errors.
SQL> sho err
Errors for PROCEDURE PT:
LINE/COL ERROR
-------- -----------------------------------------------------------
5/1 PL/SQL: SQL Statement ignored
7/9 PL/SQL: ORA-00942: table or view does not exist
SQL>
SQL> select name from v$database;
NAME
---------
ORCL
I can't access v$parameter,or v$database from a procedure?
I logged in as system and I just execute below script, however Im getting error "SQL Error: ORA-00942: table or view does not exist 00942. 00000 - "table or view does not exist""
GRANT execute ON UTL_FILE TO USER1;
How to grant privilege on ult_file?
how will i know if i have execute privilege on a package which is in user?
View 6 Replies View RelatedThere two users a and b,and the table b.test_part.And one procedure under a ,text like below:
create or replace procedure a.sp_test
is
vs_sqls varchar2(32767);
begin
vs_sqls:='alter table b.test_part truncate partition p_day';
execute immediate vs_sqls;
end;
now,i have to grant drop any table to a.but in fact,i prefer to drop the special one table "b.test_partany" rather than any other table.how ? no by trigger!
How to know what object/tab privileged a user have ? without connecting that user and using USER_TAB_PRIVS by connecting on that user I mean on DBA table .
View 0 Replies View RelatedI wonder if exists a privilege, that i could grant to a user, just to run a specific function.
I searched in dba_sys_privs something about it but, returned nothing.
13:38:10 brunos@fastora1> select * from dba_sys_privs where privilege like '%FUNCTION%';
GRANTEE PRIVILEGE ADMIN_OPTION
I grant sysdba privilege to user1. After that i connected with user1. But i could not shutdown the database.
View 3 Replies View Relatedwhat privilege is require for a user to execute explain plan? I get below error while try to execute explain plan.
SQL> explain plan for SELECT /*+ FULL(t) */ COUNT(*) FROM "DREAM"."CONSUMER.TAB" t WHERE ROWNUM <= 1000000;
explain plan for SELECT /*+ FULL(t) */ COUNT(*) FROM "DREAM"."CONSUMER.TAB" t WHERE ROWNUM <= 1000000
*
ERROR at line 1:
ORA-01031: insufficient privileges
I connect to an Oracle-10g database using OEM, and if I click to see the list of users, roles, storage options, ... everything is ok, but if I click to see the list of schemas, I get "1031: insufficient privileges" error.
The user account I use has the SELECT_ANY_DICTIONARY privilege and SELECT_CATALOG_ROLE role granted, and if I try to do "select * from dba_tables" in the SQL*Plus, I get a result.
The matter is even more strange because, if I use the SYSTEM account, I get the same error (!!!).
Do I need any other privilege/role to see the user's schemas with OEM?
1.How to check a user has 'create table' privilege?
2.how to check a user has privilege to grant 'create table' privilege to other user ?
What privilege is required to gather table statistics using dbms_stats ?
View 2 Replies View Related