Adding records using SQL to create new user/password

5 posts by 2 authors in: Forums > CMS Builder
Last Post: May 10, 2010   (RSS)

Re: [rcrofoot] Adding records using SQL to create new user/password

By Jason - May 3, 2010

Hi,

How many users are in your text file? CMS Builder automatically creates entries in cms__accesslist. It assigns 1 record for each user, for each table they are given access to (unless they are given access to all).

If there are not too many records, it may be easier to manually assign access after the accounts records have been created.

Hope this helps.
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

Re: [Jason] Adding records using SQL to create new user/password

By rcrofoot - May 3, 2010

Hi Jason-
My client has a text file of user names & passwords that are updated on a daily basis from an external database...It consists of approx. 800 names/passwords...Each of these users needs to be registered in cms builder so they can post their own material when necessary...So, I thought that re-writing the main table (section editor), and then updating the cms_accesslist using sql would be the way to go...I'm confident that would work, but wondered if you have any recommendations or snags you can alert me to...Also, not sure where the value for randomSaveId field in cms_access table is coming from...Is it getting automatically generated as a unique value...And what is it used for...
Thanks, Rick

Re: [rcrofoot] Adding records using SQL to create new user/password

By Jason - May 4, 2010

Hi,

Yes, it is possible to create users from a text file.

The table cms__accesslist is used to set permissions for each user. You need to create 1 record in the accesslist for each permission you want to set. For each one, you can just set the randomSaveId to "1234567890".

So, for each user, you will first have to create a record in the accounts table. Here are the minimum fields you'll have to set:

-createdDate (use NOW() )
-updatedDate (use NOW() )
-createdByUserNum (you can set this to 0)
-updatedByUserNum (you can set this to 0)
-username
-password
-expiresDate (user NOW() or the date you want their account to expire)
-neverExpires (set to 1 if you don't want their account to expire, otherwise use 0)
-isAdmin (1 for admin access, 0 for non-admin)
-disabled (1 for disabled, 0 for not disabled).

After you create the user in the accounts table, you'll need to get their user number (num in the accounts table). You'll need to create at least 1 record for them in the access list. You'll need to set these values:

-userNum (their user number)
-tableName (set to all for the first record)
-accessLevel (for the first record this can be 0 (none), 6 (author), or 9 (editor), 1(by section - this allows you to set permissions for individual tables) )
-maxRecords (you can set a number for the maximum number of records they can create, or NULL for unlimited)
-randomSaveId (1234567890)

This record sets their permissions for all tables. If you set accesslevel to 1, you can then create more records to set permissions for other values. All you have to do is change the table name.

NOTE: you can only use the accesslevel 1 for the "all" table. After that, you would use either 6, or 9. Also, if you set the accesslevel to 1 for the "all" table, any table that you don't create a record for is defaulted to "none" for access.

I hope this helps. Let me know if you run into any trouble.
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

Re: [Jason] Adding records using SQL to create new user/password

By rcrofoot - May 10, 2010

Thank you, Jason...

That's exactly what I needed info about...

Rick