Activity-based Authorization

A very good post: Don’t Do Role-Based Authorization Checks; Do Activity-Based Checks

Tables

User:UserID | Name
1 | Jamie
2 | Tom

Role:RoleID | Name
1 | Admin
2 | General
3 | Audit
4 | Manager

Activity:ActivityID | Name
1 | Read A
2 | Create A
3 | Delete A
4 | Update A
5 | Read User
6 | Delete User

UserToRole:UserID | RoleID
1 | 3
2 | 1

RoleToActivity: RoleID | ActivityID
1 | 1
1 | 2
1 | 3
1 | 4
1 | 5
1 | 6
2 | 1
4 | 1
4 | 5

Code

Check users activity rather than role. Create similar APIs like Ruby CanCanCan.

void Initial() // load activity list into a list
bool Can(Activity name) // verify if current user can do this activity
bool Cannot(Activity name) // !can

SQL Server MDF File Fragmentation

Issue: SQL reject writing with error ‘…because the ‘PRIMARY’ filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup…’

Checks:

  1. disk space – 100GB free
  2. autogrow was set properly
  3. restart SQLSERVER / SQLAGENT
  4. contig –a xxx.mdf saying huge number of frags and biggest continuous space is 14GB
  5. run contig xxx.mdf but no enough space to defrag and it is slooooow and I/O intensive.

Solution: It is a DEVEL database and no one really cares about it, so we deleted table, rerun the schema and restored from backups

Thoughts:

  1. smaller database file
  2. reduce autogrow

http://www.mssqltips.com/sqlservertip/3008/solving-sql-server-database-physical-file-fragmentation/

 

To avoid “msg 15151 because it does not exist or you do not have permission.” call the following statement before create procedure and grant permission.

use <database name>
go

create procedure <procedure name>
as

select 
	[filed name] '1979', 
	[filedname] '1097', 
from [database name].[schema].[table name]
return 0
go

grant exec on [database name].[schema].[table name] to <login/user>
go