f



Oracle permissions issue Oracle 9.2.0.7/Win 2003

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.

0
wallyraju (50)
8/30/2007 3:42:12 PM
comp.databases.oracle.server 22978 articles. 1 followers. Post Follow

2 Replies
806 Views

Similar Articles

[PageSpeed] 12

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
dba@nospam.peasland.net
http://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 from http://www.teranews.com

0
dba (518)
8/30/2007 5:09:01 PM
On Aug 30, 1:09 pm, Brian Peasland <d...@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.

0
wallyraju (50)
8/30/2007 5:35:39 PM
Reply: