Home » RDBMS Server » Security » Give "select on all tables from a user" to another user (11 or 12)
Give "select on all tables from a user" to another user [message #666943] Sat, 02 December 2017 07:20 Go to next message
Rumak18
Messages: 20
Registered: April 2009
Location: Germany
Junior Member
Hello folks,

i've got a schema user called "LOCAL_01". This user is owner of it's schema. Now there is a need for reading some tables from the schema user LOCAL_01 to another user called "REMOTE_01". Both are created on the same databse. I've tried the following:

Logged in as "LOCAL_01" user:
"GRANT SELECT ON TABLE1 TO REMOTE_01"
"GRANT SELECT ON TABLE2 TO REMOTE_01"
"GRANT SELECT ON TABLE3 TO REMOTE_01"


Logged out as LOCAL_01 , logged in as REMOTE_01. But REMOTE_01 still couldn't read the tables. Reuslt: no tables there.

Am i doing something wrong? How would you do this?
Re: Give "select on all tables from a user" to another user [message #666944 is a reply to message #666943] Sat, 02 December 2017 07:44 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Rumak18 wrote on Sat, 02 December 2017 05:20
Hello folks,

i've got a schema user called "LOCAL_01". This user is owner of it's schema. Now there is a need for reading some tables from the schema user LOCAL_01 to another user called "REMOTE_01". Both are created on the same databse. I've tried the following:

Logged in as "LOCAL_01" user:
"GRANT SELECT ON TABLE1 TO REMOTE_01"
"GRANT SELECT ON TABLE2 TO REMOTE_01"
"GRANT SELECT ON TABLE3 TO REMOTE_01"


Logged out as LOCAL_01 , logged in as REMOTE_01. But REMOTE_01 still couldn't read the tables. Reuslt: no tables there.

Am i doing something wrong? How would you do this?
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read


Do NOT tell us what you think you did.
use COPY & PASTE to show us exactly what you do & how Oracle responds.
Oracle is too dumb to lie.
Invariably Oracle reports reality better than novice users.

Re: Give "select on all tables from a user" to another user [message #666945 is a reply to message #666943] Sat, 02 December 2017 07:44 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You'll need to show what you did, as local_01 and as remote_01. Use SQL*Plus, and copy/paste the session here. Remember to enclose the text within [code] tags.
Re: Give "select on all tables from a user" to another user [message #666946 is a reply to message #666945] Sat, 02 December 2017 08:07 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
What results when you do like below while logged in as REMOTE_01?

SELECT COUNT(*) FROM LOCAL_01.TABLE1;

Remember that REMOTE_01 must prefix LOCAL_01 before table name to reference table in different schema.

Re: Give "select on all tables from a user" to another user [message #666953 is a reply to message #666946] Sun, 03 December 2017 03:21 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Or, if you want to avoid naming the owner (LOCAL_01) in front of a table name, create a synonym for that table in REMOTE_01 user. Something like this:
SQL> -- connected as LOCAL
SQL> create table test (id number);

Table created.

SQL> insert into test (id) values (100);

1 row created.

SQL> commit;

Commit complete.

SQL> grant select on test to remote;

Grant succeeded.

SQL> -- now, connect as REMOTE
SQL> connect remote/pwd@ora
Connected.
SQL> -- this is what you tried, and it didn't work (obviously, as there's no TEST table in REMOTE)
SQL> select * from test;
select * from test
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> -- as suggested by BlackSwan, use owner's name (LOCAL)
SQL> select * from local.test;

        ID
----------
       100

SQL> -- or, create a synonym ...
SQL> create synonym test for local.test;

Synonym created.

SQL> -- ... so that you could avoid naming owner's name (remember, we're still connected as REMOTE)
SQL> select * from test;

        ID
----------
       100

SQL>
Re: Give "select on all tables from a user" to another user [message #666958 is a reply to message #666953] Sun, 03 December 2017 14:15 Go to previous messageGo to next message
Rumak18
Messages: 20
Registered: April 2009
Location: Germany
Junior Member
Ok. Thanks. GUess the problem was the missing "with grant option" when creating "local_01".
When i tried all these steps as system user, it worked perfectly.

create user "REMOTE_01" identified by "PASSw";
grant create session to "REMOTE_01";
GRANT SELECT ON "LOCAL_01.table1" TO REMOTE_01;
GRANT SELECT ON "LOCAL_01.table2" TO REMOTE_01;
GRANT SELECT ON "LOCAL_01.table3" TO REMOTE_01;
conn REMOTE_01/PASSw@MYDB
select * from LOCAL_01.table1;


BUT...if i try this with another user whose username has a DASH in his username, then it does not work:
GRANT SELECT ON 'LOCAL-01.table1' TO "REMOTE_01"
-> ORA-00942: This view does not exist

or
GRANT SELECT ON "LOCAL-01.table1" TO "REMOTE_01"
-> ORA-00903 : unguilty table name


or
GRANT SELECT ON LOCAL-01.table1 TO "REMOTE_01"
-> ORA-00911 : invalid character


So how to do this with a dash in the schema name?

[Updated on: Sun, 03 December 2017 14:19]

Report message to a moderator

Re: Give "select on all tables from a user" to another user [message #666959 is a reply to message #666958] Sun, 03 December 2017 15:53 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>So how to do this with a dash in the schema name?
You do it the same as when you CREATE USER.

post results from SQL below

SELECT USERNAME FROM ALL_USERS WHERE USERNAME LIKE 'LOCAL%';
Re: Give "select on all tables from a user" to another user [message #666960 is a reply to message #666958] Sun, 03 December 2017 16:15 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Please see the following complete demonstration and try to duplicate it. Make sure that whatever names are created using double quotes are always referenced using double quotes and that whatever is in upper or lower case within those quotes is always the same.

-- connected as scott create user "LOCAL-01" and grant privileges:
SCOTT@orcl_12.1.0.2.0> connect scott/tiger@orcl
Connected.
SCOTT@orcl_12.1.0.2.0> create user "LOCAL-01" identified by "LOCAL-01";

User created.

SCOTT@orcl_12.1.0.2.0> alter user "LOCAL-01" quota unlimited on users;

User altered.

SCOTT@orcl_12.1.0.2.0> grant create session to "LOCAL-01";

Grant succeeded.

SCOTT@orcl_12.1.0.2.0> grant create table to "LOCAL-01";

Grant succeeded.

-- connected as "LOCAL-01" create table1 and grant select to scott with grant option:
SCOTT@orcl_12.1.0.2.0> connect "LOCAL-01"/"LOCAL-01"@orcl
Connected.
LOCAL-01@orcl_12.1.0.2.0> create table table1 as select * from dual;

Table created.

LOCAL-01@orcl_12.1.0.2.0> grant select on table1 to scott with grant option;

Grant succeeded.

-- connected as scott create user "REMOTE_01" and grant select on "LOCAL-01".table1 to "REMOTE_01":
LOCAL-01@orcl_12.1.0.2.0> connect scott/tiger@orcl
Connected.
SCOTT@orcl_12.1.0.2.0> create user "REMOTE_01" identified by "PASSw";

User created.

SCOTT@orcl_12.1.0.2.0> grant create session to "REMOTE_01";

Grant succeeded.

SCOTT@orcl_12.1.0.2.0> grant select on "LOCAL-01".table1 to "REMOTE_01";

Grant succeeded.

-- connected as "REMOTE_01" select from "LOCAL-01".table1:
SCOTT@orcl_12.1.0.2.0> connect "REMOTE_01"/"PASSw"@orcl
Connected.
REMOTE_01@orcl_12.1.0.2.0> select * from "LOCAL-01".table1;

D
-
X

1 row selected.
Re: Give "select on all tables from a user" to another user [message #666962 is a reply to message #666960] Mon, 04 December 2017 00:12 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Barbara

-- connected as scott create user "REMOTE_01"
This, normally, won't work because Scott doesn't have a privilege to create users. I guess Barbara granted certain privileges to it (DBA?).
Re: Give "select on all tables from a user" to another user [message #666993 is a reply to message #666958] Mon, 04 December 2017 06:49 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Rumak18 wrote on Sun, 03 December 2017 14:15

BUT...if i try this with another user whose username has a DASH in his username, then it does not work:
<snip>
So how to do this with a dash in the schema name?
You shouldn't be creating users (or any other object) with a dash in the name. And you shouldn't be using double-quotes to get around that. That may work just fine in other rdbms products, but in oracle it is the road to perdition.

SQL> --
SQL> select username
  2  from dba_users
  3  where upper(username) like 'LOCAL%'
  4  order by 1;

no rows selected

SQL> --
SQL> create user local_01 identified by fubar;

User created.

SQL> create user local-01 identified by fubar;
create user local-01 identified by fubar
                 *
ERROR at line 1:
ORA-00922: missing or invalid option


SQL> create user "local-01" identified by fubar;

User created.

SQL> select username
  2  from dba_users
  3  where upper(username) like 'LOCAL%'
  4  order by 1;

USERNAME
--------------------------------------------------------------------------------
LOCAL_01
local-01

2 rows selected.

SQL> --
SQL> drop user local_01;

User dropped.

SQL> drop user local-01;
drop user local-01
               *
ERROR at line 1:
ORA-00921: unexpected end of SQL command


SQL> drop user "local-01";

User dropped.

SQL> --
SQL> spo off
Re: Give "select on all tables from a user" to another user [message #667001 is a reply to message #666962] Mon, 04 December 2017 13:14 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Littlefoot wrote on Sun, 03 December 2017 22:12
Barbara

-- connected as scott create user "REMOTE_01"
This, normally, won't work because Scott doesn't have a privilege to create users. I guess Barbara granted certain privileges to it (DBA?).

Correct. On my home system, user scott has been granted DBA privileges. I just used this user as an example. It could be any user with DBA privileges that is used to create the users.
Re: Give "select on all tables from a user" to another user [message #667230 is a reply to message #667001] Fri, 15 December 2017 07:37 Go to previous messageGo to next message
Rumak18
Messages: 20
Registered: April 2009
Location: Germany
Junior Member
Ok, unfortunately i have to live with these "dashes" in the names of the schemes. But at least i've got now a workaround. Thank you all for you effort and great help!
Re: Give "select on all tables from a user" to another user [message #667233 is a reply to message #667230] Fri, 15 December 2017 08:00 Go to previous message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Rumak18 wrote on Fri, 15 December 2017 13:37
Ok, unfortunately i have to live with these "dashes" in the names of the schemes. But at least i've got now a workaround. Thank you all for you effort and great help!
Find out who did that and educate them is my advice Smile
Previous Topic: Oracle RDBMS 11G TDE on existing tablespaces?
Next Topic: Regarding oracle VPD
Goto Forum:
  


Current Time: Thu Mar 28 04:14:14 CDT 2024