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)
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 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?
Is there any Role where 'exp_full_database' role is contained in it? or it is compulsory to grant to the user for exporting objects.,
View 2 Replies View RelatedBut what I noticed is that user HIEL has a connect role but YONC does not. (Result set is below) Have searched for information but did not find what I was looking for. I did read something about backward compatibility.I'm leaning towards that thought since the other user YONC does not have this role.
Database: 11g R1
SQL> select * from dba_role_privs where grantee in ('YONC', 'HIEL') order by grantee;
GRANTEE GRANTED_ROLE ADM DEF
--------------------------- --------------------------- --- ---
YONC WCAIMS_INQUIRY NO YES
YONC ENDUSER NO YES
HIEL WCAIMS_CSR_ADDR NO YES
[code].........
i have user with the name 'Rob' and this user has been assigned a role 'MY_SRC_ROLE' . I developed a table under rob schema and granted access to this table via role GRANT DELETE, INSERT, SELECT, UPDATE ON rob.emp TO MY_ SRC_ ROLE; I have 100 more users & they have been granted this role 'MY_SRC_ROLE'. These 100 users can now access emp table via Role 'MY_SRC_ROLE' without any issues. Now i took a datapump export & performed datapump import on target server which is also HP Unix with 11.20.3 .
On target server i have user 'JACK' and a role called 'MY_WORK_ROLE'. 5000 users have been granted 'MY_ WORK_ ROLE' on this server. I have used remap tablespace clause & remap schema clause in datapump import script. Once i performed an import , due to schema remap , i can see JACK now owns table 'emp', however grants are still not there, I tried searching on Google & oracle documentation, if somehow we can remap ROLE GRANTS also while doing datapump imp, but i couldn't find supporting syntax. can i assume datapump import is not capable to handle this particular scenario ? I was able to do it by manipulating sqlfile and replacing role name in that but i am looking for a sol. within datapump itself. how can grants assigned to ROLE 'X' be transferred to 'Role Y' via datapump import.
I have 3 users in one schema. One user is having all the tables, packages,triggers etc and this is the super user.
I want to access all the tables, packages etc without prefix the username in other two users.
I want grant object level privileges to some user so that he can view(select) any object that resides under another user.Not select any table option.
View 5 Replies View Relatedis there a way to create a role just like DBA role?
View 6 Replies View RelatedI have a question regarding how to grant create type and create procedure roles correctly.Currently if I do this:
create user vackar_temp identified by "123456789";
grant create type to vackar_temp ;
Then as vackar_temp:
create type temp_col as table of number;
I get the following error:
ORA-01031: insufficient privileges
I've also tried:
grant resource to vackar_temp ;
But that doesn't work either If I run this:
select
lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
(
/* THE USERS */
select
null grantee,
[code]...
I can see that the user does have the create type role:
User, his roles and privileges
------------------------------
VACKAR_TEMP
CREATE TYPE
RESOURCE
CREATE CLUSTER
CREATE INDEXTYPE
[code]...
oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"
I have created two new procedures. When I log in as ADMIN, I can execute these procedures.. but when i log in as WC_GUEST or WC_TECH, I cannot.. One of the procedure name is WCL_RPRTD_PARTS_REP_SEARCH, to which, I have given this: grant execute on WCL_RPRTD_PARTS_REP_SEARCH to WC_GUEST,WC_TECH;
Also, I have changed the ROLE of ADMIN, to the same role as these two users and tried.. it works fine.
Is there a way to grant rights to view a stored procedure without granting execute permissions?
View 2 Replies View RelatedA 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]....
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!
When i want to connect inro database by Following:
# sqlplus /nolog
sql>conn / as sysdba
Insufficient privilege.
What are the reasons.
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 .
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 RelatedI 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 want to extract roles and privileges DDL for tablespace using dbms_metadata.get_ddl. How to do it?
View 3 Replies View Relatedwhat privilege is required to run dbms_stats package for gathering table and schema statistics.
SQL> show user
USER is "JACK"
SQL>
SQL> select * from user_sys_privs;
no rows selected
SQL> select * from user_role_privs;
no rows selected
SQL> select * from role_sys_privs;
no rows selected
SQL> exec dbms_stats.gather_table_stats('JACK','EN1')
PL/SQL procedure successfully completed.
I revoke all the privileges from JACK user but still i am able to gather stats for a table.what privilege is require to gather stats.