How can to make a new user see table in existing schema

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;

up vote
2
down vote

favorite

I'm new into Oracle so this question might be dumb. I created new user and now I would like him to see table (or even better all tables by one command) in already existing schema (hr schema in this case). Is it even possible?

From my understanding databases store data and Schemas are equal to Users, so can two users see the same data stored in database, like in my case?

asked 1 hour ago

Rocket128

111

New contributor

add a commentÂ |Â

up vote
2
down vote

favorite

From my understanding databases store data and Schemas are equal to Users, so can two users see the same data stored in database, like in my case?

asked 1 hour ago

Rocket128

111

New contributor

add a commentÂ |Â

up vote
2
down vote

favorite

From my understanding databases store data and Schemas are equal to Users, so can two users see the same data stored in database, like in my case?

asked 1 hour ago

Rocket128

111

New contributor

From my understanding databases store data and Schemas are equal to Users, so can two users see the same data stored in database, like in my case?

oracle oracle-11g-r2 permissions schema users

asked 1 hour ago

Rocket128

111

New contributor

asked 1 hour ago

Rocket128

111

New contributor

asked 1 hour ago

Rocket128

111

New contributor

asked 1 hour ago

Rocket128

111

asked 1 hour ago

Rocket128

111

New contributor

Rocket128 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a commentÂ |Â

2 Answers
2

active

oldest

votes

up vote
1
down vote

select 'GRANT SELECT ON ' || table_name || ' TO OTHER_USER;' from user_tables;

then copy the output, paste and execute it as HR user.

answered 1 hour ago

Michal

111

New contributor

Is there some sqlplus command equivalent to this statement?
â€“Â Rocket128
1 hour ago

add a commentÂ |Â

up vote
1
down vote

It is something Oracle could have solved ages ago. There is no simple command do it, so usually we just loop through the tables and grant object level privileges:

set serveroutput on
declare
 l_cmd clob;
begin
 for t in (select * from all_tables where owner = 'HR')
 loop
 begin
 l_cmd := 'grant select on "' || t.owner || '"."' || t.table_name || '" to new_user';
 execute immediate l_cmd;
 exception when others then dbms_output.put_line(l_cmd);
 end;
 end loop;
end;
/

Tables created after this in HR schema will not be visible to the other user, each time an object is created, the privileges should be granted on that.

A better solution is to create a role, lets say HR_READONLY, and each time a new table is created in HR schema, the privileges on that object should be granted to HR_READONLY. Then you can just simply grant this role to the other user. This requires some self discipline from the developers, but I have seen some of them follow this method.

answered 1 hour ago

Balazs Papp

23.8k2930

Is there some sqlplus command equivalent to this statement? When I log as hr user and try this command it's not working: grant select on jobs to TestUser;
â€“Â Rocket128
1 hour ago

@Rocket128 - no. But the Database Idea is Under Review - Voting still open. See: community.oracle.com/ideas/2333 (requires registration to Vote)
â€“Â Michael Kutz
17 mins ago

add a commentÂ |Â

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);

);

Rocket128 is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f221645%2fhow-can-to-make-a-new-user-see-table-in-existing-schema%23new-answer', 'question_page');

);

Post as a guest

Name

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

up vote
1
down vote

select 'GRANT SELECT ON ' || table_name || ' TO OTHER_USER;' from user_tables;

then copy the output, paste and execute it as HR user.

answered 1 hour ago

Michal

111

New contributor

Is there some sqlplus command equivalent to this statement?
â€“Â Rocket128
1 hour ago

add a commentÂ |Â

up vote
1
down vote

select 'GRANT SELECT ON ' || table_name || ' TO OTHER_USER;' from user_tables;

then copy the output, paste and execute it as HR user.

answered 1 hour ago

Michal

111

New contributor

Is there some sqlplus command equivalent to this statement?
â€“Â Rocket128
1 hour ago

add a commentÂ |Â

up vote
1
down vote

select 'GRANT SELECT ON ' || table_name || ' TO OTHER_USER;' from user_tables;

then copy the output, paste and execute it as HR user.

answered 1 hour ago

Michal

111

New contributor

select 'GRANT SELECT ON ' || table_name || ' TO OTHER_USER;' from user_tables;

then copy the output, paste and execute it as HR user.

answered 1 hour ago

Michal

111

New contributor

answered 1 hour ago

Michal

111

New contributor

answered 1 hour ago

Michal

111

answered 1 hour ago

Michal

111

New contributor

Michal is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

Is there some sqlplus command equivalent to this statement?
â€“Â Rocket128
1 hour ago

add a commentÂ |Â

Is there some sqlplus command equivalent to this statement?
â€“Â Rocket128
1 hour ago

Is there some sqlplus command equivalent to this statement?
â€“Â Rocket128
1 hour ago

add a commentÂ |Â

up vote
1
down vote

It is something Oracle could have solved ages ago. There is no simple command do it, so usually we just loop through the tables and grant object level privileges:

set serveroutput on
declare
 l_cmd clob;
begin
 for t in (select * from all_tables where owner = 'HR')
 loop
 begin
 l_cmd := 'grant select on "' || t.owner || '"."' || t.table_name || '" to new_user';
 execute immediate l_cmd;
 exception when others then dbms_output.put_line(l_cmd);
 end;
 end loop;
end;
/

Tables created after this in HR schema will not be visible to the other user, each time an object is created, the privileges should be granted on that.

answered 1 hour ago

Balazs Papp

23.8k2930

Is there some sqlplus command equivalent to this statement? When I log as hr user and try this command it's not working: grant select on jobs to TestUser;
â€“Â Rocket128
1 hour ago

@Rocket128 - no. But the Database Idea is Under Review - Voting still open. See: community.oracle.com/ideas/2333 (requires registration to Vote)
â€“Â Michael Kutz
17 mins ago

add a commentÂ |Â

up vote
1
down vote

It is something Oracle could have solved ages ago. There is no simple command do it, so usually we just loop through the tables and grant object level privileges:

set serveroutput on
declare
 l_cmd clob;
begin
 for t in (select * from all_tables where owner = 'HR')
 loop
 begin
 l_cmd := 'grant select on "' || t.owner || '"."' || t.table_name || '" to new_user';
 execute immediate l_cmd;
 exception when others then dbms_output.put_line(l_cmd);
 end;
 end loop;
end;
/

Tables created after this in HR schema will not be visible to the other user, each time an object is created, the privileges should be granted on that.

answered 1 hour ago

Balazs Papp

23.8k2930

Is there some sqlplus command equivalent to this statement? When I log as hr user and try this command it's not working: grant select on jobs to TestUser;
â€“Â Rocket128
1 hour ago

@Rocket128 - no. But the Database Idea is Under Review - Voting still open. See: community.oracle.com/ideas/2333 (requires registration to Vote)
â€“Â Michael Kutz
17 mins ago

add a commentÂ |Â

up vote
1
down vote

It is something Oracle could have solved ages ago. There is no simple command do it, so usually we just loop through the tables and grant object level privileges:

set serveroutput on
declare
 l_cmd clob;
begin
 for t in (select * from all_tables where owner = 'HR')
 loop
 begin
 l_cmd := 'grant select on "' || t.owner || '"."' || t.table_name || '" to new_user';
 execute immediate l_cmd;
 exception when others then dbms_output.put_line(l_cmd);
 end;
 end loop;
end;
/

Tables created after this in HR schema will not be visible to the other user, each time an object is created, the privileges should be granted on that.

answered 1 hour ago

Balazs Papp

23.8k2930

It is something Oracle could have solved ages ago. There is no simple command do it, so usually we just loop through the tables and grant object level privileges:

set serveroutput on
declare
 l_cmd clob;
begin
 for t in (select * from all_tables where owner = 'HR')
 loop
 begin
 l_cmd := 'grant select on "' || t.owner || '"."' || t.table_name || '" to new_user';
 execute immediate l_cmd;
 exception when others then dbms_output.put_line(l_cmd);
 end;
 end loop;
end;
/

Tables created after this in HR schema will not be visible to the other user, each time an object is created, the privileges should be granted on that.

answered 1 hour ago

Balazs Papp

23.8k2930

answered 1 hour ago

Balazs Papp

23.8k2930

answered 1 hour ago

Balazs Papp

23.8k2930

answered 1 hour ago

Balazs Papp

23.8k2930

Is there some sqlplus command equivalent to this statement? When I log as hr user and try this command it's not working: grant select on jobs to TestUser;
â€“Â Rocket128
1 hour ago

@Rocket128 - no. But the Database Idea is Under Review - Voting still open. See: community.oracle.com/ideas/2333 (requires registration to Vote)
â€“Â Michael Kutz
17 mins ago

add a commentÂ |Â

Is there some sqlplus command equivalent to this statement? When I log as hr user and try this command it's not working: grant select on jobs to TestUser;
â€“Â Rocket128
1 hour ago

@Rocket128 - no. But the Database Idea is Under Review - Voting still open. See: community.oracle.com/ideas/2333 (requires registration to Vote)
â€“Â Michael Kutz
17 mins ago

Is there some sqlplus command equivalent to this statement? When I log as hr user and try this command it's not working: grant select on jobs to TestUser;
â€“Â Rocket128
1 hour ago

@Rocket128 - no. But the Database Idea is Under Review - Voting still open. See: community.oracle.com/ideas/2333 (requires registration to Vote)
â€“Â Michael Kutz
17 mins ago

add a commentÂ |Â

Rocket128 is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Rocket128 is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Post as a guest

Name

Search This Blog

Iyfjky