On Aug 30, 1:09 pm, Brian Peasland <d..._at_nospam.peasland.net> wrote:
> Wally wrote:
> > Oracle 9.2.0.7
> > Windows 2003
>
> > We are going through a Certification and Accreditation process with
> > our databases. We are being dinged for grants that any schema owner is
> > giving on its objects to database roles. We were told that only the
> > SYSTEM user or a user with DBA privileges should grant the permissions
> > on any object to any role.
>
> > We have changed the process so that the user SYSTEM will log in and
> > grant the permissions but here is something we have noticed.
>
> > Lets say that the schema in question is SCOTT.
> > The table to grant SELECT permission on is the EMP table.
> > The role to grant permissions to is EMP_ACCESS_ROLE
>
> > 1. We log in as SYSTEM and run the following statement.
>
> > GRANT SELECT on SCOTT.EMP to EMP_ACCESS_ROLE;
>
> > 2. When we run
>
> > select * from DBA_TAB_PRIVS where table = 'EMP';
>
> > GRANTEE OWNER TABLE_NAME GRANTOR
> > PRIVILEGE
> > ---------------------------------- ------------
> > ------------------------ ----------------
> > ------------------------------------
> > EMP_ACCESS_ROLE SCOTT EMP SCOTT SELECT
>
> > We see that the GRANTOR is still the schema owner SCOTT. So when the
> > database is scanned again we are still being dinged for the schema
> > owner granting permission on an object to a role.
>
> > -------
>
> > 3. We then tried this (even though this is retarded and unnecessary
> > since SYSTEM has the SELECT permission to begin with)
>
> > GRANT SELECT ON SCOTT.EMP TO SYSTEM WITH GRANT OPTION;
>
> > 4. We then log in as SYSTEM and grant the permission again.
>
> > GRANT SELECT on SCOTT.EMP to EMP_ACCESS_ROLE;
>
> > 5. When we run
>
> > select * from DBA_TAB_PRIVS where table = 'EMP';
>
> > GRANTEE OWNER TABLE_NAME GRANTOR
> > PRIVILEGE
> > ---------------------------------- ------------
> > ------------------------ ----------------
> > ------------------------------------
> > EMP_ACCESS_ROLE SCOTT EMP SYSTEM SELECT
>
> > We see that the GRANTOR is now SYSTEM. But now we are being dinged
> > that the SYSTEM user has unnecessary permissions on the SCOTT schema
> > that it does not need to have, and also that the schema owner SCOTT is
> > still logging in to give permissions to other users.
> > --------------
>
> > Has anyone else run into this problem of the schema owner showing up
> > as the GRANTOR even though SYSTEM granted the permission. What was
> > your work around ?
>
> > Thanks in advance.
>
> Turn on auditing for GRANT statements in the database. Then when they
> ding you, you can pull up your audit trail and say "see....even though
> the Data Dictionary shows the grantor was SCOTT, it was in fact...SYSTEM".
>
> But then this brings up another point to my mind...should this type of
> auditing be done on an individual basis? In that case, multiple DBAs
> should not be sharing the SYSTEM account. Rather, they should have their
> own accounts. That way, when you audit the GRANT commands, you can
> denote the actual individual who has performed the operation.
>
> HTH,Brian
>
> --
> ===================================================================
>
> Brian Peasland
> d...@nospam.peasland.nethttp://www.peasland.net
>
> Remove the "nospam." from the email address to email me.
>
> "I can give it to you cheap, quick, and good.
> Now pick two out of the three" - Unknown
>
> --
> Posted via a free Usenet account fromhttp://www.teranews.com
Thanks for the info Brian. We'll see if having an audit trail will be
enough to satisfy them.
Received on Thu Aug 30 2007 - 12:35:39 CDT
