REVOKE ROLE - Disable All Privileges?

Mar 1, 2012

I am observing a strange behavior for the ROLES:

The DBA granted the RESOURCE role for my user USER1:

GRANT RESOURCE TO USER1;
The USER1 logged and activated the role RESOURCE.
CONN USER1/USER1
SET ROLE RESOURCE;

... it has some privileges in database ( UNLIMITED TABLESPACE for example that grant to him create table in any tablespace).

Now I created a new tablespace, but the user1 cannot use this new tablespace.So I will to revoke the RESOURCE for the USER1:REVOKE RESOURCE TO USER1;

It seems ok, but the user USER1 can still create tables in the new tablespace.I repared that UNLIMITED TABLESPACE still active, and the REVOKE RESOURCE just will have effect in the next LOGON of the USER1.

How can I REVOKE the ROLE and disable all the privileges from it immediately?

View 3 Replies


ADVERTISEMENT

SQL & PL/SQL :: Find Out Privileges On A Role

Oct 20, 2010

How can i find out or list all the privileges that were given to a role with sqlplus cmd?

View 2 Replies View Related

PL/SQL :: Viewing Privileges For A Role

Sep 9, 2013

I have a role R.I want to see what privileges have been granted to this Role R. Where should i query? 

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionPL/SQL Release 11.2.0.3.0 - Production"CORE    11.2.0.3.0    Production"TNS for Solaris: Version 11.2.0.3.0 - ProductionNLSRTL Version 11.2.0.3.0 - Production

View 2 Replies View Related

PL/SQL :: ROLE Privileges In Oracle

Aug 1, 2012

How CONNECT Role in oracle works.

I have my schema "SchemaABC" with ROLES created as CONNECT and RESOURCE. Also it doesn't have DBA role (to say).

SQL> Select * from  User_Role_Privs;

USERNAME                       GRANTED_ROLE                   ADMIN_OPTION DEFAULT_ROLE OS_GRANTED
------------------------------ ------------------------------ ------------ ------------ ----------
SCHEMAABC                       CONNECT                        NO           YES          NO
SCHEMAABC                       RESOURCE                       NO           YES          NO

SQL>Currently we are able connect to this schema through code as well as from PL/SQL Developer. And uses it for all coding purposes like Create, Insert, Update, Stored Procedures, functions, triggers etc.

Now my question is, can this schema be changed to a role/privilege where all the above said activities can be done through code, but not from PL/SQL developer (making it to Read-only)? Yet, I will be using the same schema Name in code as well to connect from PL/SQL Developer.

I understand Connect role has big share in this. But if I remove the connect privilege(role), then will that work when I connect to it through code?

View 1 Replies View Related

SQL & PL/SQL :: Role Dependency - Additional Privileges

Aug 6, 2012

I want to find role dependency on another role.

1. If its a basic role (made of priviliges), what data dictionary view I should query?

2. If the role is made of some other role and additional priviliges, what query I should fire to find that?

3. Role is granted to which users?

View 15 Replies View Related

SQL & PL/SQL :: How To Know Schemas In A Role And Privileges On Objects

Jul 14, 2011

I have a role mfg_grp .I want to know, to whom the role is granted and which objects and which privileges on those objects are assigned to this role.

View 4 Replies View Related

SQL & PL/SQL :: Grant Privileges To Subprogram Via Role - Should Not Work

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

Security :: Any Role Where Exp_full_database Role Is Contained In It?

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

SQL & PL/SQL :: Revoke Fails Even With SYS?

May 17, 2010

I want to revoke some privileges (given from user, say A, on table TABLE to user B) with SYS, and i've got an error : It says that I can't revoke a privilege that I didn't give.

In DBA_TAB_PRIVS, there's no information about that : I can't know who gives the privilege (when I try to perform the REVOKE statment with OWNER, GRANTOR, I've got the same error).

View 20 Replies View Related

SQL & PL/SQL :: Grant Permission Revoke?

Dec 16, 2011

Is there a way to find out who and how was the GRANT permission revoked from user.Why i am asking is , i see a grant permission exist for a user and has been revoked later.

View 13 Replies View Related

SQL & PL/SQL :: Connect Role In Dba-role-privs?

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

Export/Import/SQL Loader :: Assigned To ROLE X Be Transferred To Role Y Via Datapump Import

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

Create A Role Like DBA Role?

Sep 20, 2012

is there a way to create a role just like DBA role?

View 6 Replies View Related

Role Of SGA And PGA In Oracle

Aug 19, 2005

what is the role of SGA and PGA in oracle . And want kind of functionality they provide the oracle. i not understand what is system global Area and Program global Area (SGA and PGA).

View 7 Replies View Related

SQL & PL/SQL :: Role Not Creating

Jun 3, 2010

If iam create a new role in scott/tiger user why not creating. can any one explan me.

SQL> create role test_role;
create role test_role
*
ERROR at line 1: ORA-01031: insufficient privileges

View 2 Replies View Related

SQL & PL/SQL :: Grant Privilege To Role

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

SQL & PL/SQL :: Granting Privilege To A Role

Aug 18, 2010

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)

View 7 Replies View Related

ODP.NET :: Minimum Role For GetSchema

Apr 16, 2013

I am trying to find out what is the minimum Oracle User Role required for the GetSchema command to work using ODP.NET and the Oracle.DataAccess library.

Dim cn As New OracleConnection(ConnectionString) 'Oracle.DataAccess.Client.OracleConnection Return cn.GetSchema("Tables")

View 0 Replies View Related

Clusterware :: GNS Vip Role Without Subdomain

Oct 23, 2013

By using GNS with sub-domain ,we will get dynamic allocation of VIP's & Scan IP from DNS sub-domain. This makes easier addition & deletion of nodes in the cluster.  But I have few doubt in GNS configuration without sub-domain.

In this configuration just GNS vip entry to be made in DNS to install Oracle 12C RAC with ASM flex.

1. What is the role of GNS vip without subdomain?
2. What is the necessity of GNS vip with ASM flex installation.

View 0 Replies View Related

PL/SQL Package Can't See Tables Granted Via Role

Mar 12, 2010

A Package cannot query a table that the package owner has permission to query via a role specifically:

There are schema_1 and schema_2

schema_1 owns package_1
schema_1 has select, update, insert on schema_2 via role_1
schema_1 CAN query schema_2
package_1 cannot access schema_2

(288/22 PL/SQL: ORA-00942: table or view does not exist)

How can this be resolved?

View 6 Replies View Related

SQL & PL/SQL :: ORA-01917 / User Or Role Does Not Exist

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

SQL & PL/SQL :: Grant Execute On More Than One Procedure To A Role

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

SQL & PL/SQL :: DML Sentences Role In Data Dictionary?

May 21, 2012

Changing Data dictionary by DML Sentences (e.g Insert, Delete, Update,...), Will be possible? Or just by DDL Sentences?

View -1 Replies View Related

Server Administration :: Number Of Role?

Dec 5, 2012

there restrictions in number of defining role in oracle?

View 2 Replies View Related

Security Role And Auto Permissions

May 13, 2013

I have created and role in my database and assign privileges as per following query.

Select 'Grant select on'||' user.'||object_name||' '||'to MyRole ;' from all_objects
where object_type in ('TABLE','VIEW')
and owner='username';

After granting role to new user everything work fine.I want to know a way to sync role with any newly created object.

Should I create a job that may execute above sql store results in a file and then execute to ensure all privileges are up to date for role or there any other optimal way exist ?

View 4 Replies View Related

How To Not Let User With DBA Role To Delete Record From Sys.aud$

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

Forms :: Create Menu Role With User

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

Security :: How To Change DEFAULT-ROLE Of A User

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

Forms :: Role Based Menu Security

May 2, 2010

Direct me on step by step to create secure menu based on roles.

View 2 Replies View Related

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 View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved