0

It is a common function in a website for users to customize their reports(they can select which column names in database tables they want to show in their reports like https://i.stack.imgur.com/GxOGz.jpg). I know how to use Select statement in SQL server to find system attributes for a column(like column_name, data_type, length, and etc) However, how can I add extra customized attributes for database columns(like is_visible, owner, display_name, or something else)? Do I have to create my own tables to record these extra attributes or there is a better way to do it if it is not hard coding? For an example, if I am an administrator user, I can list all columns selectable on the website but for some users or groups with a lower permission, some columns will not be shown and selectable. What is the common way to achieve this?

YHung
  • 1

1 Answers1

0

if you are using mySql server and you have multiple users one way to create column level permission is to user GRANT. for example

GRANT SELECT ON Students (StudentID, FirstName, MiddleName, SurName) TO HR_office;

and to execute the queries using these permissions you can use the following snippet:

EXECUTE AS USER = 'HR_office';
GO
SELECT * FROM Students;
GO 
REVERT;
GO
Salman Hanif
  • 42
  • 6
  • Thanks for your reply. But what about if the users' information is stored in a table, not in MySQL? – YHung Jan 30 '18 at 01:45
  • What kind of platform are you using ? – Salman Hanif Jan 30 '18 at 01:46
  • The database might be SQL Server or MySQL. Development language might be Java or PHP. Just want to know the concept how people handle this kind of problems if I can add and select more customized attributes from a database column. Not only handle permission issue but also do something like the alias for more flexibility. Thank you. – YHung Jan 30 '18 at 01:58
  • you can use INDEXPROPERTY, DATABASEPROPERTYEX and COLUMNPROPERTY to retrieve properties from database and table. https://www.mssqltips.com/sqlservertip/1298/retrieving-sql-server-column-properties-with-columnproperty/ this link provides the list of attributes that you can retrieve. for retrieving the permission information check https://stackoverflow.com/questions/7048839/sql-server-query-to-find-all-permissions-access-for-all-users-in-a-database – Salman Hanif Jan 30 '18 at 02:23
  • However there is no standard way doing things, you can call any queries that you deem fit for your program, if you think your program needs aliases then you should. As far as customized attributes are concerned the above link shows all the attributes that you can access through queries. – Salman Hanif Jan 30 '18 at 02:30
  • Dear Salman, Thanks for your reply again. I just created a simple sample picture using your example(https://i.stack.imgur.com/2CQZI.jpg). If I hope most of the functions are controlled from the database side, not hard coding. Like globalization, localization, column display or edit permission, column alias and etc. Is this the only way to create your own tables to record the information(additional properties for table columns) or there are some better ways to do it? Also, just curious is that possible to modify system table to add additional columns? Thanks. – YHung Jan 30 '18 at 19:39
  • when dealing with JDBC, you have to understand most of the functions are controlled through SQL queries but these queries needs to be hard coded. about adding an extra column using query, it is definitely possible using `ALTER TABLE ADD coloumnName VARCHAR(20) NULL` – Salman Hanif Feb 01 '18 at 08:49