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 RepliesUser1 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.
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.
I wanted a query that will give me the list of views using three tables
when i fire
select * from dba_views where text like '%SELECT%';
it is giving me error as expected number got long.
I have 12 different tables
Record_jan
Record_feb
.
.
.
Record_nov
Record_dec
Based on the month i need to do some i/o operations from these table.
For eg: If it is january then Record_jan table should be accesed or for nov Record_nov table should be accesed.
If I try to store the table name in some variable and then give the variable name in place of table name then it gives error.
var:=Record_month;
Select * from var;
this gives error.
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.
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 RelatedMy 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.
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?
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?
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.
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.
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;"
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 RelatedWhen I use the below code in my perl script (it is oracle database):
$query = "select a.sub_id, b.name from subscribertable a, invitationBin b where a.subscriberid=b.subscriberid";
$sth = $dbh->prepare($sub_query) or die "SELECT-Query failed";
everything is ok... and when I try to add a third table...
$query = "select a.sub_id, b.name, c.phone from subscribertable a, invitationBin b, personalDet c where a.subscriberid=b.subscriberid and b.subIdx=c.subIdx";
$sth = $dbh->prepare($sub_query) or die "SELECT-Query failed";
this fails... it seems like it doesnt let me add 3 tables in the SELECT query through perl script. The strange is that when I test this query with Oracle SQL Developer, it works fine...!
Consider the following tables
MANAGERID
101
102
103
104
MANAGERID EMPID
101 ----------------24
101-----------------25
101 ----------------26
104 --------------- 27
write sql query to get the following output without using minus,union,intersect.
MANAGERID EMPID
101 ---------------------------------- 24
101------------------------------------25
101 -----------------------------------26
102 -----------------------------------N/A
103 -----------------------------------N/A
104 -----------------------------------27
I have two tables a and b column names are id and date. Data type of date in a and b are different
table (a)
date id
10-DEC-01 2:08:39 PM 1
10-DEC-01 2:08:39 PM 2
10-JAN-02 10:10:22 PM 3
10-JAN-02 10:10:22 PM 4
10-JAN-02 10:10:22 PM 5
table (b)
date id
10-DEC-01 1
10-DEC-01 2
I need table b like this one
table (b)
date id
10-DEC-01 1
10-DEC-01 2
10-JAN-02 3
10-JAN-02 4
10-JAN-02 5
I'm having trouble with some SQL code regarding count and an outer join.
Here is my code.
SELECT o.salespersonid, Count(*) from
salesperson s, Ord o
Where s.salespersonid(+) = o.salespersonid
Group By o.salespersonid;
Where salesperson is a salesperson table and ord is a table containing orders.
The orders table contains a FK to salespersonid in the salesperson table.
I want it to return all salespersons along with the amount of orders they are on. It works but does not show the ones that do not appear on any orders hence the outer join.
Is there any way i can select * from all the tables owned by particular schema at once.
View 22 Replies View RelatedI have two tables containing dates:
A
From____________To________
01.01.2009 || 01.01.2010
01.01.2013 || 01.10.2014
B
From____________To________
01.01.2007 || 01.01.2008
01.01.2011 || 01.10.2012
01.01.2009 || 01.01.2010
01.01.2015 || 01.01.2016
I need to get list of all entries from Table A and entries from B where dates are before min begin date from A or somewhere between any entry from A.
In this Example result:
From____________To________
01.01.2007 || 01.01.2008
01.01.2009 || 01.01.2010
01.01.2011 || 01.10.2012
01.01.2013 || 01.10.2014
I am working on the following SQL select and I am having a mental block on how to get it fixed. I have two tables that I need to match on the codes in each table. If there is a just one record in Table1 with the same code as one record in table2 and both the date and name match then dont output those two records. Output all records if there are more than 1 record with the same code in each table. Below is some example data that is representive of a sample in the two tables and how the output should look based on that data:
Table1
code date name
aaaa 1/1/2003 billy bob
bbbb 2/2/2004 louis lewis
cccc 3/3/2005 joe crab
dddd 4/4/2006 mary little
eeee 5/5/2007 joe black
Table2
code date name
aaaa 2/2/2004 larry cole
aaaa 3/3/2005 nat king
bbbb 2/2/2004 louis lewis
cccc 3/3/2005 joe crab
cccc 6/6/2008 dennis jackson
dddd 7/7/2009 missy muffet
dddd 5/5/2007 joe black
eeee 8/8/2010 elton rocket
desired output results from select
aaaa 1/1/2003 billy bob aaaa 2/2/2004 larry cole
aaaa 1/1/2003 billy bob aaaa 3/3/2005 nat king
cccc 3/3/2005 joe crab cccc 3/3/2005 joe crab
cccc 3/3/2005 joe crab cccc 6/6/2008 dennis jackson
dddd 4/4/2006 mary little dddd 7/7/2009 missy muffet
dddd 4/4/2006 mary little dddd 7/7/2009 missy muffet
eeee 5/5/2007 joe black eeee 8/8/2010 elton rocket
Here is the select that I have so far:
select table1.rowid, table1_code, table1_date, table1_name,
table2.rowid, table2_code, table2_date, table2_name from table1, table2
where table1_code= table2_code
order by table1_code;
The above select gives me all records just fine, but does not eliminate single records that match. I tried using the Count(table1_code) > 1 and table2 code but I get a message about inproper grouping.
I have had a google around and can't seem to find an answer as to how do do the following Select statement. i am wanting to Select the following fields from across multiple tables.
(field.table)
CustName.CUST
SalesNo.SALE
SalesDate.SALE
ItemDes.ITEM
Qty.SALEITEM
OrderComplete.SALEITEM
with 2 types of WHERE criteria:
WHERE SalesDate is between 'dateX' AND 'dateY'
and also WHERE OrderComplete = 'Y'
i understand this will require some sort of join in the statement so the keys for the different tables are as follows:
CUST
CustNo - PK
SALE
SalesNo - PK
CustNo - fk
ITEM
ItemNo - PK
SALEITEM
SalesNo -fk
ItemNo - fk (compound PK)
i have had a play around with using some joins & embedded statements
How does one select * from one table without selecting * from other tables that are included in a query? For example, if in the query below I want to view all fields in some_table, but not the fields from other_table, how do it?
select *
from some_table st,
other_table ot
where st.id = ot.id
I've got 3 tables and I need to select records where date is max from these tables.
select * from the_table_1
----------------------------------
contract_key date_1 saldo
1234 30.9.2011 12:06:50 14,6638
select * from the_table_2
----------------------------------
contract_key date_1 saldo
1234 26.9.2011 11:04:02 5,6638
select * from the_table_2
----------------------------------
contract_key date_1 saldo
1234 29.9.2011 17:39:43 2,5438
how to do that ?
i have 3 tables in my database..
table1 with one column
table1
table1_id
11
12
13
14
table2 with one column
table2
table2_id
21
22
23
24
table 3 with 2 primary foreign keys from table1 and table2
table3
table1_id table2_id
11 21
11 22
12 23
12 24
13 21
14 22
i want a select statement that selects all from table2 where the specified table1_id is not in the list of the table1_id in table3
for example if i want the list for id 11 it should return (23, 24)
i came up with the following but it didn't work
select table2_id
from tabel3 c, table2 b
where table1_id <> 11 and c.table2_id = b.table2_id
i know my explanation is not that clear..
I have long select which operate on 5 tables and has a lot of conditions in where clause (many combinations of values of just a few columns). Does reducing of those conditions could improve performance or just has a small impact?
I think if I have a lot of conditions on the same column, it don't take a lot of time to check them because values are in memory.
i am trying to left join a selection of two or more tables. what i have found, and solved part of my problem, is that oracle left joins only the last table in the select statement ...
i.e : select * from A, B left join C on C.id = A.id wouldn't work because left join applies to B and not A.
but as my queries grow i need to make something as follows :
select * from A, B
left join C on (C.ID_A = A.ID and C.ID_B = B.ID)
[... evantually more left joins as the preceding one may go here]
this query works for DB2 but Oracle claims that "A"."ID" is an invalid identifier, while the B.ID is recognized since it's the last table stated before the "LEFT JOIN" keyword.
I have two tables one source table and one destination . Column names and data types of both table are same.
source table (source)
ID NAME
1 aa
2 bb
3 cc
. ...
. ..
. ..
destination Table(dest)
ID NAME
1 aa
2 bb
I need destination table like
destination table
ID NAME
1 aa
2 bb
3 cc
. .....
. ....
I want to remove repeated rows
I am trying to make a select statement to join a few tables together. What i would like to know is if i can do this by saying the following.
I want to select indviduals that have a skill, but i want to say that if they have any of these skills to show the name so for example
SELECT S.NAME
FROM EMPLOYEE S ,PROJECT_TEAM T, SKILL_LIST L
WHERE T.PROJECT = 'Tesco' AND
L.SKILLNO = 'skill1' or 'skill2' or 'skill3';
im trying to select columns from different tables dynamically in a function . The parameter for the function will be table name and column id's, In this number of columns may vary . Is it possible to have dynamic %rowtype to store the cursor value in it.
View 2 Replies View Related