Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Beating a dead horse on privileges

    I feel like I'm going nuts. What am I missing about privileges here?

    Code:
    SQL> connect roccusr@dev
    Enter password: ********
    Connected.
    SQL> select count(*) from cstm.b1permit_v;
    select count(*) from cstm.b1permit_v
                                    *
    ERROR at line 1:
    ORA-01031: insufficient privileges
    
    
    SQL> connect drfr@dev
    Enter password: **********
    Connected.
    SQL> select count(*) from cstm.b1permit_v;
    
      COUNT(*)
    ----------
         38073
    
    SQL> grant all on cstm.b1permit_v to roccusr;
    
    Grant succeeded.
    
    SQL> connect roccusr@dev
    Enter password: ********
    Connected.
    SQL> select count(*) from cstm.b1permit_v;
    select count(*) from cstm.b1permit_v
                                    *
    ERROR at line 1:
    ORA-01031: insufficient privileges
    Why is roccusr unable to see the contents of this view? The VIEW owner can see them also:

    Code:
    SQL> connect cstm@dev
    Enter password: ******
    Connected.
    SQL>  select count(*) from cstm.b1permit_v;
    
      COUNT(*)
    ----------
         38073
    Thanks,
    --=Chuck
    Last edited by chuck_forbes; 08-12-10 at 19:56.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Why is roccusr unable to see the contents of this view?
    insufficient privilege
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074

    What else could I grant, other than GRANT ALL?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >What else could I grant, other than GRANT ALL?
    Compare & contrast the schema that work against the schema which throws error.
    "Obviously" a difference exists & most likely the cause of the different results/behavior.

    What datatype do SELECT succeed?
    What datatype throw error?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    This:
    Code:
    grant all on cstm.b1permit_v to roccusr;
    was executed when you were connected as DRFR. It means that DRFR was granted privileges (from CSTM) "with grant option", right? Otherwise, DRFR wouldn't be able to grant privleges on CSTM's objects to ROCCUSR. Right?

    As it is a view we are talking about, does it contain "something" that causes the error? Perhaps a function owned by someone else who didn't grant privileges to everyone?

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Or maybe...

    Expanding on Littlefoot's post:

    Does the view have underlying tables belonging maybe to another schema?
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I know in SQL Server you can deny permissions on an object. After a deny is placed, you can not read the data until the deny is revoked. Is there a similar thing in Oracle permissions?

  8. #8
    Join Date
    Dec 2003
    Posts
    1,074
    Ugh. Ok. In the past we've given the SCHEMAs which contain application data the SELECT ANY TABLE grant. In this case, we were trying to limit setup to only those privileges that were necessary.

    The DRFR account has DBA access, and that ended up obscuring the actual problem. When I logged in as the object owners, then I got the error which helped repair the problem (the view in the CSTM schema was referencing a single table in the ACLA schema, something that I didn't think was a problem since SELECT was granted directly to CSTM for that table):

    Code:
    SQL> connect acla@dev
    Enter password: ******
    Connected.
    SQL> select count(*) from acla.b1permit;
    
      COUNT(*)
    ----------
         50271
    
    SQL> connect cstm@dev
    Enter password: ******
    Connected.
    SQL> select count(*) from acla.b1permit;
    
      COUNT(*)
    ----------
         50271
    
    SQL> create view myview1 as select * from acla.b1permit;
    
    View created.
    
    SQL> select count(*) from cstm.myview1;
    
      COUNT(*)
    ----------
         50271
    
    SQL> grant select on cstm.myview1 to roccusr;
    grant select on cstm.myview1 to roccusr
                               *
    ERROR at line 1:
    ORA-01720: grant option does not exist for 'ACLA.B1PERMIT'
    
    
    SQL> connect acla@dev
    Enter password: ******
    Connected.
    SQL> grant select on acla.b1permit to cstm with grant option;
    
    Grant succeeded.
    
    SQL> connect cstm@dev
    Enter password: ******
    Connected.
    SQL> grant select on cstm.myview1 to roccusr;
    
    Grant succeeded.
    
    SQL> connect roccusr@dev
    Enter password: ********
    Connected.
    SQL> select count(*) from cstm.myview1;
    
      COUNT(*)
    ----------
         50271
    Thanks so much for the help. It gave me enough information to hunt down the problem and fix it.

    --=Chuck

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •