Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1278
  • Last Modified:

Read-Only View

How do I make a view read only?  I do not want update, replace, insert, delete ext... to work on the view.  I want only select query's to run.
0
kyleroi
Asked:
kyleroi
  • 3
1 Solution
 
virmaiorCommented:
you add a user to MySQL and only give them SELECT rights on the relevant table and database.
0
 
Tomas Helgi JohannssonCommented:
    Hi!

If you want to have the view read only the only thing you have to do is
not write the create view statement with the "with [cascaded/loca] check option"

http://dev.mysql.com/doc/refman/5.0/en/create-view.html

Regards,
   Tomas Helgi
0
 
Tomas Helgi JohannssonCommented:
If a view is without the "...check option" the view
is automatically read only.

Regards,
    Tomas Helgi
0
 
kyleroiAuthor Commented:
I do not have the check option in my query although it still allows me to delete records from the view and ultimately from the source.  Below is the view creation query.
CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`rmast`@`%` SQL SECURITY DEFINER VIEW `newbus`.`client` AS select `roidata`.`client`.`client` AS `client`,`roidata`.`client`.`tob` AS `tob`,`roidata`.`client`.`afacility` AS `afacility`,`roidata`.`client`.`facility` AS `facility`,`roidata`.`client`.`plcschedul` AS `plcschedul`,`roidata`.`client`.`soa_level_` AS `soa_level_`,`roidata`.`client`.`soa_level2` AS `soa_level2`,`roidata`.`client`.`soa_cl` AS `soa_cl`,`roidata`.`client`.`rptlevel` AS `rptlevel`,`roidata`.`client`.`rate` AS `rate`,`roidata`.`client`.`insscrner` AS `insscrner`,`roidata`.`client`.`dialtype` AS `dialtype`,`roidata`.`client`.`dialtime` AS `dialtime`,`roidata`.`client`.`site` AS `site`,`roidata`.`client`.`allserve_p` AS `allserve_p`,`roidata`.`client`.`allserve_i` AS `allserve_i`,`roidata`.`client`.`frontline` AS `frontline`,`roidata`.`client`.`typeofbus` AS `typeofbus`,`roidata`.`client`.`fixedfee` AS `fixedfee`,`roidata`.`client`.`netgross` AS `netgross`,`roidata`.`client`.`courtcost` AS `courtcost`,`roidata`.`client`.`legalrate` AS `legalrate`,`roidata`.`client`.`lnetgross` AS `lnetgross`,`roidata`.`client`.`ratecode` AS `ratecode`,`roidata`.`client`.`rangefrom` AS `rangefrom`,`roidata`.`client`.`rangeto` AS `rangeto`,`roidata`.`client`.`capamt` AS `capamt`,`roidata`.`client`.`combclient` AS `combclient`,`roidata`.`client`.`combtob` AS `combtob`,`roidata`.`client`.`combfac` AS `combfac`,`roidata`.`client`.`facname` AS `facname`,`roidata`.`client`.`rptname` AS `rptname`,`roidata`.`client`.`addr` AS `addr`,`roidata`.`client`.`addr2` AS `addr2`,`roidata`.`client`.`state` AS `state`,`roidata`.`client`.`zip` AS `zip`,`roidata`.`client`.`zip2` AS `zip2`,`roidata`.`client`.`area` AS `area`,`roidata`.`client`.`phone` AS `phone`,`roidata`.`client`.`taxid` AS `taxid`,`roidata`.`client`.`taxcode` AS `taxcode`,`roidata`.`client`.`settlepct` AS `settlepct`,`roidata`.`client`.`add_date` AS `add_date`,`roidata`.`client`.`inactive` AS `inactive`,`roidata`.`client`.`close_date` AS `close_date`,`roidata`.`client`.`fastmo` AS `fastmo`,`roidata`.`client`.`fastdy` AS `fastdy`,`roidata`.`client`.`fastyr` AS `fastyr`,`roidata`.`client`.`legalfeeop` AS `legalfeeop`,`roidata`.`client`.`ar` AS `ar`,`roidata`.`client`.`legalar` AS `legalar`,`roidata`.`client`.`daytoprt` AS `daytoprt`,`roidata`.`client`.`ackrpt` AS `ackrpt`,`roidata`.`client`.`ltrgroup` AS `ltrgroup`,`roidata`.`client`.`minbal` AS `minbal`,`roidata`.`client`.`maxbal` AS `maxbal`,`roidata`.`client`.`allowsuit` AS `allowsuit`,`roidata`.`client`.`minbalsue` AS `minbalsue`,`roidata`.`client`.`allowcash` AS `allowcash`,`roidata`.`client`.`mailname` AS `mailname`,`roidata`.`client`.`mailaddr` AS `mailaddr`,`roidata`.`client`.`mailaddr2` AS `mailaddr2`,`roidata`.`client`.`mailcity` AS `mailcity`,`roidata`.`client`.`mailzip` AS `mailzip`,`roidata`.`client`.`mailzip2` AS `mailzip2`,`roidata`.`client`.`contact` AS `contact`,`roidata`.`client`.`oldroi` AS `oldroi`,`roidata`.`client`.`mejan` AS `mejan`,`roidata`.`client`.`mefeb` AS `mefeb`,`roidata`.`client`.`memar` AS `memar`,`roidata`.`client`.`meapr` AS `meapr`,`roidata`.`client`.`memay` AS `memay`,`roidata`.`client`.`mejun` AS `mejun`,`roidata`.`client`.`mejul` AS `mejul`,`roidata`.`client`.`meaug` AS `meaug`,`roidata`.`client`.`mesep` AS `mesep`,`roidata`.`client`.`meoct` AS `meoct`,`roidata`.`client`.`menov` AS `menov`,`roidata`.`client`.`medec` AS `medec`,`roidata`.`client`.`curryr` AS `curryr`,`roidata`.`client`.`prioryrend` AS `prioryrend`,`roidata`.`client`.`priorend` AS `priorend`,`roidata`.`client`.`hosptype` AS `hosptype`,`roidata`.`client`.`pyrendjan` AS `pyrendjan`,`roidata`.`client`.`pyrendfeb` AS `pyrendfeb`,`roidata`.`client`.`pyrendmar` AS `pyrendmar`,`roidata`.`client`.`pyrendapr` AS `pyrendapr`,`roidata`.`client`.`pyrendmay` AS `pyrendmay`,`roidata`.`client`.`pyrendjun` AS `pyrendjun`,`roidata`.`client`.`pyrendjul` AS `pyrendjul`,`roidata`.`client`.`pyrendaug` AS `pyrendaug`,`roidata`.`client`.`pyrendsep` AS `pyrendsep`,`roidata`.`client`.`pyrendoct` AS `pyrendoct`,`roidata`.`client`.`pyrendnov` AS `pyrendnov`,`roidata`.`client`.`pyrenddec` AS `pyrenddec`,`roidata`.`client`.`changes` AS `changes`,`roidata`.`client`.`typeofplac` AS `typeofplac`,`roidata`.`client`.`salespersn` AS `salespersn`,`roidata`.`client`.`budgetpers` AS `budgetpers`,`roidata`.`client`.`lstlddt` AS `lstlddt`,`roidata`.`client`.`lstldnoaccts` AS `lstldnoaccts`,`roidata`.`client`.`lstldamt` AS `lstldamt`,`roidata`.`client`.`ccaccept` AS `ccaccept`,`roidata`.`client`.`graceper` AS `graceper` from `roidata`.`client`

Open in new window

0
 
Tomas Helgi JohannssonCommented:
Hmmm also try to skip the ALGORITHM, DEFINER and the SQL SECURITY options from the creation of the view.
To use those options you must carefully describe who has what kind of access to the table (SELECT,INSERT,UPDATE,DELETE). As described in the manual posted earlier a user who invokes this kind of view
and has DELETE access to the table can delete from the table through the view.

Regards,
   Tomas Helgi  
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now