ORA-01031: insufficient privileges - while creating new user
I have situation where user will create a new users and give a grant to them but I was unable to create a new user via Apex.
I logged in through system and I have already given a grant to create user to system via backend. My granting command looks like
grant create user to system with admin option; but unable to grant.
But if I logged in through SQL Command Line and create a new user it allowed me to create but not via Apex
What am I doing wrong please help me out.
Note I am using apex form to add a new user
forms oracle-apex
edited Nov 24 '18 at 18:08
Mihai Chelaru
2,247101222
asked Nov 17 '18 at 10:39
NishanNishan
15114
add a comment |
I have situation where user will create a new users and give a grant to them but I was unable to create a new user via Apex.
I logged in through system and I have already given a grant to create user to system via backend. My granting command looks like
grant create user to system with admin option; but unable to grant.
But if I logged in through SQL Command Line and create a new user it allowed me to create but not via Apex
What am I doing wrong please help me out.
Note I am using apex form to add a new user
forms oracle-apex
edited Nov 24 '18 at 18:08
Mihai Chelaru
2,247101222
asked Nov 17 '18 at 10:39
NishanNishan
15114
Perhaps the connection being used via APEX has not re-connected since the grant was issued. If possible try toSELECT * FROM SESSIONS_PRIVSandSESSION_ROLESto see what grants are active. You might also like to to try (temporarily) seeing if granting DBA to the APEX user overcomes this, and if so, you would need to delve deeper as to what other privilege contained within the DBA role is required if you want to avoid giving them full DBA privs.
– TenG
Nov 17 '18 at 11:22
Where should I run that command. Since it comes with the errortable or view does not exist
– Nishan
Nov 17 '18 at 11:31
I would write a test page in your APEX app that lists the results of these queries , since that is the environment that is having the issue. You can run it in SQLPLUS as the same user and then compare the results.
– TenG
Nov 17 '18 at 11:44
add a comment |
I have situation where user will create a new users and give a grant to them but I was unable to create a new user via Apex.
I logged in through system and I have already given a grant to create user to system via backend. My granting command looks like
grant create user to system with admin option; but unable to grant.
But if I logged in through SQL Command Line and create a new user it allowed me to create but not via Apex
What am I doing wrong please help me out.
Note I am using apex form to add a new user
forms oracle-apex
edited Nov 24 '18 at 18:08
Mihai Chelaru
2,247101222
asked Nov 17 '18 at 10:39
NishanNishan
15114
I have situation where user will create a new users and give a grant to them but I was unable to create a new user via Apex.
I logged in through system and I have already given a grant to create user to system via backend. My granting command looks like
grant create user to system with admin option; but unable to grant.
But if I logged in through SQL Command Line and create a new user it allowed me to create but not via Apex
What am I doing wrong please help me out.
Note I am using apex form to add a new user
forms oracle-apex
forms oracle-apex
edited Nov 24 '18 at 18:08
Mihai Chelaru
2,247101222
asked Nov 17 '18 at 10:39
NishanNishan
15114
edited Nov 24 '18 at 18:08
Mihai Chelaru
2,247101222
asked Nov 17 '18 at 10:39
NishanNishan
15114
edited Nov 24 '18 at 18:08
Mihai Chelaru
2,247101222
edited Nov 24 '18 at 18:08
Mihai Chelaru
2,247101222
edited Nov 24 '18 at 18:08
Mihai Chelaru
2,247101222
2,247101222
asked Nov 17 '18 at 10:39
NishanNishan
15114
asked Nov 17 '18 at 10:39
NishanNishan
15114
asked Nov 17 '18 at 10:39
NishanNishan
15114
15114
Perhaps the connection being used via APEX has not re-connected since the grant was issued. If possible try toSELECT * FROM SESSIONS_PRIVSandSESSION_ROLESto see what grants are active. You might also like to to try (temporarily) seeing if granting DBA to the APEX user overcomes this, and if so, you would need to delve deeper as to what other privilege contained within the DBA role is required if you want to avoid giving them full DBA privs.
– TenG
Nov 17 '18 at 11:22
Where should I run that command. Since it comes with the errortable or view does not exist
– Nishan
Nov 17 '18 at 11:31
I would write a test page in your APEX app that lists the results of these queries , since that is the environment that is having the issue. You can run it in SQLPLUS as the same user and then compare the results.
– TenG
Nov 17 '18 at 11:44
add a comment |
Perhaps the connection being used via APEX has not re-connected since the grant was issued. If possible try toSELECT * FROM SESSIONS_PRIVSandSESSION_ROLESto see what grants are active. You might also like to to try (temporarily) seeing if granting DBA to the APEX user overcomes this, and if so, you would need to delve deeper as to what other privilege contained within the DBA role is required if you want to avoid giving them full DBA privs.
– TenG
Nov 17 '18 at 11:22
Where should I run that command. Since it comes with the errortable or view does not exist
– Nishan
Nov 17 '18 at 11:31
I would write a test page in your APEX app that lists the results of these queries , since that is the environment that is having the issue. You can run it in SQLPLUS as the same user and then compare the results.
– TenG
Nov 17 '18 at 11:44
Perhaps the connection being used via APEX has not re-connected since the grant was issued. If possible try to
SELECT * FROM SESSIONS_PRIVS and SESSION_ROLES to see what grants are active. You might also like to to try (temporarily) seeing if granting DBA to the APEX user overcomes this, and if so, you would need to delve deeper as to what other privilege contained within the DBA role is required if you want to avoid giving them full DBA privs.– TenG
Nov 17 '18 at 11:22
Perhaps the connection being used via APEX has not re-connected since the grant was issued. If possible try to
SELECT * FROM SESSIONS_PRIVS and SESSION_ROLES to see what grants are active. You might also like to to try (temporarily) seeing if granting DBA to the APEX user overcomes this, and if so, you would need to delve deeper as to what other privilege contained within the DBA role is required if you want to avoid giving them full DBA privs.– TenG
Nov 17 '18 at 11:22
Where should I run that command. Since it comes with the error
table or view does not exist– Nishan
Nov 17 '18 at 11:31
Where should I run that command. Since it comes with the error
table or view does not exist– Nishan
Nov 17 '18 at 11:31
I would write a test page in your APEX app that lists the results of these queries , since that is the environment that is having the issue. You can run it in SQLPLUS as the same user and then compare the results.
– TenG
Nov 17 '18 at 11:44
I would write a test page in your APEX app that lists the results of these queries , since that is the environment that is having the issue. You can run it in SQLPLUS as the same user and then compare the results.
– TenG
Nov 17 '18 at 11:44
add a comment |
1 Answer
1
active
oldest
votes
Database user named SYSTEM owns the database. It can create users without you granting it that privilege.
Saying that you logged in (to Apex) as system: I'd suggest you not to do that. Leave both SYS and SYSTEM alone. They are special, you don't want to mess up with them.
Create a new user (through SQL*Plus), grant it create user privilege and use it for such a purpose. Just for testing, that's what I did with the HR user:
- I have Apex 4.0.2 which comes with Oracle 11g XE.
- There's the
HRdatabase user for which I've created an Apex workspace. - Logged in to Apex as
HR, I created a page with a single item:P3_USERNAMEand a button
then I created a process that fires when I push the button. The process looks like this:
begin
execute immediate 'create user ' || :P3_USERNAME || ' identified by x';
end;
ran the page, entered
xxxinto the item and pressed a button.- checked
ALL_USERSand - here it is; userxxxis here
Try to do the same. Should be OK.
answered Nov 17 '18 at 16:58
LittlefootLittlefoot
22.9k71533
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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%2fstackoverflow.com%2fquestions%2f53350408%2fora-01031-insufficient-privileges-while-creating-new-user%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
Database user named SYSTEM owns the database. It can create users without you granting it that privilege.
Saying that you logged in (to Apex) as system: I'd suggest you not to do that. Leave both SYS and SYSTEM alone. They are special, you don't want to mess up with them.
Create a new user (through SQL*Plus), grant it create user privilege and use it for such a purpose. Just for testing, that's what I did with the HR user:
- I have Apex 4.0.2 which comes with Oracle 11g XE.
- There's the
HRdatabase user for which I've created an Apex workspace. - Logged in to Apex as
HR, I created a page with a single item:P3_USERNAMEand a button
then I created a process that fires when I push the button. The process looks like this:
begin
execute immediate 'create user ' || :P3_USERNAME || ' identified by x';
end;
ran the page, entered
xxxinto the item and pressed a button.- checked
ALL_USERSand - here it is; userxxxis here
Try to do the same. Should be OK.
answered Nov 17 '18 at 16:58
LittlefootLittlefoot
22.9k71533
add a comment |
Database user named SYSTEM owns the database. It can create users without you granting it that privilege.
Saying that you logged in (to Apex) as system: I'd suggest you not to do that. Leave both SYS and SYSTEM alone. They are special, you don't want to mess up with them.
Create a new user (through SQL*Plus), grant it create user privilege and use it for such a purpose. Just for testing, that's what I did with the HR user:
- I have Apex 4.0.2 which comes with Oracle 11g XE.
- There's the
HRdatabase user for which I've created an Apex workspace. - Logged in to Apex as
HR, I created a page with a single item:P3_USERNAMEand a button
then I created a process that fires when I push the button. The process looks like this:
begin
execute immediate 'create user ' || :P3_USERNAME || ' identified by x';
end;
ran the page, entered
xxxinto the item and pressed a button.- checked
ALL_USERSand - here it is; userxxxis here
Try to do the same. Should be OK.
answered Nov 17 '18 at 16:58
LittlefootLittlefoot
22.9k71533
add a comment |
Database user named SYSTEM owns the database. It can create users without you granting it that privilege.
Saying that you logged in (to Apex) as system: I'd suggest you not to do that. Leave both SYS and SYSTEM alone. They are special, you don't want to mess up with them.
Create a new user (through SQL*Plus), grant it create user privilege and use it for such a purpose. Just for testing, that's what I did with the HR user:
- I have Apex 4.0.2 which comes with Oracle 11g XE.
- There's the
HRdatabase user for which I've created an Apex workspace. - Logged in to Apex as
HR, I created a page with a single item:P3_USERNAMEand a button
then I created a process that fires when I push the button. The process looks like this:
begin
execute immediate 'create user ' || :P3_USERNAME || ' identified by x';
end;
ran the page, entered
xxxinto the item and pressed a button.- checked
ALL_USERSand - here it is; userxxxis here
Try to do the same. Should be OK.
answered Nov 17 '18 at 16:58
LittlefootLittlefoot
22.9k71533
Database user named SYSTEM owns the database. It can create users without you granting it that privilege.
Saying that you logged in (to Apex) as system: I'd suggest you not to do that. Leave both SYS and SYSTEM alone. They are special, you don't want to mess up with them.
Create a new user (through SQL*Plus), grant it create user privilege and use it for such a purpose. Just for testing, that's what I did with the HR user:
- I have Apex 4.0.2 which comes with Oracle 11g XE.
- There's the
HRdatabase user for which I've created an Apex workspace. - Logged in to Apex as
HR, I created a page with a single item:P3_USERNAMEand a button
then I created a process that fires when I push the button. The process looks like this:
begin
execute immediate 'create user ' || :P3_USERNAME || ' identified by x';
end;
ran the page, entered
xxxinto the item and pressed a button.- checked
ALL_USERSand - here it is; userxxxis here
Try to do the same. Should be OK.
answered Nov 17 '18 at 16:58
LittlefootLittlefoot
22.9k71533
answered Nov 17 '18 at 16:58
LittlefootLittlefoot
22.9k71533
answered Nov 17 '18 at 16:58
LittlefootLittlefoot
22.9k71533
answered Nov 17 '18 at 16:58
LittlefootLittlefoot
22.9k71533
22.9k71533
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f53350408%2fora-01031-insufficient-privileges-while-creating-new-user%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
Perhaps the connection being used via APEX has not re-connected since the grant was issued. If possible try to
SELECT * FROM SESSIONS_PRIVSandSESSION_ROLESto see what grants are active. You might also like to to try (temporarily) seeing if granting DBA to the APEX user overcomes this, and if so, you would need to delve deeper as to what other privilege contained within the DBA role is required if you want to avoid giving them full DBA privs.– TenG
Nov 17 '18 at 11:22
Where should I run that command. Since it comes with the error
table or view does not exist– Nishan
Nov 17 '18 at 11:31
I would write a test page in your APEX app that lists the results of these queries , since that is the environment that is having the issue. You can run it in SQLPLUS as the same user and then compare the results.
– TenG
Nov 17 '18 at 11:44