Forum Stats

  • 3,724,517 Users
  • 2,244,775 Discussions
  • 7,851,066 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

ORA-01950: no privileges on tablespace

918420
918420 Member Posts: 18
edited February 2012 in SQL & PL/SQL
Hi u all :)

I keep getting a no privileges error message every time i try to execute a procedure that suppose to insert data to different tables on different tablespaces.
When i created the user i gave him a default tablespace, and after it was created i also gave him the following privs :

SYS >grant unlimited tablespace to MGR;
SYS >ALTER USER MGR
2 QUOTA UNLIMITED ON TEL_AVIV_TBS;
SYS >ALTER USER MGR
2 QUOTA UNLIMITED ON JERUSALEM_TBS;

And still every time I try to execute the procedure i get :
Error report:

ORA-01950: no privileges on tablespace 'JERUSALEM_TBS'
ORA-06512: at "VOTE.VOTING_PROC", line 14
ORA-06512: at line 14
01950. 00000 - "no privileges on tablespace '%s'"
*Cause: User does not have privileges to allocate an extent in the
specified tablespace.
*Action: Grant the user the appropriate system privileges or grant the user
space resource on the tablespace.

I checked the OEM interface and see the following:

General
Name MGR
Profile DEFAULT_PROFILE
Authentication Password
Default Tablespace ELECTION_MASTER_TBS
Temporary Tablespace TEMP
Status UNLOCK
Default Consumer Group None
Roles
Role Admin Option Default
MANAGER N Y
System Privileges
System Privilege Admin Option
CREATE SESSION N
EXECUTE ANY PROCEDURE N
INSERT ANY TABLE N
SELECT ANY TABLE N
UNLIMITED TABLESPACE N
Object Privileges
Object Privilege Schema Object Grant Option
INSERT ELECTION_MASTER JER_VOTES N
INSERT ELECTION_MASTER TA_VOTES N
Quotas
Unlimited Tablespace System Privilege granted

Please help :)))

Itzik

Best Answer

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,215 Black Diamond
    edited February 2012 Accepted Answer
    915417 wrote:
    JER_VOTES <font color=red>JERUSALEM_TBS</font><br>
    TA_VOTES <font color=red>TEL_AVIV_TBS</font>
    Exactly what I said. Since table is owned by ELECTION_MASTER you MUST grant tablespace quota to ELECTION_MASTER. Issue:
    ALTER USER ELECTION_MASTER QUOTA UNLIMITED ON JERUSALEM_TBS;
    ALTER USER ELECTION_MASTER QUOTA UNLIMITED ON TEL_AVIV_TBS;
    SY.

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,215 Black Diamond
    By default stored procedure is created with definer rights which means it is executed under owner's security domain. So it is stored procedure owner not caller who needs tablespace privileges. Also, definer rights stored procedures do not honor roles. So question is who owns stored procedure and what privileges are directly granted to it?

    SY.
  • 918420
    918420 Member Posts: 18
    Hi man :)
    Thanks for the fast reply

    I gave both the procedure owner and the procedure executer privs to the tables directly and to tables.

    SYS >grant create session, select any table,insert any table, execute any procedure to vote;

    Still i get this error.

    Do u have more suggestions?

    thanx :)
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,215 Black Diamond
    Post your CREATE PROCEDURE statement execution in SQL*Plus so we see line numbers. Post stored procedure call statement in SQL*Plus along with all errors.

    SY.
  • 918420
    918420 Member Posts: 18
    This is the response i get when i execute the procedure through the user suppose to execute it (Not the user who created it)


    MGR >create or replace
    2 procedure VOTING_PROC (p_partid in number, p_areid in number, p_userid in varchar2)
    3 is
    4 v_EXISTS_IND NUMBER;
    5 begin
    6 select count(*) INTO v_EXISTS_IND from ELECTION_MASTER.PARTIES where party_id = p_partid;
    7 if v_EXISTS_IND = 0
    8 then raise_application_error (-20001, 'ILLEGAL PARTY - CHOOSE A LEGAL ONE');
    9 end if;
    10 if p_areid not in (1, 2)
    11 then DBMS_OUTPUT.PUT_LINE (p_areid);
    12 raise_application_error (-20001, 'CAN NOT ELECT ON THIS AREA');
    13 end if;
    14 if p_areid = 1 and p_userid = 'USER1' or p_userid = 'USER2' or p_userid = 'USER3' or p_userid = 'USER4' or p_userid = 'USER5'
    15 then insert into ELECTION_MASTER.JER_VOTES ( party_id, votedate, area_id)
    16 values ( p_partid, sysdate, p_areid);
    17 elsif
    18 p_areid = 2 and p_userid = 'USER6' or p_userid = 'USER7' or p_userid = 'USER8' or p_userid = 'USER9' or p_userid = 'USER10'
    19 then
    20 insert into ELECTION_MASTER.TA_VOTES ( party_id, votedate, area_id)
    21 values ( p_partid, sysdate, p_areid);
    22 else
    23 raise_application_error (-20002, 'YOU ARE AN ALIEN');
    24 end if;
    25 end;
    26 /
    create or replace
    *
    ERROR at line 1:
    ORA-01031: insufficient privileges

    This is the error messgae shows when i execute it in developer:

    Error report:
    ORA-01031: insufficient privileges
    01031. 00000 - "insufficient privileges"
    *Cause: An attempt was made to change the current username or password
    without the appropriate privilege. This error also occurs if
    attempting to install a database without the necessary operating
    system privileges.
    When Trusted Oracle is configure in DBMS MAC, this error may occur
    if the user was granted the necessary privilege at a higher label
    than the current login.
    *Action: Ask the database administrator to perform the operation or grant
    the required privileges.
    For Trusted Oracle users getting this error although granted the
    the appropriate privilege at a higher label, ask the database
    administrator to regrant the privilege at the appropriate label.


    :)
  • 918420
    918420 Member Posts: 18
    I call the procedure with this anonymous block

    DECLARE
    p_partid number(5);
    p_areid number(5);
    P_userid varchar2(10);
    BEGIN
    FOR i IN 1..5
    LOOP
    p_partid := round(dbms_random.value (1, 5));
    p_areid := round(dbms_random.value (1, 2));
    P_userid := ('USER' || to_char(round(dbms_random.value(1, 10))));
    dbms_output.put_line(p_partid||'p_partid');
    dbms_output.put_line(p_areid ||'p_areid ');
    dbms_output.put_line(P_userid ||'P_userid ');
    vote.VOTING_PROC
    (p_partid, p_areid, P_userid);
    END LOOP;
    COMMIT;
    END;
    /

    and get this:

    Error report:
    ORA-01950: no privileges on tablespace 'TEL_AVIV_TBS'
    ORA-06512: at "VOTE.VOTING_PROC", line 19
    ORA-06512: at line 14
    01950. 00000 - "no privileges on tablespace '%s'"
    *Cause: User does not have privileges to allocate an extent in the
    specified tablespace.
    *Action: Grant the user the appropriate system privileges or grant the user
    space resource on the tablespace.

    :)
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,215 Black Diamond
    >

    How can you execute SP that doesn't compile? Unless you are confusing CREATE procedure with EXECUTE procedure. So let start with:

    1. Who is SP owner? Login as that user to SQL*Plus and create procedure. Post CREATE PROCEDURE statement execution. There shouldn't be any compilation errors.
    2. What user is calling SP? Login as that user to SQL*Plus and post execution of anonymous block that calls SP.

    SY.
  • 918420
    918420 Member Posts: 18
    The procedure was already compiled successfully by The user who created it "VOTE".

    The user "MGR" is trying to execute it with the anonymous block and gets the error message i pasted before.
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,215 Black Diamond
    Then, based on:
    Error report:
    ORA-01950: no privileges on tablespace 'TEL_AVIV_TBS'
    ORA-06512: at &quot;VOTE.VOTING_PROC&quot;, line 19
    based on procedure VOTING_PROC code you posted line 19 is:
    insert into ELECTION_MASTER.TA_VOTES ( party_id, votedate, area_id)
    Now, this is a good one. User VOTE owns procedure VOTING_PROC. So even though insert into ELECTION_MASTER.TA_VOTES is made on VOTE behalf, it is user ELECTION_MASTER who needs tablespace quota. But if user ELECTION_MASTER has no tablespace quota, how did user ELECTION_MASTER created table TA_VOTES in the first place? This means you are on 11g. And on 11g default is deferred segment creation. In other words, when user creates a table segment is not created. Segment is created only with first attempt to insert into that table. Only then tablespace quota is needed. So in 11g you can create a stored procedure which inserts into a table where table owner has no quota, and SP will compile OK. Anyway, grant user ELECTION_MASTER tablespace quota and you will be fine.

    SY.
  • 918420
    918420 Member Posts: 18
    I really thank u on all your effort dude :)

    ELECTION_MASTER already has tablespace quota

    SYS >create user ELECTION_MASTER
    2 identified by ELECTION_MASTER
    3 default tablespace ELECTION_MASTER_TBS
    4 temporary tablespace TEMP
    5 quota unlimited on ELECTION_MASTER_TBS;
  • 918420
    918420 Member Posts: 18
    I gave all users default tablespace and quota except from VOTE.
    I gave vote these quota after i got the error message


    SYS >ALTER USER VOTE
    2 QUOTA UNLIMITED ON TEL_AVIV_TBS;

    User altered.

    SYS >ALTER USER VOTE
    2 QUOTA UNLIMITED ON JERUSALEM_TBS;
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,215 Black Diamond
    Issue the following:
    select  table_name,
            tablespace_name
      from  dba_tables
      where owner = 'ELECTION_MASTER'
      order by table_name
    /
    Post results.

    SY.
  • 918420
    918420 Member Posts: 18
    SYS >select table_name,
    2 tablespace_name
    3 from dba_tables
    4 where owner = 'ELECTION_MASTER'
    5 order by table_name
    6 /

    TABLE_NAME TABLESPACE_NAME
    ------------------------------ ------------------------------
    JER_VOTES JERUSALEM_TBS
    PARTIES ELECTION_MASTER_TBS
    RESULTS ELECTION_MASTER_TBS
    TA_VOTES TEL_AVIV_TBS

    SYS >
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,215 Black Diamond
    edited February 2012 Accepted Answer
    915417 wrote:
    JER_VOTES <font color=red>JERUSALEM_TBS</font><br>
    TA_VOTES <font color=red>TEL_AVIV_TBS</font>
    Exactly what I said. Since table is owned by ELECTION_MASTER you MUST grant tablespace quota to ELECTION_MASTER. Issue:
    ALTER USER ELECTION_MASTER QUOTA UNLIMITED ON JERUSALEM_TBS;
    ALTER USER ELECTION_MASTER QUOTA UNLIMITED ON TEL_AVIV_TBS;
    SY.
  • 918420
    918420 Member Posts: 18
    Awesome.....It worked :)
    Thanx a lot man u'r the best :)
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,215 Black Diamond
    Still, question remains why the two tales were created not in user ELECTION_MASTER default tablespace? There is a good chance it wasn't the intention. Then, even though granting quota helped, correct way would be moving the tables into proper tablespace:
    ALTER TABLE ELECTION_MASTER.JER_VOTES MOVE
     TABLESPACE ELECTION_MASTER_TBS;
    ALTER TABLE ELECTION_MASTER.TA_VOTES MOVE
     TABLESPACE ELECTION_MASTER_TBS;
    and then revoking quotas you granted on other tablespaces.

    SY.
This discussion has been closed.