ORA-01031: insufficient privileges - while creating new user
up vote
0
down vote
favorite
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
oracle forms apex
asked Nov 17 at 10:39
Nishan
12811
add a comment |
up vote
0
down vote
favorite
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
oracle forms apex
asked Nov 17 at 10:39
Nishan
12811
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 at 11:22
Where should I run that command. Since it comes with the errortable or view does not exist
– Nishan
Nov 17 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 at 11:44
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
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
oracle forms apex
asked Nov 17 at 10:39
Nishan
12811
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
oracle forms apex
oracle forms apex
asked Nov 17 at 10:39
Nishan
12811
asked Nov 17 at 10:39
Nishan
12811
asked Nov 17 at 10:39
Nishan
12811
asked Nov 17 at 10:39
Nishan
12811
asked Nov 17 at 10:39
Nishan
12811
12811
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 at 11:22
Where should I run that command. Since it comes with the errortable or view does not exist
– Nishan
Nov 17 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 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 at 11:22
Where should I run that command. Since it comes with the errortable or view does not exist
– Nishan
Nov 17 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 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 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 at 11:22
Where should I run that command. Since it comes with the error
table or view does not exist– Nishan
Nov 17 at 11:31
Where should I run that command. Since it comes with the error
table or view does not exist– Nishan
Nov 17 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 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 at 11:44
add a comment |
1 Answer
1
active
oldest
votes
up vote
1
down vote
accepted
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 at 16:58
Littlefoot
18.1k51333
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
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 at 16:58
Littlefoot
18.1k51333
add a comment |
up vote
1
down vote
accepted
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 at 16:58
Littlefoot
18.1k51333
add a comment |
up vote
1
down vote
accepted
up vote
1
down vote
accepted
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 at 16:58
Littlefoot
18.1k51333
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 at 16:58
Littlefoot
18.1k51333
answered Nov 17 at 16:58
Littlefoot
18.1k51333
answered Nov 17 at 16:58
Littlefoot
18.1k51333
answered Nov 17 at 16:58
Littlefoot
18.1k51333
18.1k51333
add a comment |
add a comment |
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 at 11:22
Where should I run that command. Since it comes with the error
table or view does not exist– Nishan
Nov 17 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 at 11:44