?
Solved

Set Permissions in Access

Posted on 2004-04-18
5
Medium Priority
?
156 Views
Last Modified: 2013-12-24
I want to set permissions inside of folders in a database (Access) so that only certain people can access them.  I have a Users Table and a Folder Table and then a concatenated table of Users/Folders that only has a User ID and Folder ID in it.   If a user indicates a folder name and a user's email address I want to be able to populate the Users/Folders table with the association.

The code I wrote for this was:

<cfquery datasource="datasource">
INSERT INTO Folder_Assignment(user_id, menu_item_id)
VALUES ('user_id', 'menu_item_id')
WHERE email_address = '#email_address#"
  AND folder_name = '#folder_name#'
</cfquery>

But it doesn't seem to work.  Am I totally off base with the way I'm doing this or am I close at all?  
Anyone have any thoughts?

Thanks,
Nancy
0
Comment
Question by:narv813
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 18

Expert Comment

by:Plucka
ID: 10856528
Hi narv813,

What's the problem your having, I think I understand what your trying to do, not sure how menu_item_id relates to the folders though?
0
 
LVL 17

Expert Comment

by:anandkp
ID: 10857441
Hi

With Insert Into - u wont be having where clause ...
its gotta be normal insert strait up ...
Something like this :

INSERT INTO Folder_Assignment(user_id, menu_item_id)
VALUES (<CFQueryParam CFSQLTYPE="CF_SQL_NUMERIC" VALUE="#User_ID#">,
<CFQueryParam CFSQLTYPE="CF_SQL_VARCHAR" VALUE="#Menu_Item_ID#">)

hth

K'Rgds
Anand
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 10861284
Anandkp is correct in that you don't use a WHERE clause in an insert statement.  If you can explain why you thought you needed it we can probably help you write an if statement around the INSERT that will account for the conditions you were trying to test for or help you insert the correct information into your tables.
0
 

Author Comment

by:narv813
ID: 10872507
Maybe I'm just confused on how the database knows which user is being accessed.  I was using the WHERE clause to show which user I'm defining access for.  I want the command to go to the users table, get the user ID for the user that has the email address ____________ put that user ID in the concatenated table along with the folder id that they can access.  Does that explain it?  Or am I just confusing the issue more?
0
 
LVL 35

Accepted Solution

by:
mrichmon earned 300 total points
ID: 10872657
Okay then you actually need 2 or even 3 queries.

It should be in a transaction so that it all happens together.

<cftransaction action="begin">
    <!--- First run a query to find the person's userid --->
    <cfquery datasource="yourdsn" name="GetUser">
          SELECT UserID FROM usertable WHERE EmailAddress = #Form.Email#
    </cfquery>
    <!--- Second run a query to find the folderid--->
    <cfquery datasource="yourdsn" name="GetFolder">
          SELECT FolderID FROM foldertable WHERE foldername = #Form.foldername#
    </cfquery>
     <!--- Now do the insert--->
    <cfquery datasource="yourdsn">
          INSERT INTO Folder_Assignment (user_id, menu_item_id)
          VALUES (#GetUser.UserID#, #GetFolder.FolderID#)
    </cfquery>
   <cftransaction action="commit">
</cftransaction>
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses
Course of the Month13 days, 15 hours left to enroll

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question