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""
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?
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?
Actually I was performing "grant all on table_name to user_name;" to grant the privilege but 1 of my friends suggested "grant all on any table to user_name;"
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 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)
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?
There 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!
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.
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 .
what 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?
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.
I'm new to oracle DB,i've been given access to Oracle as a user. when i try to create a table under my default schema i get the insufficient privileges error.
How do i check from the system views if i have create table privilege under my own schema?
wanted to grant dbms_scheduler permission to the system user in oracle 11.2.0.3,but it is showing insufficient privileges, but my System has Sysdba rights.
SQL> SELECT * FROM v$pwfile_users WHERE sysdba='TRUE'; USERNAME SYSDB SYSOP SYSAS ------------------------------ ----- ----- ----- SYS TRUE TRUE FALSE
[code]....
Revoke succeeded.
SQL> conn system Enter password: Connected.
[code]...
while i ran this query " SQL> select * from session_privs;"
for system user i have 202 rows of different privileges,but the same query for sysdba has 208 rows...
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
if a user have alter table gant but could not alter .. what additional grant it need
SQL> alter table HRS_PERS_FIELDS_INC modify(PER0000252 NUMBER(19,3)); alter table HRS_PERS_FIELDS_INC modify(PER0000252 NUMBER(19,3)) * ERROR at line 1: ORA-00942: table or view does not exist
I have a schema TEST1 and it has a view 'TEST_VIEW'. I granted read only permission on the view to TEST2 schema. grant select on TEST_VIEW to TEST2.
But TEST2 is used by an application which requires to read view metadata and then sync with application afterwords start reading. Is there any special permission to grant to access metadata of the view / table.