Grant User Access To Role With Limited Exposure?
Oct 23, 2012
I have a role in my Oracle 10g instance like below:
GRANT ALTER USER TO <role_name> WITH ADMIN OPTION;
And this works fine for any user who has:
GRANT <role_name> TO <user>;
What I need is to limit <user> to only have the ALTER USER privilege to a set of users. Preferrably where the set of users are identified by a column value in a table, something like:
WHERE PeopleTable.InList = "YES"
Or maybe where set of users are defined by their membership in another role.
GRANT ALTER USER TO <role_name> FOR USERS IN MEMBERS_LIST_ROLE;
View 7 Replies
ADVERTISEMENT
Jun 3, 2010
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?
View 9 Replies
View Related
Aug 9, 2010
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"
Is there a way to grant EXECUTE on a group of procedures/functions/packages to a particular role , in a single statement ? Or we have to do it one by one... like
GRANT EXECUTE on event_main to role1,
GRANT EXECUTE on event_main2 to role1,
GRANT EXECUTE on event_main3 to role1,
View 2 Replies
View Related
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
Dec 6, 2010
i have created one user SVC_LAMR and created one ROLE - SVC_LAMR_ROLE.
i have attachd this role to abv user SVC_LAMR.
i need to grant SELECT privilege on some tables ( from some other schema TPAOWNER ) to this role.so that this user SVC_LAMR can access those tables lying in TPAOWNER schema.
for e.g.
SQL> show user
USER is "SYS"
SQL> grant SELECT on TPAOWNER.USER_APPLICATION to SVC_LAMR_ROLE;
grant SELECT on TPAOWNER.USER_APPLICATION to SVC_LAMR_ROLE
*
ERROR at line 1:
ORA-01031: insufficient privileges
But, if i connect this user: TPAOWNER, and give SELECT privilge directly to this role, it is accepting.
i.e.
SQL> conn tpaowner/*******
Connected.
SQL> grant select on USER_APPLICATION to SVC_LAMR_ROLE;
Grant succeeded.
SQL> grant SELECT on USERS to SVC_LAMR_ROLE;
Grant succeeded.
View 5 Replies
View Related
Aug 15, 2013
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.
View 4 Replies
View Related
Feb 9, 2011
I have two users say A and B. I have all the tables,views,indexes, types,procedures,packages etc. User B wants to access all the objects from user A.
View 4 Replies
View Related
Oct 5, 2013
How to give ROLE access to users in PL/SQL developer Tool..??
View 3 Replies
View Related
Feb 10, 2011
I have an Index in User1 schema. can i grant access on this Index to User2. if so, what privs i can give..?
for proc's we will give "Grant Execute on proce1 to User2". Like this how on Indexes..
View 3 Replies
View Related
Apr 23, 2011
I want to perform something like -
Conn xyz/passwordxyz
grant create procedure to 'xyz';
I want to give permissions to my current user 'xyz' , I am able to give permissions to user using system user but Is there any way to grant permissions to user 'xyz' while I am using 'xyz' user.
View 2 Replies
View Related
May 26, 2011
I just created a role as
CREATE ROLE XXXX;
Then tried to grant a privilege, but getting error as below:
grant alter on schema.table_name to XXXX;
ORA-01917: user or role 'MACH_ALTER_ROAMXDB' does not exist
View 4 Replies
View Related
Sep 20, 2012
we have certain users have DBA role assigned. of course they can delete records from sys.aud$.
we are trying to make this go away. we found that in DBA role, there is a role called DELETE_CATALOG_ROLE controls this. if we revoke this role from DBA role, user no longer able to delete records from sys.aud$ but the problem is as a powerful user who has DBA role, they can always grant this back to DBA role, or grant delete from sys.aud$ table directly to themself.
can we create a second role which just like DBA role, but with less privileges?
View 7 Replies
View Related
Jul 19, 2010
how to create menu with submenu according to user,
For Example :
Menu1 have got 02 SubMenu :
SubMenu1 just user1 see.
SubMenu2 just for user2.
And user3 could access SubMenu1 and SubMenu2.
View 1 Replies
View Related
Jul 13, 2011
I want to change the DEFAULT_ROLE column to NO for following user 'P10_DEMO'.
SQL> show user
USER is "BALA"
SQL>
SQL>
SQL> select name from v$database;
[code]...
how to do this ?
View 8 Replies
View Related
Oct 20, 2012
- we have user id parameter.can we update the parameter(:parameter.p_userid) before firing "WHEN NEW FORM INSTANCE TRIGGER"(when new form instance trigger contain code for tree node), for login another canvas as per user rights?
- i have created a login form in one canvas.
- also tree node Hierarchy form created on another canvas. now we want to login through login screen, after login only those forms should show in tree Hierarchy which users have rights. for this purpose we want to pass the parameter of userid before connecting to tree node form through the following query
SELECT COUNT (*)
INTO v_count
FROM usersinfo
WHERE usersname = :USERBLOCK.usernames AND passwords = :USERBLOCK.passwords ;
[code]...
Every thing is working but when we click on button in login form at that time parameter.p_userid will return null. because above code is define on button click in login screen, i cannot understand where i need to define the above code .
View 1 Replies
View Related
Oct 4, 2012
I switch to Oracle11g express and create user
CREATE USER LEO
IDENTIFIED BY xy
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
[code]...
and after login i check
select * from SESSION_ROLES
and i have none role if I set role all works fine. Why I doesn't have DEFAULT ROLE after login.
View 1 Replies
View Related
Jul 11, 2012
I have two user a and b. I have to Grant select update insert Delete Permission to all tables of user b To User a. how can it possible?
View 5 Replies
View Related
Nov 24, 2010
I am trying to create a script where a user can be granted all the roles that another user has. I created a script:
create user yyyyy identified by zzzz default tablespace ahspace temporary tablespace temp_data;
grant connect to yyyyy;
declare
cursor grantpriv is
select granted_role from dba_role_privs where grantee='xxxxx'; -- existing user
cursor_name integer;
statement varchar2(200);
Begin
cursor_name :=dbms_sql.open_cursor;
[Code]....
View 3 Replies
View Related
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
Jan 30, 2013
on 11G R2,
We want to grant truncate any table to a user.
How ?
We should create the following procedure ?
1. Create the procedure to truncate the table.
create or replace procedure truncate_table (
table_name varchar2,
storage_type varchar2)
[Code]....
grant execute on <procedure_name> to <user>Is it for only one table ? If yes how to do that for all tables ? Or the tables of a schema ?
View 6 Replies
View Related
Oct 22, 2011
I have a procedure in my schema. I have created a sys context name xyz for passing the date in that i am passing the sysdate to that context which is used in a view.i have used/called that procedure in form.
My problem is that when i am giving Grant select any dictionary to the user then form is compiled otherwise form is giving error procedure name must be declared. But for security reasons i don't to give select any dictionary to that user.
View 3 Replies
View Related
Jul 9, 2011
grant insert,update,delete,select on staging_tb1 to public;
What is public here? i know something about public user like it is users exist in the database .
View 5 Replies
View Related
Mar 21, 2013
i have to grant permission to a user on a package dbms_transaction.After i granted permission i am getting error:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_TRANSACTION", line 88
ORA-06512: at line 1
View 7 Replies
View Related
Dec 6, 2010
when a dba user'AA' try to access other user'BB' object it gives error pls-00201 identifier 'BB.function_name' must be declared. However the procedure of BB user are being accessible.
View 17 Replies
View Related
Aug 13, 2013
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 Related
Oct 31, 2012
I would like to know what happens when we fire:
grant all on any table to user_name;
and
grant all on table_name to user_name;
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;"
View 5 Replies
View Related
Sep 26, 2013
how to acess tables of another user from the sys user as dba in oracle 8 dont have the password but need to acess the table
View 1 Replies
View Related
Oct 10, 2012
But 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].........
View 4 Replies
View Related
Oct 17, 2010
The application user owns the application schema which owns all the database objects in this schema. Now 50 of our developers need access to this application schema but giving away the password for the application user is risk as "Drop user application cascade" and wipe off all the objects. The option i have is to create 50 separate users with tablespace as application and grant all rights to application schema.
View 9 Replies
View Related
Oct 18, 2013
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.
View 2 Replies
View Related
