Adding records using SQL to create new user/password
5 posts by 2 authors in: Forums > CMS Builder
Last Post: May 10, 2010 (RSS)
By rcrofoot - May 2, 2010
Hello-
Using CMS Builder v2.01:
I have a section editor named "Bulletin Board" and have created 2 users, each with their own usernames and passwords...Each has been give the "Author" access level...
In MyPHPAdmin I can see the new table "bulletin_board" , and in the cms_accounts table I can see the 2 new users and their related data...There is also a table called cms_accesslist, which I suspect holds metadata for the 2 users mentioned above, e.g. access level, etc....
So my question is: From a text file of existing username/password pairs, and using PHP, do you have a recommended procedure to create new records for each user in the cms_accounts tables, and also update the cms_accesslist table...Updating the first table (cms_accounts) is relatively straight forward...It's cms_accesslist that seems a little trickier, and that I'm concerned about...
Thanks...Your suggestions are greatly appreciated...Rick
Using CMS Builder v2.01:
I have a section editor named "Bulletin Board" and have created 2 users, each with their own usernames and passwords...Each has been give the "Author" access level...
In MyPHPAdmin I can see the new table "bulletin_board" , and in the cms_accounts table I can see the 2 new users and their related data...There is also a table called cms_accesslist, which I suspect holds metadata for the 2 users mentioned above, e.g. access level, etc....
So my question is: From a text file of existing username/password pairs, and using PHP, do you have a recommended procedure to create new records for each user in the cms_accounts tables, and also update the cms_accesslist table...Updating the first table (cms_accounts) is relatively straight forward...It's cms_accesslist that seems a little trickier, and that I'm concerned about...
Thanks...Your suggestions are greatly appreciated...Rick
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.
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/
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
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.
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/
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
That's exactly what I needed info about...
Rick