Give "select on all tables from a user" to another user [message #666943] |
Sat, 02 December 2017 07:20 |
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 #666953 is a reply to message #666946] |
Sun, 03 December 2017 03:21 |
|
Littlefoot
Messages: 21823 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 |
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
|
|
|
|
|
|
|
|
|
|