Mysql – Tiered permissions to generate conditional data access

database-designMySQLpermissions

I am working to improve the confidentiality and access level of the information presented within a program interface over top of a MySQL database. My goal is to create a two tier system: one based on access-level, and the other based on physical location.

For instance, an engineer will have greater complexity and more access than someone within HR. While at the same time users in China will be able to review information specific to them, and will not be able to see information for Australia. I thought about creating a permissions table, with all the possible combinations stored in it, and each unique combination would have a number associated with it, but I would like administrators to have the ability to add new permissions at will and in a manner that is fairly painless. I have also toyed with the idea of a single character for company and another for group-level, but this would give a limit of 62 companies (assuming for simplicity case-sensitive alphanumeric values) and 62 regions. Is there an obvious solution that I'm just not seeing?

Best Answer

It isn't too hard to create table-driven access rules if you can base the rules on something that is in the data already, like a country code.

You want to beware the power of multiplication. Don't try to create access rules based on the cross-product of two or three or more factors. The management of such a rule set will quickly become impractical due to the shear amount of data.

Instead, keep each access rule separate (i.e. country, department) and treat each as a gate that the user has to get through before they get access. This will be a bit of work when setting up your data access code, but it is the best way to operate the system in the long term.

Related Solutions

Sql-server – Find which permissions are being used for a given user on a object

Have a look at sys.user_token (http://technet.microsoft.com/en-us/library/ms188421.aspx) and sys.login_token (http://technet.microsoft.com/en-us/library/ms186740.aspx). They tell you which roles/logins/users SQL Server is involving when figuring out the permissions for the current user.

Then you can use sys.database_permissions (http://msdn.microsoft.com/en-us/library/ms188367.aspx) and sys.server_permissions (http://msdn.microsoft.com/en-us/library/ms186260.aspx) to get the permissions associated with each token. (They even tell you who granted the permission.)

Finally, sys.database_principals (http://msdn.microsoft.com/en-us/library/ms187328.aspx) and sys.server_principals (http://msdn.microsoft.com/en-us/library/ms188786.aspx) can be used to translate the principal_id into a name.

I took a stab at combining the system views above. It might not be perfect, but it should get you started:

SELECT  
        T.name token_name,
        T.type token_type,
        T.usage,
        T.is_login_token,
        UDP.name user_name,
        UDP.type_desc user_type_desc,
        UDP.default_schema_name,
        UDP.create_date,
        UDP.is_fixed_role,
        --UDP.authentication_type_desc,
        SP.name login_name,
        SP.type_desc login_type,
        SP.create_date,
        --SP.is_fixed_role,
        DP.class_desc,
        DP.permission_name,
        DP.state_desc,
        QUOTENAME(OBJECT_SCHEMA_NAME(O.object_id))+'.'+QUOTENAME(O.name) object_name,
        O.type_desc object_type_desc,
        C.name column_name
  FROM (SELECT 0 is_login_token,* FROM sys.user_token 
        UNION ALL
        SELECT 1 is_login_token,* FROM sys.login_token
       )AS T
  LEFT JOIN sys.database_principals AS UDP
    ON T.principal_id = UDP.principal_id
   AND T.is_login_token = 0
  LEFT JOIN sys.server_principals AS SP
    ON (UDP.sid = SP.sid AND T.is_login_token = 0)
    OR (T.principal_id = SP.principal_id AND T.is_login_token = 1)
  LEFT JOIN sys.database_permissions AS DP
    ON (DP.grantee_principal_id = UDP.principal_id
        AND T.is_login_token = 0)
    OR (DP.grantee_principal_id = SP.principal_id
        AND T.is_login_token = 1)
  LEFT JOIN sys.all_objects AS O
    ON DP.major_id = O.object_id
  LEFT JOIN sys.all_columns AS C
    ON DP.major_id = C.object_id
   AND DP.minor_id = C.column_id;

SQL Server – Managing Server-Wide Database Permissions

So basically it sounds like you want to give this group of individuals db_owner access to all databases on the server. The way to do this is:

Create a local group account on the Windows Server (e.g. \\MYSERVER\SpecialGroup)
Add the AD accounts to that group.
Create a SQL Server login for \\MYSERVER\SpecialGroup
Then create a job, that runs every night (or however frequently you want) that loops through all your user databases and issues a CREATE USER statement for \\MySERVER\SpecialGroup and assigns them to db_owner.

Best Answer

Related Solutions

Sql-server – Find which permissions are being used for a given user on a object

SQL Server – Managing Server-Wide Database Permissions

Related Question