1

I'm running this SQL:

CREATE VIEW
showMembersInfo(MemberID,Fname,Lname,Address,DOB,Telephone,NIC,Email,WorkplaceID,WorkName,WorkAddress,WorkTelephone,StartingDate,ExpiryDate,Amount,WitnessID,WitName,WitAddress,WitNIC,WitEmail,WitTelephone)
AS SELECT 
mem.MemberID,mem.FirstName,mem.LastName,mem.Address,mem.DOB,mem.Telephone,mem.NIC,mem.Email,
wrk.WorkPlaceID,wrk.Name,wrk.Address,wrk.Telephone,
anl.StartingDate,anl.ExpiryDate,anl.Amount,
wit.WitnessID,wit.Name,wit.Address,wit.NIC,wit.Email,wit.Telephone 
FROM Member mem, WorkPlace wrk, AnnualFees anl, Witness wit 
WHERE mem.MemberID = anl.MemberID AND mem.WorkPlaceID = work.WorkPlaceID AND mem.WitnessID = wit.WitnessID

When I try to create the view I get this error:

ERROR at line 1:
ORA-01031: insufficient privileges

Why is that? I'm logged in to sqlplus using sysman

Michael Mrozek
  • 169,610
  • 28
  • 168
  • 175
Nubkadiya
  • 3,285
  • 13
  • 40
  • 45
  • 1
    Why are you creating views in `SYSMAN`'s schema in the first place? You should create your application objects in a separate schema. – Jeffrey Kemp Jun 17 '10 at 01:37

2 Answers2

1

Make sure that SYSMAN is granted SELECT privileges on each of the tables in the FROM clause directly, not through a role. See CREATE VIEW prerequisites.

DCookie
  • 42,630
  • 11
  • 83
  • 92
0

Try logging as sys or sysadm

See on oracle users here

Community
  • 1
  • 1
Tom
  • 43,810
  • 29
  • 138
  • 169
  • i havnt put any passwords for sysdba but can someone suggest me to find the password of sysdba – Nubkadiya Jun 16 '10 at 14:53
  • @Nubkadiya: You shouldn't need sysdba to do this. Get the right privileges granted to the sysman user (if that is the appropriate user). Your statement as written will create the view in the SYSMAN schema! – DCookie Jun 16 '10 at 15:00
  • how to check that user's privileges and how to change. can someone guide me. – Nubkadiya Jun 16 '10 at 15:10