How To Grant Select On All Tables Of User1 To Another Schema

Jan 18, 2012

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.

View 1 Replies


ADVERTISEMENT

SQL & PL/SQL :: Grant Sysdba Privilege To User1

Oct 8, 2010

I grant sysdba privilege to user1. After that i connected with user1. But i could not shutdown the database.

View 3 Replies View Related

Schema Permissions - Execute GRANT SELECT ON Xyz - Insufficient Privileges?

Sep 30, 2011

I have an automated process which runs on an Oracle 8i database server as user abc.This process creates views/tables in other schemas, on the same database server, which point to objects owned by the abc user.

The issue I'm getting is that when I try to execute GRANT SELECT ON xyz.view123 TO PUBLIC as the abc user, I get an insufficient privileges.I should add that the abc user created the xyz.view123 table/view.

What grants/priviliges or whatever do I have to do to the abc schema?

View 1 Replies View Related

SQL & PL/SQL :: Trigger - Automatically Grant Select Permission To ODI-MASTER Schema?

Jan 4, 2011

I have two schema named ODI_MASTER and ODI_WORK.Under ODI_WORK there are some tables like TEMP1, TEMP2
Further more when any new tables will create under ODI_WORK, then i need automatically grant select permission to ODI _MASTER schema.

for this purpose i choose trigger, and a Stored procedure.

CREATE OR REPLACE TRIGGER ODI_WORK.TRIG1
AFTER CREATE ON ODI_WORK.SCHEMA
ENABLE
call sp1 (ora_login_user)

[code]...

I searched a lot over blogs, if EXECUTE IMMEDIATE commands exist under Trigger it gets problem. Insert/update/delete statement on trigger seems no problem.

View 13 Replies View Related

SQL & PL/SQL :: Give Select Grant For Few Tables?

Jul 8, 2010

I created a user and granted connect,resource priviliges. I gave access to this user for only 5 tables. when i check it later, other tables are also given access. How can i avoid this and give access to selected tables.

View 9 Replies View Related

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?

View 2 Replies View Related

SQL & PL/SQL :: Give Grant To Schema Of Different Versions Of Oracle Databases?

Aug 8, 2012

I have a task assigned to "grant select on sys.link$" to a user which exists on 500 different databases.

This needs to be done in Oracle database.

View 6 Replies View Related

Forms :: Grant Select Any Dictionary To User

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

Security :: Grant To Select Session Details

Mar 13, 2006

Developers they use Toad and in that they want to use session browser option so that they can see the SQL running and open Cursor,other session specific details...So what grant/ permissions can be given to them so that the Schema they logged on can access those information.

View 4 Replies View Related

SQL & PL/SQL :: Grant Privileges For Select On View From Different Schemes

Oct 27, 2011

My need is to grant priveleges for select on view without granting on nested tables.

Actual problem is that I can grant privileges for particular user, but can't grant privileges for user group.

View 4 Replies View Related

Execute GRANT SELECT ON Xyz - Insufficient Privileges?

Sep 30, 2011

have an automated process which runs on an Oracle 8i database server as user abc. This process creates views/tables in other schemas, on the same database server, which point to objects owned by the abc user.

The issue I'm getting is that when I try to execute GRANT SELECT ON xyz.view123 TO PUBLIC as the abc user, I get an insufficient privileges.I should add that the abc user created the xyz.view123 table/view.

What grants/priviliges or whatever do I have to do to the abc schema?

View 2 Replies View Related

Server Administration :: Unable To Grant SELECT Priv To ROLE?

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

Client Tools :: Grant User Schema To Another User

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

Security :: Difference Between Grant All On Any Table And Grant All On Table_name

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

Create Another User User2 With Same Privileges Which User1 Has

Jan 24, 2012

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.

View 1 Replies View Related

Grant Select On V$session And Gv$session Directly?

Aug 28, 2013

Instead of giving permission directly to v$session and gv$session to a developer, Is there any other way to do the same?

View 3 Replies View Related

SQL & PL/SQL :: Create Tables From Different Schema?

Mar 18, 2012

I have created 2 different databases and each have their own schema user for eg we have database ALIVE with user allive and the other database RLIVE with user rllive , actually we have implemented new module in ALIVE database and in the process we created many tables , synonyms , index and other objects now we want to list out all the tables ,sequences as scripts which are not present in RLIVE and create them in RLIVE as new objects.

View 3 Replies View Related

PL/SQL :: Trigger From Other Tables Of Same Schema?

Jan 14, 2013

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit

i'm trying since some hours to get information from inside a trigger from another table of the same schema.My trigger table is PSE_BKB.NUM_PHANTOM_BP

First i tried simple solution ...

CREATE OR REPLACE TRIGGER PSE_BKB.NUM_TR_PHANTOM_BP
BEFORE DELETE OR INSERT OR UPDATE
ON PSE_BKB.NUM_PHANTOM_BP
REFERENCING NEW AS NEW OLD AS OLD

[code]...

The last version i tried was with a cursor definition inside the trigger as in the code-block below.For debugging purposes i've inserted a RAISE_APPLICATION_ERROR in the inner loop - see below. The variable v_obj_key is never set, like in all other variations i tried - i alway see the predefined 'gugus' from the declare section.

It seems oracle cannot read from other tables at this point. The :old.phantom_key is set (in this simple example there should come one obj_key back).

trigger:

CREATE OR REPLACE TRIGGER PSE_BKB.NUM_TR_PHANTOM_BP
BEFORE DELETE OR INSERT OR UPDATE
ON PSE_BKB.NUM_PHANTOM_BP
REFERENCING NEW AS NEW OLD AS OLD

[code]...

View 11 Replies View Related

Select All Views From Schema

Apr 27, 2010

I want to list out all the views in a schema. What table can I query?

View 2 Replies View Related

Insert Into Select From Another Schema?

Jan 11, 2011

I have a schema northwind which has tables and then i have another schema northwind_staging which has its own tables.
CREATE TABLE "NORTHWIND"."CUSTOMERS"
(
"CUSTOMERID" VARCHAR2(5 CHAR),
"COMPANYNAME" VARCHAR2(40 CHAR) NOT NULL ENABLE,
"CONTACTNAME" VARCHAR2(30 CHAR),
"CONTACTTITLE" VARCHAR2(30 CHAR),
"ADDRESS" VARCHAR2(60 CHAR),

[code]...

I created a procedure

CONNECT NORTHWIND_STAGING/NENAGH1;
create or replace procedure INSERTCUSTOMERS_STAGING
as
begin
INSERT INTO NORTHWIND_STAGING.customers_staging
SELECT DISTINCT c.customerid, c.companyname,c.contactname,c.region, c.country
FROM NORTHWIND.CUSTOMERS c;
end;
/

show errors This works fine when i ran my test scripts in SQL developer. I created a role Select on Northwind

View 10 Replies View Related

SQL & PL/SQL :: Select Data Different Schema

Jan 12, 2011

I have 3 users in schema.

User09
User10
User11

Table Name PURCHASE exists in all above three schema with different data(according to date)

Like
User09 ----> purchase ---- data date Jan09 to Dec09
User10 ----> purchase ---- data date Jan10 to Dec10
User11 ----> purchase ---- data date Jan11 to Dec11

I want to select * from purchase, from above all three user with select statement.I can select data one by one with union all

select * from user09.pruchase Union all
select * from user10.pruchase Union all
select * from user11.pruchase

after creating this query I add another User User12 and that User has same purchase table so I will have to add one another line IN Union all Query. when I see this query. It gives me the all owner name of User09,User10,User11......

SELECT * FROM ALL_ALL_TABLES
WHERE OWNER LIKE 'USER%'
and table_name ='PURCHASE'

OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
USER PURCHASE USERS
USER09 PURCHASE USERS
USER10 PURCHASE USERS
3 rows selected

Can I get data from Purchase Table of all users with select statement .

View 4 Replies View Related

Create One Trigger Before DML On All Schema Tables

Nov 28, 2011

We have an application is must be connected to our database for specific requirements in our company but this application has a very bug thing as we must write the super DB password "Like HR password as example" clear in some files and these files must be shared so developers can use the HR password to do any action !!! I know that this application is a problem but we have to install

I can do this by creating trigger on each table will restrict DML. As example: if the operating system user is XXX, the trigger restrict the action. But not logic at all to create more than 1000 triggers on schema (This will impact badly on DB performance).

So, i need to create one trigger to fire before doing any DML on all schema tables. As example: If "MMM" the administrator operating system user trying to do insert action, he can do the action. BUT If "DEV" the developer operating system user trying to do insert action, the trigger must fire here to restrict this action.

Be noted also, i need this trigger not depend on any specific tool like Toad as any user can simply rename the exe file for toad then he can pass the trigger. At least, trigger must depend on (Operating system user & Action_type)

View 11 Replies View Related

SQL & PL/SQL :: Get Row Counts Of Tables In Schema Without Using Num_rows

Aug 9, 2010

I am trying to get a row count(*) for all the tables in my schema. The NUM_ROWS column in DBA_TABLES is not appropriate in this case because they are as good as the last analyze. So I need to get real time counts.

I tried the following code but I can't seem to catch my error.

DECLARE

l_sql varchar2(150);
cursor tablelist is
select table_name from dba_tables where owner = 'ME';

[Code]....

My expected results are :

TABLE_NAME ROW_COUNT
---------- ----------

View 13 Replies View Related

Tool To View Schema And Relations Between Tables

Jul 4, 2008

Any tool or way to view the schema and relations between tables of an Oracle database?

View 1 Replies View Related

Server Utilities :: Import Only Tables Of A Schema

Jul 26, 2013

The way to do IMPORT of a schema's ALL the TABLES only. Through Data Pump. I don't want to import any other objects like 'package',procedure etc...

Is it the only way is specifying them within EXCLUDE parameter?

View 3 Replies View Related

Server Utilities :: How To Backup Tables In A Schema

May 31, 2012

How to backup the tables in a schema? I need som halp on this subjekt. I hav Dev envrnment that I must to backup.

View 9 Replies View Related

SQL & PL/SQL :: Query To Find List Of Tables Contains Particular Data In Schema?

Feb 3, 2012

I want to list the tables in a schema which contains a particular value or data.

View 2 Replies View Related

Security :: Enabling Audits Can They Be Applied On Tables Or Whole Schema

Apr 19, 2013

Enabling of auditing in oracle database, can this be done on some tables or applied on the whole schema/user.

View 5 Replies View Related

Backup & Recovery :: Selected Data From Tables Of Schema

Mar 7, 2012

I want to take backup of selected data from tables of a schema (as huge data, that is not used, causing slow query performance) I planned to create a seprate backup schema and tablespace to store the data from these tables. Then write procedures that can move the data to and fro among table of those schema. And create partitioned index on those backup tables.

View 11 Replies View Related

Forms :: Summit Office Supply Schema (Tables)?

Jan 21, 2013

I have this pdf (2 Volumes) from Oracle University courses, "Oracle Fusion Middleware 11g: Build Applications with Oracle Forms" In order to read the examples you must have "Summit Office Supply Schema" fmbs, mmb, pll, tables.(dmp)

indicatively some tables (not confuse it with other summit user)
customers,
departments,
warehouses,
orders,
employees,
order_items,
product_information,
inventories

View 2 Replies View Related







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