I’m just planning my check in check out tool, and one thing I was curious about is whats the best way of dealing with file permissions.
For example I want to have several different users, and each user only has access to files relevant to them. How do I go about storing which files are relevant to them?
I will be using a sql server to store the majority of the metadata I will be using, I can store the file permissions in there to, my only concern is that it might get a bit messy with folders access as well.
Would it be best to just store file permissions then use those to determine folder permissions?
I hope that makes sense, basically I’m looking for a way to restrict the users access to files they do not need access to, the browsing and opening of files will be done through a custom pyqt window, well thats the plan at least.
Cheers.
You’re looking for something but not providing a lot of information. Are you running the server on Linux on Windows? Are you using Active Directory for user logins? Do you want them to have remote access outsidde of the studio? Have you looked at tools like Perforce, TRAC or a self run SVN?
Sorry, this is all in the planning stages at the moment.
It will be running on windows, the user login system is going to be created from scratch, only very simply. This is a simple tool so remote access is not needed, and the idea is to create a tool without using other tools and services.
I know its not ideal but its for uni so this is the way I have to do it.
More info, basically the user will start up maya and be asked to log in, then depending on the user (member of staff) they will be presented with projects and files of which are relevant to them.
This will all be done using pyqt for the user interface.
Essentialy I am trying to find the best way to show files that are only relevant to the user, using just pyqt, python and sql.
I have yet to to my research into sql, so I might be going about this the wrong way, if thats the case I apologise.
Hope this clears things up a bit.
Cheers.
For SQL, I would recommend taking a look at the sample script I provided for my GDC talk this year.
It shows how to use SQLAlchemy ORM to easily work with a database from Python. It’s a great place to start if you’re new to SQL work.
If you’re rolling your own file permissions system, then I think the choice of whether to do it per file or per folder is up to you. Even how to store it is really personal taste.
You could have a DB table that stores user IDs and a list of files/folders that user has access to, or store the file/folder as the key, with a list of users. If one folder/file is always limited to one user, then the former method would make the most sense. An example of how that might break down into a couple DB tables:
User_Table
- id [integer]
- user_name [string]
Files_Table
- id [integer]
- filename [string] (should include full path)
- user_id [integer] (id of user that owns it, from User_Table)
If more than one user can access a given file, then the model would look a bit different.
Does this help?
Yes that helps thanks.
The way I was thinking about doing it, this was just off the top of my head without any real research into it was to have a table that stored the files and a list of users that have access to those folders, I think the other way round as you suggested would work better.
Thanks for the sample script, I know pyqt has its own methods for working with databases to.
Storing the files directly into a database is going to be bad mojo if you’re working with models and textures as they’re going to be large and putting them into a blob is going to take some time. Once worked on a system where we would FTP pdf’s for print so clients could approve them and upping 100+ mb files was horrendous.
Adam’s got the right thing going with how he lays it out, but I would actually extend that one more time and add in a permissions table and field. Then you can add files with a given permission field, without having to specify every single user you want to add to a project.
Could you clarify what you mean by permission table and field please, it sounds interesting but not quite sure I understand.
The files themselves wont be stored in the database, just references to the file locations.
My kids just woke up so I can’t post anything comprehensive from my phone, but here’s an article that should shed some light until I can post something more relevant this evening.
Ignore the fact that it’s “PHP”. Language is language, just different mechanisms for accessing those wrappers for interaction (plus the article doesn’t talk much about PHP specifically anyways).
Many thanks for the article link.
Did that help or do you still want/need an example? Don’t want to explain something you already get. 
It did help thanks, I started to get a little confused when the article was talking about the responsibility based stuff.
Been doing a little more research and it seems a many to many relationship is often used for something like this.
So I’d have two tables one with the user ids and another with the file ids, I’d then have a third table which combined them both.
My worry is if I have say 100 files that 3 users have access to, thats going to equal 300 rows, I’m worried that this might become too large and slow of a table.
A large number of rows is not inherently a problem for a database. Certainly not with sub-thousand numbers like you’re talking. What matters more is how much data your queries request, and the complexity of the query.
If you’re doing permissions by simply associating one/more users to one/more files, then the table layout you described is pretty accurate. To clarify a bit, something like this:
Users_Table
ID Name
0 Steve Smith
1 Sally Brown
2 Joe Himdickel
Files_Table
ID Filename
0 P:\stuff\file1.txt
1 Q:\whatever\file2.txt
2 P:\stuff\file3.txt
Users_Files_Table
User_ID File_ID
0 0
0 1
1 0
0 2
1 1
2 2
In that example, Steve has permission to all three files, Sally has only file2.txt and file3.txt, and Joe only file3.txt.
The fact that each single association requires a new row in the Users_Files_Table is not a concern. It might seem counter-intuitive, but this is the traditional way to build a fast many-to-many relationship. The key is that each string is only stored once, and the relationship is defined only by integer IDs. Which are presumably also the primary keys for the first two tables.
If you want to use role-based permissions, then the above would have to change somewhat.
That exactly what I was talking about, glad it made sense as you can tell databases are very new to me.
I just wanted to check that it wouldn’t be too much what with each rolw being a new association, cheers for clearing this up.
Hopefully I can get the database fully designed by mid week then I can actually start on the tool itself.
Thanks very much for your help.
If you did want to cut down on the number of rows your processing, you could always create group based permissions where you’d add users to a group and then assign that group as the permission holders for those files. Thus in your example you’d only have (rough bad math off the top of my head with a screaming kiddo) about 104 table entries total.
I’d personally say to go with group (role) based permissions for your project. It adds more flexibility down the line and doesnt add that much complexity to it. Plus it then becomes easier to manage who sees what because you can break it down by teams or projects in your shop
I was thinking about doing something like that within the tool itself, as I will have to create a tool to manage the database.
So when you are changing a users permissions you can quickly apply a filter or check box that says for example “All models” and it would grant the user access to all models. This would still give me the flexibility to say right give “Jack” access to all models and then go in and deselect the few models I don’t want him to have access to.
I do like the idea of groups but I still want to be able to give each user individual permissions. Might not be that useful in production but I want to demonstrate that it is possible, as this tool will form part of my dissertation its all about demonstrating things as well as trying to create something useful.
Then you can simply have an area where you choose either user or role, that way you then get the best of both worlds. You could either add another column to your table for user_permissions or you could simply append the permissions to the already in place permissions column. Then simply query based on user_permissions and (technically you want to use or, but putting that in the sentence made less sense) permissions (or if you choose the latter simply query both the username and the role from permissions). Not sure if I’m being entirely clear, but if not let me know.
