restrict privileges

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Frank Swarbrick
    #1

    restrict privileges

    I feel like an idiot for asking this, but neither I nor my DBA can figure
    this out.
    How do you create a database object that does not implicitly allow access by
    any defined user?

    For instance, I have a user who has been granted only the CONNECT privilege
    to a certain database. But he is still able to do selects and updates and
    other things to tables created by another user. Everything I've read
    indicates this should not be allowed; that the user must be granted these
    privileges explicitly. What are we missing?

    =============== =============== =============== ===============
    About DB2 Administration Tools Environment
    =============== =============== =============== ===============
    DB2 administration tools level:
    Product identifier SQL09050
    Level identifier 03010107
    Level DB2 v9.5.0.808
    Build level s071001
    PTF NT3295
    =============== =============== =============== ===============
    Java development kit (JDK):
    Level IBM Corporation 1.5.0
    =============== =============== =============== ===============


    Here's some more things that show what I am attempting:
    connect to securedb user fswarbri using

    Database Connection Information

    Database server = DB2/NT 9.5.0
    SQL authorization ID = FSWARBRI
    Local database alias = SECUREDB


    SELECT * FROM SYSIBMADM.PRIVI LEGES WHERE OBJECTNAME = 'SECTEST1'

    AUTHID AUTHIDTYPE PRIVILEGE GRANTABLE OBJECTSCHEMA OBJECTNAME OBJECTTYPE

    ------------------ ----------- --------- ------------ ---------- ----------
    OPSUSER U UPDATE Y OPSUSER SECTEST1 TABLE

    OPSUSER U REFERENCE Y OPSUSER SECTEST1 TABLE

    OPSUSER U SELECT Y OPSUSER SECTEST1 TABLE

    OPSUSER U INSERT Y OPSUSER SECTEST1 TABLE

    OPSUSER U INDEX Y OPSUSER SECTEST1 TABLE

    OPSUSER U DELETE Y OPSUSER SECTEST1 TABLE

    OPSUSER U ALTER Y OPSUSER SECTEST1 TABLE

    OPSUSER U CONTROL N OPSUSER SECTEST1 TABLE


    8 record(s) selected.


    SELECT * FROM OPSUSER.SECTEST 1

    COL1
    --------------------
    123
    456
    987

    3 record(s) selected.

    As you can see, only OPSUSER has any privileges on OPSUSER.SECTEST 1, and yet
    user FSWARBRI is able to query on the table (and update it, for that
    matter).

    Thanks!

    Frank

    Tags: None
    • jefftyzzer
      #2
      Re: restrict privileges

      On Mar 4, 12:02 pm, "Frank Swarbrick" <Frank.Swarbr.. .@efirstbank.co m>
      wrote:
      I feel like an idiot for asking this, but neither I nor my DBA can figure
      this out.
      How do you create a database object that does not implicitly allow access by
      any defined user?
      >
      For instance, I have a user who has been granted only the CONNECT privilege
      to a certain database. But he is still able to do selects and updates and
      other things to tables created by another user. Everything I've read
      indicates this should not be allowed; that the user must be granted these
      privileges explicitly. What are we missing?
      >
      =============== =============== =============== ===============
      About DB2 Administration Tools Environment
      =============== =============== =============== ===============
      DB2 administration tools level:
      Product identifier SQL09050
      Level identifier 03010107
      Level DB2 v9.5.0.808
      Build level s071001
      PTF NT3295
      =============== =============== =============== ===============
      Java development kit (JDK):
      Level IBM Corporation 1.5.0
      =============== =============== =============== ===============
      >
      Here's some more things that show what I am attempting:
      connect to securedb user fswarbri using
      >
      Database Connection Information
      >
      Database server = DB2/NT 9.5.0
      SQL authorization ID = FSWARBRI
      Local database alias = SECUREDB
      >
      SELECT * FROM SYSIBMADM.PRIVI LEGES WHERE OBJECTNAME = 'SECTEST1'
      >
      AUTHID AUTHIDTYPE PRIVILEGE GRANTABLE OBJECTSCHEMA OBJECTNAME OBJECTTYPE
      >
      ------------------ ----------- --------- ------------ ---------- ----------
      OPSUSER U UPDATE Y OPSUSER SECTEST1 TABLE
      >
      OPSUSER U REFERENCE Y OPSUSER SECTEST1 TABLE
      >
      OPSUSER U SELECT Y OPSUSER SECTEST1 TABLE
      >
      OPSUSER U INSERT Y OPSUSER SECTEST1 TABLE
      >
      OPSUSER U INDEX Y OPSUSER SECTEST1 TABLE
      >
      OPSUSER U DELETE Y OPSUSER SECTEST1 TABLE
      >
      OPSUSER U ALTER Y OPSUSER SECTEST1 TABLE
      >
      OPSUSER U CONTROL N OPSUSER SECTEST1 TABLE
      >
      8 record(s) selected.
      >
      SELECT * FROM OPSUSER.SECTEST 1
      >
      COL1
      --------------------
      123
      456
      987
      >
      3 record(s) selected.
      >
      As you can see, only OPSUSER has any privileges on OPSUSER.SECTEST 1, and yet
      user FSWARBRI is able to query on the table (and update it, for that
      matter).
      >
      Thanks!
      >
      Frank
      Could it be that you need to explicitly remove/revoke this user from
      the PUBLIC role?

      --Jeff

        Comment

        • Frank Swarbrick
          #3
          Re: restrict privileges

          Now this is interesting. The DBA went in to one of the instances running on
          Linux and did the following from Control Center:
          Selected the group PUBLIC
          Revoked all privileges for all types of objects (specifically SCHEMA and
          PACKAGES. I don't think any tables has explicit rights to any tables.)
          Granted back privileges for all packages owned by NULLID.

          He also had to remove the user at an OS level from a certain group
          (db2admin?).

          And now the user is no longer able to access tables to which that user has
          not been granted privileges.

          But I tried the same thing on my Windows PC running DB2 Express-C (9.5) and
          it is still not enforcing the restrictions.

          Huh?

          Frank

            Comment

            • Serge Rielau
              #4
              Re: restrict privileges

              If a user has privileges to SELECT, DELETE, UPDATE, or INSERT data
              without having gotten in diractly then the access must have been granted
              to either a group (or role) the user belongs to or PUBLIC.

              So, take a look at that end.
              --
              Serge Rielau
              DB2 Solutions Development
              IBM Toronto Lab

                Comment

                • Serge Rielau
                  #5
                  Re: restrict privileges

                  Frank Swarbrick wrote:
                  db2admin? Well....
                  If you want to hide data from dbadm you will need to use LBAC.
                  (or encrypt)

                  Cheers
                  Serge
                  --
                  Serge Rielau
                  DB2 Solutions Development
                  IBM Toronto Lab

                    Comment

                    • Frank Swarbrick
                      #6
                      Re: restrict privileges

                      >>On 3/4/2008 at 2:42 PM, in message
                      <635u1hF267lecU 1@mid.individua l.net>,
                      Serge Rielau<srielau@ ca.ibm.comwrote :
                      Frank Swarbrick wrote:
                      db2admin? Well....
                      If you want to hide data from dbadm you will need to use LBAC.
                      (or encrypt)
                      No, I was saying that the db2 removed the 'restricted' user for a certain
                      Linux group, and I believe that group was named 'db2admins'. But I'm
                      probably wrong...

                        Comment

                        • Frank Swarbrick
                          #7
                          Re: restrict privileges

                          Wow, even odder. Now things seem to be working fine on my work PC. I am
                          able to restrict the PUBLIC group appropriately and grant rights to specific
                          users as necessary.

                          Maybe last night's reboot cleared things up. Who knows! Oh well. I still
                          have no idea what I did to cause the problem, but...

                          Frank

                            Comment

                            Previous template Next
                            Working...