From: Walter T Rejuney Newsgroups: comp.databases.oracle.misc Subject: Re: Thanks for nothing!!! Date: Tue, 10 Oct 2000 10:04:54 -0400 Organization: xyzzy Lines: 79 Message-ID: <39E32206.A13366E6@Unforgetable.com> References: <39DDEBCC.A09A6F55@hsv.boeing.com> <8rliiv$vib$1@nnrp1.deja.com> <39E21276.DF1C6424@hsv.boeing.com> Reply-To: BlueSax@Unforgetable.com NNTP-Posting-Host: ga0235kstahl.mmr.avaya.com Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Mailer: Mozilla 4.73 [en] (Win95; U) X-Accept-Language: en Richard Hollingsworth wrote: > Dosen't matter! Not the point! > > I simply need to know that the min privs/grants are for each group, please. > > I CANNOT find these liseted, and simply want to know what they are. > > Thanks again, > Richard H > > STiger2000 wrote: > > > We're a little light on information here. > > Two rules of thumb: (ascending soap box) > > 1. Developers belong on a development system. (Which also may be read > > as: Developers DO NOT belong on a production system.) > > 2. Users (application users, developers, testers, trainers, etc) should > > only be given the MINIMUM roles and privileges needed to do there job. > > Convenience DOES NOT equate to necessity. > > (descending soap box) > > jc > > http://www.networkessentials.com/certified/ocp > > In article <39DDEBCC.A09A6F55@hsv.boeing.com>, > > Richard Hollingsworth wrote: > > > Hi. > > > > > > I've build the tables for my system and now the developers need access > > > to those tables. I've tried several things but they still can't get > > > access to some of the tables. The only unusual thing about the tables > > > is that some of them have sequences. > > > > > > What is the min. set of grants/roles/privs that I need to give to the > > > developers? What about the users? You are asking an unanswerable question. There is no concept in Oracle of "mininum set of grants/roles/privs". All of those things are determined by the environment that you are working in. However, since you've provided virtually no information about your environment I'll just have to assume that you have basically no experience with database security and that you need extremely basic information about why a particular login account cannot access the tables you've built. Let's say you have a Schema named XYZ. In this case the userid XYZ is also synonymous with the schema (in reality, the concept of schema and userid are logically separate, but in Oracle they essentially end up being the same thing in most cases). For the sake of security you have decided that you don't want the users to log in as XYZ since that would give them full access to the schema and you don't want them to be able to create or drop tables, etc in that schema, but you do want them to be able to SELECT,INSERT,UPDATE,DELETE the tables. Let's further suppose that your application is called MyApp and that there are some users that you only want to allow reporting capabilities and others that can do any DML that is associated with the appliation. In this case, create a role named MyAppRead and give it SELECT privileges on all of the application tables. Then create a role of MyAppModify and give it INSERT,UPDATE,DELETE privileges on the table and also grant it MyAppRead. Then go down your list of users and give them either MyAppRead or MyAppModify according to the level of access that you want them to have. Finally, go back and create a PUBLIC synonym for each of the application tables. Because the actual access to the tables will be determined by the roles, the presence of the public synonym won't have any effect other than to eliminate the need for a fully-specified table reference like xyz.mytable. Instead, if you create a public synonym named MYTABLE for the table MYTABLE, then any of the users would be able to access the table as MYTABLE, but would only be able to perform those operations allowed by the roles that you created. The users themselves only need to be grated the CONNECT role and one of the roles that you created. That is an extremely simplified security plan but one that should get you started.