SEARCH YOUR SOLUTION HERE  

SELECT ANY TABLE and VIEWS in different schema



I'm confident that this a stupid question but I'm rather confused as to what's is going on here. If anyone can shed any light on this it would be much appreciated:

Code: system@DEV> system@DEV> SET LINES 132 system@DEV> SET PAGES 9999 system@DEV> system@DEV> DROP USER proxy CASCADE; User dropped. system@DEV> DROP USER tableuser CASCADE; User dropped. system@DEV> DROP USER viewuser CASCADE; User dropped. system@DEV> system@DEV> define the_pw=&1 Enter value for 1: qwerty+123 system@DEV> system@DEV> CREATE USER proxy IDENTIFIED BY "&the_pw" 2 DEFAULT TABLESPACE USERS 3 TEMPORARY TABLESPACE TEMP; old 1: CREATE USER proxy IDENTIFIED BY "&the_pw" new 1: CREATE USER proxy IDENTIFIED BY "qwerty+123" User created. system@DEV> system@DEV> CREATE USER tableuser IDENTIFIED BY "&the_pw" 2 DEFAULT TABLESPACE USERS 3 TEMPORARY TABLESPACE TEMP 4 QUOTA UNLIMITED ON USERS; old 1: CREATE USER tableuser IDENTIFIED BY "&the_pw" new 1: CREATE USER tableuser IDENTIFIED BY "qwerty+123" User created. system@DEV> system@DEV> CREATE USER viewuser IDENTIFIED BY "&the_pw" 2 DEFAULT TABLESPACE USERS 3 TEMPORARY TABLESPACE TEMP; old 1: CREATE USER viewuser IDENTIFIED BY "&the_pw" new 1: CREATE USER viewuser IDENTIFIED BY "qwerty+123" User created. system@DEV> system@DEV> GRANT CREATE SESSION TO proxy; Grant succeeded. system@DEV> GRANT CREATE SESSION TO viewuser; Grant succeeded. system@DEV> system@DEV> CREATE TABLE tableuser.t ( 2 id NUMBER 3 ); Table created. system@DEV> system@DEV> GRANT SELECT ON tableuser.t TO viewuser; Grant succeeded. system@DEV> system@DEV> CREATE VIEW viewuser.tv AS 2 SELECT * FROM tableuser.t; View created. system@DEV> system@DEV> GRANT SELECT ON viewuser.tv TO proxy; Grant succeeded. system@DEV> system@DEV> CONNECT viewuser/&the_pw Connected. system@DEV> system@DEV> SELECT * FROM tv; no rows selected system@DEV> system@DEV> CONNECT proxy/&the_pw Connected. system@DEV> system@DEV> SELECT * FROM viewuser.tv; SELECT * FROM viewuser.tv * ERROR at line 1: ORA-01031: insufficient privileges system@DEV> system@DEV> CONNECT system Connected. system@DEV> system@DEV> GRANT SELECT ANY TABLE TO viewuser; Grant succeeded. system@DEV> system@DEV> CONNECT proxy/&the_pw Connected. system@DEV> system@DEV> SELECT * FROM viewuser.tv; no rows selected system@DEV> system@DEV> SPOOL OFF Why can user proxy only select from the view when viewuser has SELECT ANY TABLE privileges How can I allow PROXY to SELECT from the view without granting SELECT ANY TABLE to VIEWUSER; minimum privileges and all that.

I'm sure this is all basic stuff and I'm embarrased to say I don't really understand what's going on here

Posted On: Thursday 15th of November 2012 11:17:52 PM Total Views:  357
View Complete with Replies




Related Messages:

prevent client select more than 3 players with stored procedure code   (177 Views)
hello client has a right to create a team with selecting players from different teams but I vant to prevent him from selecting more than 3 players form the same team my code is like this REATE PROCEDURE dbo.SaveTeam ( @MEMBER_IDint, @PLAYER_01int, @PLAYER_02int, @PLAYER_03int, @PLAYER_04int, @PLAYER_05int, @PLAYER_06int, @PLAYER_07int, @PLAYER_08int, @PLAYER_09int, @PLAYER_10int, @PLAYER_11int, @RESULTintOUTPUT ) AS DECLARE @TOTAL_PRICE float SELECT @TOTAL_PRICE = SUM(PRICE) FROM PLAYERS with(nolock) WHERE ID IN (@PLAYER_01,@PLAYER_02,@PLAYER_03,@PLAYER_04,@PLAYER_05,@PLAYER_06,@PLAYER_07,@PLAYER_08,@PLAYER_09,@PLAYER_10,@PLAYER_11) IF @TOTAL_PRICE > 40000 BEGIN SET @RESULT = -1 RETURN END I prevent him from making team more expensive than 40000 but I vant to prevent client from selectin more than 3 palyers but I didnt achieve.
HELP with SQL select   (107 Views)
i have a table called contacts and customers basically i need to get the customer_id of those ppl who have birthdays today. i know how to do that, the problem is there are some duplicate data. ie, with different cutomer ID same ppl with same firstname and surname and date of birth. how can i check that and get only one record appreciate ny help
Advice on selecting info from multiple tables   (162 Views)
I am using mySql, and I have information such as Bio, Address, Program info. They are all on seperate tables. The only key that is the same, is the ADMID, which is the main identifier. How could I select all that information without having to open three seperate connections and issuing three seperate commands
select next record   (164 Views)
, I have a query where I pass in a VARCHAR2 (CIPIDI_NR). This Query should return the next higher (or lower) record depending on some conditions. I tried: select * from TBL_CIPIDI where confidential_flag = 0 and CIPIDI_NR > (THIS IS WHERE I PASS IN THE VALUE) Unfortunately this returns all records which are higher than the VALUE I pass in. But I only would like to receive the next record. I am not using a sequence. How would be the best way to approach this.
select command problem with string parameter   (297 Views)
i construct the following select statement: selectcommand = "select fund_code from fund where fund_portfolio_code = @parameter1" then i add a parameter: selectcommand.parameters.add("@parameter1", portcode) where portcode is a string data type; when I execute a datareader this does not work; error says the expression is incorrect; when i try selectcommand = "select fund_fund from fund where fund_portfolio_code = '@parameter1' " i get another error message. an example of the precise syntax that needs to hit the database (Oracle) is: select fund_code from fund where fund_portfolio_code = 'CMI' if I construct selectcommand = "select fund_fund from fund where fund_portfolio_code = 'CMI' " it works fine. how can i get this logic to work using the parameter value
selectcommand string with Foxpro   (223 Views)
In switching to Vfpoledb free foxpro tables from SQLserver, my selectcommand no longer works in this format with the '@' parameter/variable: Is there a simple fix xdatasource1.selectcommand = "SELECT OFFICE , CUST_NAME ,ITEM FROM fy2006 whereITEM LIKE '%' + @ITEMVAR + '%'"
Seperate selected data to several textbox / label   (192 Views)
As we know the ASP.Net focuses more on DATAGRID. I would however like to display some selected record into several textbox. For instant, I selected a particular user's profile Sql = "Select name, age, sex From User;" And would like to display the selected record into several textbox That can be easily done in ASP, but what about ASP.Net So I wish someone can actuallly guide me with some sample code in ASP.Net(VB)
subselects in MySQL   (274 Views)
Is the MySQL 4.0.x doesn't support subselects statment like select * from register where registerid in (select registerid from regsetting);
how to declare variable in select statement   (273 Views)
I have the following code use in sql server strSql = "DECLARE @SeqNo Integer " strSql += "SELECT @SeqNo = MAX(SeqNo) FROM tblPromotion " strSql += "SET @SeqNo = ISNULL(@SeqNo,0) " strSql += "SET @SeqNo = @SeqNo + 1 " strSql += "INSERT INTO tblAdvertisement(SeqNo) Values (@SeqNo)"; Can anyone tell me in my sql how to declare a variable
problem with syntax for parameter marker ? and wildcard % in select going to db2 on an iseries server   (220 Views)
Hi , I have a query being used to load a gridview from aview in db2 on an iseries server. When I use the syntax select field1, field2 from tablename where field2 like there is no problem the query returns the resultset where field2 exactly matches the parameter. Having problems figuring out or finding on the net how to introduce wild card to the parameter...have tried all kinds of things such as select field1, field2 from tablename where field2 like %%and select field1, field2 from tablename where field2 like '%'++'%' but everything I try just raises exceptions. Can some kind soul give me an example of the proper syntax
How to get the value return from "select last_insert_id()" ?   (169 Views)
After Insert() I wanna get the automatic number with "select last_insert_id()" Code : ........ string mysqlstring = "select last_insert_id()"; MySqlDataReader myreader; myreader = mycommand.ExecuteReader(); int userid = myreader.GetInt16(0); ////-------------------------------- Error ! Why
how to fire insert, select query if 1 datatype is xml and other is normal?   (233 Views)
I want insert selct query for following Mailed_from varchar Mailed_date Datetime Mailed_to Xml here xml is used to store more than 1 MAil id
MySql select Statement   (218 Views)
I have a web application in asp.net that connects to mySql database to grab and display the information. I need to display the latest record. for that this statement is working fine. Select timestring, pwr_vin, dsp_dig_in_6, pwr_temp from soh_data order by timestring desc limit 1 But when I want to display all the columns with the statement below. it gives error message mysql date/time value doesnot match with system time. Select* from soh_data order by timestring desc limit 1 The table has about 80 columns. so I don't want to hard code them in the sql statement. what could be the solution. If think about stored procedures, need to be stored with database, but it is a read only databse. Any alternatives.
URGENCE- Problem with select in Oracle   (216 Views)
I have a simple search query that works in both Oracle and SQL SERVER. When I search for something in SQL SERVER it gives more result than Oracle. I belive it is case sensitive in Oracle. For example in SQL SERVER I could write "pipet "and it returns me everything regardless of the format and cases, but in Oracle it doesn't give me those "Pipet" which start with capital. it gived exactly as it is entered. I have to mention the page, the data type, and the data are all the same in these two databases.Is there anything that I should to change that except having OR in my queries for both uppercase and lower cases. //name is a text box to String myText = name.ToString(); SELECT * from MyTable where product_Desc LIKE '%" + myText + "%' Is there a better way to write such a select in order to be more accurate and include upper and lower cases.
URGENCE - Problem with select query in Oracle   (185 Views)
I have a simple search query that works in both Oracle and SQL SERVER. When I search for something in SQL SERVER it gives more result than Oracle. I belive it is case sensitive in Oracle. For example in SQL SERVER I could write "pipet "and it returns me everything regardless of the format and cases, but in Oracle it doesn't give me those "Pipet" which start with capital. it gived exactly as it is entered. I have to mention the page, the data type, and the data are all the same in these two databases.Is there anything that I should to change that except having OR in my queries for both uppercase and lower cases. //name is a text box to String myText = name.ToString(); SELECT * from MyTable where product_Desc LIKE '%" + myText + "%' Is there a better way to write such a select in order to be more accurate and include upper and lower cases.
GridView+MySql...selected item send to another page   (136 Views)
I i'm beginner in ASPNET 2.0,but i would like to select all item in a table like this SELECT * FROM mytable then fill it to a GridView(named it GridView1) then the item that showed when user click it,it will be send to another page...like this Response.Redirect("test.aspxitemSelect=" + itemName); this is my code..... MySqlConnectionconn; stringConnectionString; ConnectionString="server=localhost;uid=USERNAME;database=mydatabase;"; conn=newMySqlConnection(); conn.ConnectionString=ConnectionString; MySqlCommandcmd=newMySqlCommand(); cmd.CommandText="SELECTFilePath,ICFROMfileatserverWHEREIC= IC"; cmd.Connection=conn; cmd.Parameters.Add("IC",MySqlDbType.VarChar,45).Value=LoginName; MySqlDataAdapterMyDataAdapter=newMySqlDataAdapter(cmd); SqlDataSourceData1=newSqlDataSource(); Data1.ProviderName="MySql.Data.MySqlClient"; Data1.SelectCommand=cmd.CommandText; Data1.ConnectionString=ConnectionString; Data1.DataBind(); DataSetdatasettKu2=newDataSet(); MyDataAdapter.Fill(datasettKu2,"fileatserver"); GridView1.DataSource=datasettKu2; //GridView1.DataKeyNames="FilePath"; GridView1.DataBind(); i'm using .NET Framework Data Provider for MySQL (using MySql.Data.MySqlClient;) data that i want to send to another page is in "FilePath" column or i have another choice to view all data and send selected item to another page please help me.
Trying to select names that begin with a letter, using WHERE xxx LIKE problem   (198 Views)
SelectCommand="SELECT firstName, lastName, position, phone FROM phone WHERE (lastName LIKE + '%')"> This is what I have set up in a aspx file created with vwd. I had to remove [ ] from all of the field and table enteries. My problem is that I cannot get the select statement to work as I want. What I want to do is to take a single letter input ('s' for example) from a textbox control and return all last names that start with 's'. If I put in 'smith', my gridview will return with all the 'smith's' as expected. If I put in s, I don't get anything, except the textbox and the submit button. I tried the '%' + + '%' that was created by VWD when I added the WHERE clause, but this doesn't work either. Anyone got any joy for me
Why select count(*) is faster than select count(1)?   (156 Views)
Why select count(*) is faster than select count(1) I thought count(*) will take longer as oracle need to find out all the column names from dictionary ...
select in pl/sql   (129 Views)
Hi Does anyone know how can you put queries in pl/sql like select * from emp inside a pl/sql block and then output the results in sqlplus
select distinct count on different dates   (274 Views)
I want to select count of distinct requests for different statuses on different days like today, yesterday and day-before-yesterday from sample table, please guide me in this regard. Currently using following query where i get daily count. columns: req_Status, req_id,sent_date select req_status, count(distinct(req_id)) as REQ_TODAY, MIN(to_char(SENT_DATE,'DD-MON-YY - HH:MI:SS')) as Min_sent_date, MAX(to_char(SENT_DATE,'DD-MON-YY - HH:MI:SS')) as Max_sent_date,'1-NEW Command' Meaning_of_Status from sample where sent_date like sysdate and req_status=1 group by req_status union select req_status, count(distinct(req_id)) as REQ_TODAY, MIN(to_char(SENT_DATE,'DD-MON-YY - HH:MI:SS')) as Min_sent_date, MAX(to_char(SENT_DATE,'DD-MON-YY - HH:MI:SS')) as Max_sent_date,'2-old Command' Meaning_of_Status from sample where sent_date like sysdate and req_status=2 group by req_status union . . and so on... column req_status has values in range from 1 to 12 output: REQ_STATUS REQ_TODAY MIN_SENT_DATE MAX_SENT_DATE MEANING_OF_STAT ------------- ------------ --------------------- --------------------- ------------------- 1 3 25-JUL-12 - 06:47:20 25-JUL-12 - 07:28:24 1-NEW Command 2 3 25-JUL-12 - 06:47:20 25-JUL-12 - 08:07:10 2-old Command 3 3 25-JUL-12 - 06:47:20 25-JUL-12 - 08:07:29 3-wrong Command . . so on for 12 REQ_STATUSes In this output I need two more columns that will depict count of requests for different statuses for last two days as follows : REQ_STATUS | REQ_CNT_DB4YEST | REQ_CNT_YEST | REQ_TODAY | MIN_SENT_DATE | MAX_SENT_DATE | MEANING_OF_STAT