Grant all the privileges to a user over all other user's objects in Oracle
I would like to know how to grant all the privileges to a user over all other user's objects in oracle. Greetings and many thanks
oracle permissions
asked 9 hours ago
miguel ramiresmiguel ramires
384
add a comment |
I would like to know how to grant all the privileges to a user over all other user's objects in oracle. Greetings and many thanks
oracle permissions
asked 9 hours ago
miguel ramiresmiguel ramires
384
add a comment |
I would like to know how to grant all the privileges to a user over all other user's objects in oracle. Greetings and many thanks
oracle permissions
asked 9 hours ago
miguel ramiresmiguel ramires
384
I would like to know how to grant all the privileges to a user over all other user's objects in oracle. Greetings and many thanks
oracle permissions
oracle permissions
asked 9 hours ago
miguel ramiresmiguel ramires
384
asked 9 hours ago
miguel ramiresmiguel ramires
384
asked 9 hours ago
miguel ramiresmiguel ramires
384
asked 9 hours ago
miguel ramiresmiguel ramires
384
asked 9 hours ago
miguel ramiresmiguel ramires
384
384
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Which privileges? Of course they are granted by using the appropriate GRANT command, but I suspect that is not what you are asking. You could use the ANY option (like GRANT SELECT ANY TABLE ...) but I strongly recommend against it as it violates the principle of granting least privilege necessary. I suspect what you really need is something like this:
set echo off feedback off header off pagesize 0
spool doit.sql
select 'grant select on table '||table_name||' to someuser;'
from dba_tables
where <whatever condition to filter the list of tables>
;
spool off
Then do a sanity check on the spooled file 'doit.sql', edit as necessary, then execute it. Some do it all in one pop with a PL/SQL loop, but I'd rather capture the commands first and be able to do the sanity check on it.
answered 9 hours ago
EdStevensEdStevens
95246
Something similar ... for example ... I have 3 schemes (ESQUEM1, ESQUEM2, ESQUEM3) of work with which an application interacts ... but besides these 3 schemes, 4 users were created (US1, US2, US3, US4) they will work with the application and the database, but these 4 users have to have all the permissions to work with the 3 users mentioned above, have full access to all their objects, tables, views ... everything ... that's what I need to know, try creating a role but it does not work for me.
– miguel ramires
6 hours ago
"it does not work for me" is 100% devoid of actionable information. FWIW, privs inherited from a role to not apply within a stored procedure, unless the procedure is compiled with inovkers rights, and that is usually seen as as much a security issue as granting an ALL privilege.
– EdStevens
6 hours ago
@EdStevens - 12c+ allows you togrant role_pkg_x to package invoker_rights_pkg_xeliminating the need to grant unnecessary privileges to the user.
– Michael Kutz
4 hours ago
@MichaelKutz - thanks for the pointer. That should work even better for the OP
– EdStevens
3 hours 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',
autoActivateHeartbeat: false,
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f230068%2fgrant-all-the-privileges-to-a-user-over-all-other-users-objects-in-oracle%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Which privileges? Of course they are granted by using the appropriate GRANT command, but I suspect that is not what you are asking. You could use the ANY option (like GRANT SELECT ANY TABLE ...) but I strongly recommend against it as it violates the principle of granting least privilege necessary. I suspect what you really need is something like this:
set echo off feedback off header off pagesize 0
spool doit.sql
select 'grant select on table '||table_name||' to someuser;'
from dba_tables
where <whatever condition to filter the list of tables>
;
spool off
Then do a sanity check on the spooled file 'doit.sql', edit as necessary, then execute it. Some do it all in one pop with a PL/SQL loop, but I'd rather capture the commands first and be able to do the sanity check on it.
answered 9 hours ago
EdStevensEdStevens
95246
Something similar ... for example ... I have 3 schemes (ESQUEM1, ESQUEM2, ESQUEM3) of work with which an application interacts ... but besides these 3 schemes, 4 users were created (US1, US2, US3, US4) they will work with the application and the database, but these 4 users have to have all the permissions to work with the 3 users mentioned above, have full access to all their objects, tables, views ... everything ... that's what I need to know, try creating a role but it does not work for me.
– miguel ramires
6 hours ago
"it does not work for me" is 100% devoid of actionable information. FWIW, privs inherited from a role to not apply within a stored procedure, unless the procedure is compiled with inovkers rights, and that is usually seen as as much a security issue as granting an ALL privilege.
– EdStevens
6 hours ago
@EdStevens - 12c+ allows you togrant role_pkg_x to package invoker_rights_pkg_xeliminating the need to grant unnecessary privileges to the user.
– Michael Kutz
4 hours ago
@MichaelKutz - thanks for the pointer. That should work even better for the OP
– EdStevens
3 hours ago
add a comment |
Which privileges? Of course they are granted by using the appropriate GRANT command, but I suspect that is not what you are asking. You could use the ANY option (like GRANT SELECT ANY TABLE ...) but I strongly recommend against it as it violates the principle of granting least privilege necessary. I suspect what you really need is something like this:
set echo off feedback off header off pagesize 0
spool doit.sql
select 'grant select on table '||table_name||' to someuser;'
from dba_tables
where <whatever condition to filter the list of tables>
;
spool off
Then do a sanity check on the spooled file 'doit.sql', edit as necessary, then execute it. Some do it all in one pop with a PL/SQL loop, but I'd rather capture the commands first and be able to do the sanity check on it.
answered 9 hours ago
EdStevensEdStevens
95246
Something similar ... for example ... I have 3 schemes (ESQUEM1, ESQUEM2, ESQUEM3) of work with which an application interacts ... but besides these 3 schemes, 4 users were created (US1, US2, US3, US4) they will work with the application and the database, but these 4 users have to have all the permissions to work with the 3 users mentioned above, have full access to all their objects, tables, views ... everything ... that's what I need to know, try creating a role but it does not work for me.
– miguel ramires
6 hours ago
"it does not work for me" is 100% devoid of actionable information. FWIW, privs inherited from a role to not apply within a stored procedure, unless the procedure is compiled with inovkers rights, and that is usually seen as as much a security issue as granting an ALL privilege.
– EdStevens
6 hours ago
@EdStevens - 12c+ allows you togrant role_pkg_x to package invoker_rights_pkg_xeliminating the need to grant unnecessary privileges to the user.
– Michael Kutz
4 hours ago
@MichaelKutz - thanks for the pointer. That should work even better for the OP
– EdStevens
3 hours ago
add a comment |
Which privileges? Of course they are granted by using the appropriate GRANT command, but I suspect that is not what you are asking. You could use the ANY option (like GRANT SELECT ANY TABLE ...) but I strongly recommend against it as it violates the principle of granting least privilege necessary. I suspect what you really need is something like this:
set echo off feedback off header off pagesize 0
spool doit.sql
select 'grant select on table '||table_name||' to someuser;'
from dba_tables
where <whatever condition to filter the list of tables>
;
spool off
Then do a sanity check on the spooled file 'doit.sql', edit as necessary, then execute it. Some do it all in one pop with a PL/SQL loop, but I'd rather capture the commands first and be able to do the sanity check on it.
answered 9 hours ago
EdStevensEdStevens
95246
Which privileges? Of course they are granted by using the appropriate GRANT command, but I suspect that is not what you are asking. You could use the ANY option (like GRANT SELECT ANY TABLE ...) but I strongly recommend against it as it violates the principle of granting least privilege necessary. I suspect what you really need is something like this:
set echo off feedback off header off pagesize 0
spool doit.sql
select 'grant select on table '||table_name||' to someuser;'
from dba_tables
where <whatever condition to filter the list of tables>
;
spool off
Then do a sanity check on the spooled file 'doit.sql', edit as necessary, then execute it. Some do it all in one pop with a PL/SQL loop, but I'd rather capture the commands first and be able to do the sanity check on it.
answered 9 hours ago
EdStevensEdStevens
95246
answered 9 hours ago
EdStevensEdStevens
95246
answered 9 hours ago
EdStevensEdStevens
95246
answered 9 hours ago
EdStevensEdStevens
95246
95246
Something similar ... for example ... I have 3 schemes (ESQUEM1, ESQUEM2, ESQUEM3) of work with which an application interacts ... but besides these 3 schemes, 4 users were created (US1, US2, US3, US4) they will work with the application and the database, but these 4 users have to have all the permissions to work with the 3 users mentioned above, have full access to all their objects, tables, views ... everything ... that's what I need to know, try creating a role but it does not work for me.
– miguel ramires
6 hours ago
"it does not work for me" is 100% devoid of actionable information. FWIW, privs inherited from a role to not apply within a stored procedure, unless the procedure is compiled with inovkers rights, and that is usually seen as as much a security issue as granting an ALL privilege.
– EdStevens
6 hours ago
@EdStevens - 12c+ allows you togrant role_pkg_x to package invoker_rights_pkg_xeliminating the need to grant unnecessary privileges to the user.
– Michael Kutz
4 hours ago
@MichaelKutz - thanks for the pointer. That should work even better for the OP
– EdStevens
3 hours ago
add a comment |
Something similar ... for example ... I have 3 schemes (ESQUEM1, ESQUEM2, ESQUEM3) of work with which an application interacts ... but besides these 3 schemes, 4 users were created (US1, US2, US3, US4) they will work with the application and the database, but these 4 users have to have all the permissions to work with the 3 users mentioned above, have full access to all their objects, tables, views ... everything ... that's what I need to know, try creating a role but it does not work for me.
– miguel ramires
6 hours ago
"it does not work for me" is 100% devoid of actionable information. FWIW, privs inherited from a role to not apply within a stored procedure, unless the procedure is compiled with inovkers rights, and that is usually seen as as much a security issue as granting an ALL privilege.
– EdStevens
6 hours ago
@EdStevens - 12c+ allows you togrant role_pkg_x to package invoker_rights_pkg_xeliminating the need to grant unnecessary privileges to the user.
– Michael Kutz
4 hours ago
@MichaelKutz - thanks for the pointer. That should work even better for the OP
– EdStevens
3 hours ago
Something similar ... for example ... I have 3 schemes (ESQUEM1, ESQUEM2, ESQUEM3) of work with which an application interacts ... but besides these 3 schemes, 4 users were created (US1, US2, US3, US4) they will work with the application and the database, but these 4 users have to have all the permissions to work with the 3 users mentioned above, have full access to all their objects, tables, views ... everything ... that's what I need to know, try creating a role but it does not work for me.
– miguel ramires
6 hours ago
Something similar ... for example ... I have 3 schemes (ESQUEM1, ESQUEM2, ESQUEM3) of work with which an application interacts ... but besides these 3 schemes, 4 users were created (US1, US2, US3, US4) they will work with the application and the database, but these 4 users have to have all the permissions to work with the 3 users mentioned above, have full access to all their objects, tables, views ... everything ... that's what I need to know, try creating a role but it does not work for me.
– miguel ramires
6 hours ago
"it does not work for me" is 100% devoid of actionable information. FWIW, privs inherited from a role to not apply within a stored procedure, unless the procedure is compiled with inovkers rights, and that is usually seen as as much a security issue as granting an ALL privilege.
– EdStevens
6 hours ago
"it does not work for me" is 100% devoid of actionable information. FWIW, privs inherited from a role to not apply within a stored procedure, unless the procedure is compiled with inovkers rights, and that is usually seen as as much a security issue as granting an ALL privilege.
– EdStevens
6 hours ago
@EdStevens - 12c+ allows you to
grant role_pkg_x to package invoker_rights_pkg_x eliminating the need to grant unnecessary privileges to the user.– Michael Kutz
4 hours ago
@EdStevens - 12c+ allows you to
grant role_pkg_x to package invoker_rights_pkg_x eliminating the need to grant unnecessary privileges to the user.– Michael Kutz
4 hours ago
@MichaelKutz - thanks for the pointer. That should work even better for the OP
– EdStevens
3 hours ago
@MichaelKutz - thanks for the pointer. That should work even better for the OP
– EdStevens
3 hours ago
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f230068%2fgrant-all-the-privileges-to-a-user-over-all-other-users-objects-in-oracle%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
