RE: Difference in SYS user and A user with dba previleges.

From: Siva Valiveru <SValiveru_at_looksmart.net>
Date: Mon, 9 Oct 2006 13:09:57 -0700
Message-ID: <8F8AA525BF5CCB40BF4FA4A6156D6F270B9E3277@sfex3k2.looksmart_sf.ad.looksmart.com>

I think most of the cases this works I have seen couple of cases where either you have to connect directly to schema owner or connect as sysdba.

Version: 10.2.0.2

Note:309809.1, ORA-1031 : CREATE DATABASE LINK Fails
My case( Not sure why it happens, may be someone has answer), create MATERIALIZED VIEW fails with ORA-01031

SQL> conn dbasuper/dbasuper
Connected.
SQL> alter session set current_schema=APP;

Session altered.

SQL> CREATE MATERIALIZED VIEW APP.mymview REFRESH COMPLETE
START WITH SYSDATE NEXT TRUNC(SYSDATE+1)+.99 WITH PRIMARY KEY
AS
select a.*
from app.mytab a
where owner not in ('SYS','SYSTEM')
/

2 3 4 5 6 7 8 9 from app.mytab a

*
ERROR at line 7:
ORA-01031: insufficient privileges

******* Complete test case below *******************

drop user app cascade;
create user app identified by app;
grant create session, connect to app;

drop user dbasuper cascade;
create user dbasuper identified by dbasuper; grant dba to dbasuper;

grant CREATE MATERIALIZED VIEW to app,dbasuper; grant CREATE ANY MATERIALIZED VIEW to app,dbasuper; grant CREATE SNAPSHOT to app,dbasuper;
grant CREATE ANY SNAPSHOT to app,dbasuper; grant QUERY REWRITE to app,dbasuper;
grant GLOBAL QUERY REWRITE to app,dbasuper; grant UNLIMITED TABLESPACE to app,dbasuper;

conn dbasuper/dbasuper

alter session set current_schema=APP;

drop table APP.MYTAB;
create table APP.mytab as select rownum rid,a.* from all_objects a where rownum < 11;
alter table app.mytab add constraint mytab$pk primary key(rid);

CREATE MATERIALIZED VIEW APP.mymview
REFRESH COMPLETE
START WITH SYSDATE NEXT TRUNC(SYSDATE+1)+.99 WITH PRIMARY KEY
AS
select a.*
from app.mytab a
where owner not in ('SYS','SYSTEM')
/

ERROR at line 7:
ORA-01031: insufficient privileges

Where as when I connect to sysdba account or as directly as app user it MVIEW gets created with no issues.

conn app/app
drop MATERIALIZED VIEW APP.mymview;
CREATE MATERIALIZED VIEW APP.mymview
REFRESH COMPLETE
START WITH SYSDATE NEXT TRUNC(SYSDATE+1)+.99 WITH PRIMARY KEY
AS
select a.*
from app.mytab a
where owner not in ('SYS','SYSTEM')
/

conn /as sysdba

drop MATERIALIZED VIEW APP.mymview;

CREATE MATERIALIZED VIEW APP.mymview
REFRESH COMPLETE
START WITH SYSDATE NEXT TRUNC(SYSDATE+1)+.99 WITH PRIMARY KEY
AS
select a.*
from app.mytab a
where owner not in ('SYS','SYSTEM')
/

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mandal, Ashoke Sent: Monday, October 09, 2006 10:14 AM
To: niall.litchfield_at_gmail.com; sjaffarhussain_at_gmail.com Cc: Oracle-L_at_Freelists. Org (E-mail)
Subject: RE: Difference in SYS user and A user with dba previleges.

We were also asked by security audit team to use individual userid with DBA privilege for database administration so that you know who has done the changes in the database. But my feeling is that you can't track any changes without any auditing on.

Thanks,
Ashoke

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Niall Litchfield Sent: Monday, October 09, 2006 9:21 AM
To: sjaffarhussain_at_gmail.com
Cc: Oracle-L_at_Freelists. Org (E-mail)
Subject: Re: Difference in SYS user and A user with dba previleges.

I'd agree with your security people that you

change default passwords and
don't use sys for admin tasks.

I'm not sure that I'd be convinced by the argument that system shouldn't be used, but that an identically privileged account should be setup instead.

On 10/9/06, Syed Jaffar Hussain <sjaffarhussain_at_gmail.com> wrote:

Hi List,

I am working in an organization where security people asked us not

	keep the sys and system user passwords, rather create an dba 
	authenticated user with dba prvileges.
	I know that the user with just dba privileges, can't shutdown
and
	startup the database.
	However, I believe, on server, we can use 'connect / as sysdba'
and do
	startup and shutdown. 
	Is there anything significant difference between the sys user
and a
	user with dba premission? I mean, dba administration point view.
	
	Thanks for your time.
	
	--
	Best Regards,
	Syed Jaffar Hussain
	8i,9i & 10g OCP DBA
	
	I blog at :http://jaffardba.blogspot.com/

http://www.oracle.com/technology/community/oracle_ace/ace1.html#hussain

	"Winners don't do different things. They do things differently."
	--
	http://www.freelists.org/webpage/oracle-l

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info 

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Oct 09 2006 - 15:09:57 CDT