Oracle permissions issue Oracle 9.2.0.7/Win 2003
- From: Wally <wallyraju@xxxxxxxxx>
- Date: Thu, 30 Aug 2007 08:42:12 -0700
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.
.
- Follow-Ups:
- Re: Oracle permissions issue Oracle 9.2.0.7/Win 2003
- From: Brian Peasland
- Re: Oracle permissions issue Oracle 9.2.0.7/Win 2003
- Prev by Date: Re: ORA-04031: unable to allocate 2097152 bytes of shared memory ("shared pool","unknown object","hash-join subh","QERHJ Hash Table Entries")
- Next by Date: PHP/Oracle home problem ?
- Previous by thread: Query Improvement
- Next by thread: Re: Oracle permissions issue Oracle 9.2.0.7/Win 2003
- Index(es):
Relevant Pages
|
