Friday, December 19, 2008

Converting from Free Tables to a Data Dictionary

Advantage Data Dictionaries (databases) have many benefits over using free tables. These include many relational features such as; Constraints, Referential Integrity, Triggers, Views, Stored Procedures and SQL user defined functions. Data dictionaries are also required for replication between two or more servers.

DD_AddTables The best part of data dictionaries is how little effort is required to get started using them. It is extremely easy to add existing tables to a data dictionary using ARC. You simply right-click on the tables icon in the tree view and choose "Add Existing Table(s)". A file open dialog will be displayed allowing you to select all of the tables you wish to add to your data dictionary. This will also include any structural index files associated with the table.

Adding users is also a very simple process, the ADSSYS (administrator) user is added when the dictionary is created. I would recommend creating at least one additional account for normal data access functions. Once users are created assigning rights has been made easier with version 9.x because of dictionary roles. Simply assign the appropriate rights to the DB:Public group and all new users will inherit these rights since every user is automatically a member of this group. You don’t have to enforce dictionary logins or rights checking if you don’t wish to.

DD_Properties_Security

After the tables have been added and you need to make a slight modification to your connection string. The data path you specify must now point to the dictionary file (.add). Depending on the user access you have chosen you may need to specify a username and password in your connection. You can also prompt the user for this information and then add it to the connection string.

Once the connection to the dictionary has been established you access the data in the same manner as you do with free tables. Therefore existing code should not have to change, aside from the connection strings, to implement a data dictionary. Once you have added all your tables to the dictionary you can move on to using other features like: views, triggers, stored procedures and SQL UDFs.

1 comment:

Reinaldo Crespo-Bazán said...

Chris;

Hi. I thought I'd add something about the use of DD that most everyone seems to ignore. I'm referring to the ability to hide the actual tables.

If you store the tables on a server that does NOT share that particular folder and you make the owner of the folder the "advantage" user (advantage.advantage in linux with mod 760 or "system" in windows server with full control only for user "system"), then the tables can NOT be seen unless you are connected to the DD. Tables can't be copied, delted, typed, opened, they are simply invisible.

Therefore, a user can't make copies of tables or access them in anyway, or even delete the table either by accident or with bad intent.

When tables can't be accessed unless the client connects to the DD, then why would you even need to encrypt them?

Best regards,


Ray.