PL/SQL :: How To Grant Privilege On User1 Based On Privilege Of User2
Jun 20, 2013
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?
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 created a user "user1" who has dba role.i wanted to create a another user "user2" with same privileges which user1 has. so i granted dba role to user2 but i dont want user2 to have "drop" privilege on any objects except his own objects.
User1 is having 10000 tables in his schema...How can i grant "select" on a all tables of a user1 to another schema(user2) so that in future when user1 will create tables , the user2 will have "select" access on those tables automatically.
I dont want user2 to have "select any table" privillege.
User2 should not have "drop" privillege on his own tables.
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?
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;"
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...
