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


ADVERTISEMENT

What Privileges Other Schemas Have On Objects Of Schema A

May 19, 2013

i have schema A and i want to check who(which schemas) can access schema A's objects and what privileges other schemas have on objects of schema A.

View 7 Replies View Related

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

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 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 :: 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

SQL & PL/SQL :: Create Two Objects In Different Schemas In A Single Database?

Jul 25, 2010

Is it possible to create two objects in different schemas in a single database.

For example in Schema A, table "T1" is there, is to possible to create a table with the same name "T1" in Schema B.

View 4 Replies View Related

Client Tools :: Compare Schemas And Database Objects Using TOAD And SQL Developer

Apr 17, 2013

My primary objective was to compare objects in schemas in two different databases and find out the differences, Execute DDL's in the database where objects are missing and syn schemas in two different databases. So I need to compare schemas in databases. make a comparison of database objects existing in schemas in two different databases. I'd like to see if I can get a list of pro and cons between Toad and SQL Developer for comparing schemas pros and cons. navigation in SQL Developer to compare schemas.

Connect to Source
Connect to Target
Compare schemas with different object types
Find out differences
Generate DDL's for the missing objects or for the objects in difference report
Run them in missing instace(Source/Target)
Make sure both are in sync.

View 2 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 :: 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

SQL & PL/SQL :: How To Run Query Between 2 Schemas

Jan 8, 2013

Is there any way to run a select command between 2 different schemas without using DB links ?

View 1 Replies View Related

SQL & PL/SQL :: Not Getting Output For Other Schemas?

Feb 2, 2010

One function returns table metadata in xml form. This works for current schema but not for other schemas. Is it a privilege problem?

my code is..

CREATE OR REPLACE FUNCTION TEST.F_DBEG2
RETURN XMLTYPE
AS
Handle NUMBER;
V_OUTPUT_TOTAL XMLTYPE;
V_CNT NUMBER := 0;
BEGIN

[code]....

but when i select the object of another schema, got output.

SELECT * FROM TEST_NEW.DEPT;

Which privilege i need?

View 9 Replies View Related

SQL & PL/SQL :: Get Metadata Of Different Schemas

Jun 6, 2012

I'm trying to get the Create-Statements of all tables of several schemas:

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name,'<schmema>')
FROM (SELECT TABLE_NAME, OWNER FROM SYS.ALL_TABLES WHERE owner='<schema>') u

Is there a way to create a loop to handle several schemas listed in a array, for example?

View 10 Replies View Related

SQL & PL/SQL :: Find Difference In Two Schemas?

Feb 25, 2013

I want to find difference between the objects(tables(columns,constraints), views, sequences) in two schemas. One schema is staging and one is development . In development lot of changes are made , Is there a script or a opensource tool which will bring out the difference in columns, constraints in each table and sequences , views etc.

View 2 Replies View Related

Server Utilities :: Export Schemas From 10g To 11g?

Apr 27, 2011

I am trying to export schemas from 10g to 11g. The NLS_CHARACTERSET for 10g is WE8ISO8859P1 and the NLS_CHARACTERSET for 11g is WE8MSWIN1252. Is it fine or do I need to change the character set, so that I will be able to successfully do the export/import?

View -1 Replies View Related

SQL & PL/SQL :: Retrieve List Of Tables With Same Name From Two Schemas

Sep 16, 2013

I need to retrieve the list of tables with same name from 2 different schemas.

View 10 Replies View Related

SQL & PL/SQL :: Create A Trigger On Multiple Schemas

May 25, 2011

I have following schemas in my database scott,sh,hr...I created a triger on a schema scott with the following method

1. sqlplus / as sysdba

2. CREATE TABLE LOGIN_AUDIT_INFO_ALL (
operation VARCHAR2(30),
obj_owner VARCHAR2(30),
object_name VARCHAR2(30),
sql_text VARCHAR2(64),
attempt_by VARCHAR2(30),
attempt_dt DATE);

3.CREATE OR REPLACE TRIGGER login_audit_prod_schemas
AFTER CREATE OR ALTER OR DROP
ON SCOTT.SCHEMA
[code]....

But it don't fulfill my requirement. trigger is fired when scott perform any action. but my requirment is that trigger should be fired when create action is performed on scott by hr,sh or scott. i also want a single trigger which fulfill the requirement.

View 8 Replies View Related

Query Multiple Schemas Against Default_tablespace?

Feb 6, 2013

i am trying to generate a SUM from the individual schemas to put on this report but i can't seem to get it right...

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=WINDOWS-1252">
<meta name="generator" content="SQL*Plus 11.2.0">
<TITLE>Launch page </TITLE></head>
<body>
<table border='1' width='90%' align='center' summary='Script output'>

[code].....

View 1 Replies View Related

PL/SQL :: Unique Schemas - Users List

Aug 5, 2013

find the below task. I have unique schemas. and every schema we have many tables.I would like to know

1. I have Scott schema and here I have 10 tables. EMP,DEPT,SALGARDE ( and these tables having different priviliges).

2. another schema MACIN having table DEPT, SALS, GRADE ( having different priviliges) Now I would like to know Schema 1 having permissions to see the tables and he is doing some modifications,inserting,deleting .... on tables containing in MACIN schema.like this it is happening in vice verse in max schemas. now I would like to know who all are having access to login to another schema and what are the priviliges they have till now on what  tables they applied changes...is it possible to figure out? if yes how can we?

View 6 Replies View Related

PL/SQL :: Connect To Pdb To Unlock Old Schemas Like Scott And Hr In 12c

Sep 22, 2013

After successfully installing the 12c, how can I connect to pdb to use the scott or hr schemas?By default when I connect as sys or system from sqlplus it is connecting to orcl not to pdborcl.also how can we make scott connection in sql developer.

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

Comparison Between Toad And SQL Developer To Compare Schemas

Apr 17, 2013

My primary objective was to compare objects in schemas in two different databases and find out the differences, Execute DDL's in the database where objects are missing and syn schemas in two different databases.

So I need to compare schemas in databases. Which tool will be user friendly to make a comparison of database objects existing in schemas in two different databases.

I'd like to see if I can get a list of pro and cons between Toad and SQL Developer for comparing schemas pros and cons. How to make a comparison. I have some idea on using TOAD but was not familiar with SQL Developer.

Below is my requirement:-

Connect to Source
Connect to Target
Compare schemas with different object types
Find out differences
Generate DDL's for the missing objects or for the objects in difference report
Run them in missing instace(Source/Target)
Make sure both are in sync.

View 2 Replies View Related

Enterprise Manager :: OEM And Privilege To See Users Schemas

Apr 19, 2010

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?

View 2 Replies View Related

SQL & PL/SQL :: Comparing Schemas - Content (data) Identical

Jan 20, 2011

I have two schemas with 149 tables in each schema, what I need to do is to prove that the content(data) between the two schemas is identical. I know that all the table names between the two schemas are the same, just need to prove that there is no difference in data.

So the query needs to prove that Schema A content = Schema B content

I know I cant do a simple select from Schema A.tab1 minus select Schema B.tab1 but since there are 159 tables, I am not sure if this is an efficient way of doing it.

View 14 Replies View Related

ORA-65096 - Installing Oracle 12cR1 Example Schemas

Oct 13, 2013

Database: 12cR1, 12.0.1.0, x64System: Oracle Linux 6.4, x64  

I installed a simple starter database during the installation of Oracle Database 12cR1 with the PDBORCL option selected (default) and would like to install the Oracle Example data, such as users HR schema, etc.

according to URL....IDENTIFIED BY hr  *ERROR at line 1:ORA-65096: invalid common user or role name  

Perhaps the scripts were not designed for container and plug-able databases. I did the following: 

alter pluggable database pdborcl open;alter session set container=PDBORCL;@?/demo/schema/human_resources/hr_main.sqlUser altered. Grant succeeded.Grant succeeded.Connected.GRANT execute ON sys.dbms_stats TO hr

*ERROR at line 1:ORA-01917: user or role 'HR' does not exist 

I could probably create a normal database, but I wonder if there is any other option without having to modify the setup scripts. 

View 2 Replies View Related







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