Nice Solution David. That is why I don't do DBA work anymore and just wave my arms and say "please make it do this" to my DBAs
Rob On Nov 17, 2:24 pm, ddf <orat...@msn.com> wrote: > On Nov 17, 8:31 am, Rob Wolfe <wolfe....@gmail.com> wrote: > > > > > > > That would be how i would do it, yup! > > > "select" means "read only" in oracle-speak > > > Rob > > > On Nov 17, 10:56 am, DanRegalia <drega...@gmail.com> wrote: > > > > Rob, > > > > Thats correct. Select Any Table would do it.. I just want to make > > > sure that it allows for read only access to these tables. > > > > So, the script I would use would look like: > > > > CREATE USER readonlyid IDENTIFIED BY readonlypw; > > > Grant User readonlyid connect; > > > Grant Select any Table to readonlyid; > > > > This should grant me the user privs that i need, right? > > > > On Nov 17, 9:31 am, Rob Wolfe <wolfe....@gmail.com> wrote: > > > > > I am a little rusty but wouldn't granting "select any table" to the > > > > user work if what you REALLY want is to see all data irrespective of > > > > schema? > > > > > Rob > > > > > On Nov 16, 5:00 pm, DanRegalia <drega...@gmail.com> wrote: > > > > > > Hey All, > > > > > I've gone thru some of the documentation over at Oracle about creating > > > > > a > > > > > user:http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/users.htm > > > > > > CREATE USER jward > > > > > IDENTIFIED BY aZ7bC2 > > > > > DEFAULT TABLESPACE data_ts > > > > > QUOTA 100M ON test_ts > > > > > QUOTA 500K ON data_ts > > > > > TEMPORARY TABLESPACE temp_ts > > > > > PROFILE clerk; > > > > > GRANT connect TO jward; > > > > > > And this all looks like it makes sense, however, I don't see where I > > > > > would specify read only access.. > > > > > > Say I wanted my user to have total access across all schemas to be > > > > > able to pull any data down, but it can only have read access across > > > > > them all, what would the SQL Look like? > > > > > > Thanks, > > > > > > ~Dan- Hide quoted text - > > > > > - Show quoted text -- Hide quoted text - > > > > - Show quoted text - > > I think that's overkill as it provides select privileges to data > dictionary tables/views most users should not have access to. My > course of action would be to create a role strictly for read-only > access: > > create role read_only_role; > > Then grant select access to all non-system tables/views you want a > user to be able to select from: > > grant select on mine.mytable to read_only_role; > grant select on mine.myothertable to read_only_role; > ... > > Possibly create public synonyms for these tables: > > create public synonym mytable for mine.mytable; > create public synonym myothertable for mine.myothertable; > ... > > Grant the role to the specific user and make it the default: > > grant read_only_role to someuser; > alter user someuser default role read_only_role; > > Now that user can select from non-system tables and have no other > privileges against them. > > Granting select any table privilege to non-DBA users is a violation of > SOX requirements which most companies now enforce. > > David Fitzjarrell- Hide quoted text - > > - Show quoted text - -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
