Home » RDBMS Server » Security » SYSTEM schema security (Oracle 10.2.0.3, HP-UX 11.23)
icon5.gif  SYSTEM schema security [message #415654] Tue, 28 July 2009 09:32 Go to next message
vikramjogi
Messages: 71
Registered: June 2005
Location: Scranton
Member
Hello All,

I have a situation where the application user needs DROP ANY EXECUTE ANY, ALTER ANY (system privileges). I have O7_DICTIONARY_ACCESSIBILTY=FLASE, so the user can't access the SYS Schema objects (Dictionary), but granting them the system privileges would give them access to SYSTEM Schema.

So, my question is, Is there any way that we can prevent them from accessing the SYSTEM schema objects.

I would appreciate any suggestions.

Thanks,
VJ

Report message to a moderator

Re: SYSTEM schema security [message #415656 is a reply to message #415654] Tue, 28 July 2009 09:36 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member
Quote:
Is there any way that we can prevent them from accessing the SYSTEM schema objects.


What about GRANT??

Report message to a moderator

Re: SYSTEM schema security [message #415660 is a reply to message #415656] Tue, 28 July 2009 09:45 Go to previous messageGo to next message
vikramjogi
Messages: 71
Registered: June 2005
Location: Scranton
Member
Gentlebabu,

Thanks for replying. But i am not sure if i actually understand what do you mean by "What about GRANT?" Could you please help me understand.

Thanks,
VJ

Report message to a moderator

Re: SYSTEM schema security [message #415661 is a reply to message #415654] Tue, 28 July 2009 09:46 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I have a situation where the application user needs DROP ANY EXECUTE ANY, ALTER ANY (system privileges).
I disagree.
A procedure could be written that is owned by a suitably privileged user which can take the desired action only against objects owned by specific users.
I posted sample code to do so within the last 2 weeks here.

Report message to a moderator

Re: SYSTEM schema security [message #415664 is a reply to message #415654] Tue, 28 July 2009 09:51 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
You need to redesign the application so it doesn't need those priviliges.
Either that or give up worrying about security entirely.

Report message to a moderator

Re: SYSTEM schema security [message #415667 is a reply to message #415664] Tue, 28 July 2009 09:56 Go to previous messageGo to next message
vikramjogi
Messages: 71
Registered: June 2005
Location: Scranton
Member
BlackSwan, cookiemonster

Thanks for replying. Could you please let me know if we can restrict the users from accessing SYSTEM schema. I totally agreed with your suggestions and i am in the process of implementing them. But just curious to know if there is a solution to prevent access to SYSTEM schema.

Thanks,
VJ

Report message to a moderator

Re: SYSTEM schema security [message #415668 is a reply to message #415664] Tue, 28 July 2009 09:57 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

>> there any way that we can prevent them from accessing the SYSTEM schema objects.

I said; If you want access DBA objects from NON-DBA used; you need give select privileage from DBA user to NON-DBA user.

Thanks

Report message to a moderator

Re: SYSTEM schema security [message #415669 is a reply to message #415654] Tue, 28 July 2009 09:59 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> But just curious to know if there is a solution to prevent access to SYSTEM schema.
Do not issue GRANT that provide access to SYSTEM schema.
YOU have caused this issue & only you can solve it.
This is NOT an Oracle issue. It is your application issue.

Report message to a moderator

Re: SYSTEM schema security [message #419365 is a reply to message #415667] Fri, 21 August 2009 09:51 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
Could you please let me know if we can restrict the users from accessing SYSTEM schema


No. If you grant a user SELECT ANY TABLE, then they can select ANY table in the database, including SYS and SYSTEM

Report message to a moderator

Re: SYSTEM schema security [message #419368 is a reply to message #419365] Fri, 21 August 2009 10:29 Go to previous message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SELECT ANY TABLE does not give access to SYS tables:
SQL> create user test identified by test;

User created.

SQL> grant create session, select any  table to test;

Grant succeeded.

SQL> connect test/test
Connected.
TEST> desc sys.tab$
ERROR:
ORA-04043: object sys.tab$ does not exist


TEST> desc dba_tables;
ERROR:
ORA-04043: object "SYS"."DBA_TABLES" does not exist

Regards
Michel

Report message to a moderator

Previous Topic: How to authenticate a user by OS
Next Topic: Role Change
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Apr 19 14:00:00 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.