First and foremost, you could search this forum for topic "workgroup security" which is the keyword you want. But I'll give you a thumbnail of how you should think about this if you want to do it in a way that provides the greatest flexibility yet allows for later upgrades if needed.
You have users. They have roles to play. Decide what those roles are and what each one means. I.e. data-entry clerks, data-quality clerks, query developers, code developers, etc. etc. Or, break it down by departments if that is the way you need to look at it.
What should happen is that you get a set of, for lack of a better term, job descriptions with respect to your database. (Don't care about what they do OUTSIDE the DB.) These descriptions will tell you what each person should be able to do INSIDE the DB.
OK, each description is the basis for one GROUP of users. It is OK for a user to be in more than one group sometimes, but for the most part, this should be a clear-cut separation of powers/abilities. So, using the workgroup tools, you would create GROUPS (make the names short - you'll thank me later).
The more groups you have, the worse off you are, because you have a chaotic situation. The WORST possible case is that everybody's job is unique. (If that is the way it came out, start over again and try to look at the problem a different way, if possible.) The BEST possible case is no more than two or three groups. Looking ahead, you will NEVER assign permissions to users. You will assign them to GROUPS. Then, when you get a new user, you make the user a member of the group. If a user changes jobs, you change the group they belong to.
OK, in the Tools >> Security >> Permissions dialog box, you have a drop-down list of object types - Tables, Queries, ..., Modules - and the database itself, as a whole. You can choose whether you are working on users or groups. Select Groups.
Each object in each class is enumerated in a scrolling list box on that same dialog box. You CAN multi-select objects if you want to give a group the same level of access to every object in a single operation. The "<new xxxx>" object is there, too, so you can set the default for new objects of that class. But remember, you have not less than SEVEN classes of object for which you need to decide the rights.
You have two pre-defined groups: Admins and Users, and one pre-defined user, Admin. (I'll not easily forgive Bill Gates for allowing the only difference between the Admin user and the Admins group to be the letter "s" at the end of the name.) You CANNOT do without the Admins group, and ALL users are automatically and forever members of the Users group. But you CAN do something to protect yourself anyway. By default, Admins has full rights on everything.
Build a user account for yourself and make it a member of the Admins group. Log in to that account. Now make Admin (the user) a member of the Users group but NOT a member of the Admins group. You might need to check on the rights of the Admin user and the Users group. I would curtail them heavily. IN PARTICULAR, I would make absolutely sure that the Admin (user) account does NOT have OpenExclusive on the database object. The Admins group can have that, but NOT the Admin user.
OK, build your other groups. Assign permissions to the groups. Assign users to members of the groups. If you did it right, they inherit all their permissions from group membership and have no individual rights. PERIOD. Well, possible exception... your personal account, if you are going to remain as the administrator, might be the ONLY account with individual rights. Also, the Users group and Admin user should have read-only access to the database. It is possible to deny them access to individual items if you wish. I do that because of a little 'gotcha' associated with workgroups. If a person accesses your database without joining the workgroup first, they come in as.... the Admin user! So by denying this user the right to do much, you block the mavericks who won't join the workgroup correctly.
There is far more to the topic than this, but I believe this to be enough to get you at least started in the right direction. Look up workgroup security, both on this forum and in the help files. Do some reading first, then design your groups. Then and ONLY then, start implementing from there.
