Goodmorning to you all,
I’m new to the forum and I’m new to Yii.
I’m starting to develop an application on a Windows system with IIS7 and Sql Server 2008 Express.
Connecting to db is fine and I can do almost everything.
I’m having trouble with rbac based on Db and I can’t figure out if I’m missing something or this is not supported for my enviroment.
First I tried to use extensions like srbac and similar but they all failed while working on the database. At first I thought that they all were not written with SQL server in mind and I looked at the schema for mssql in
framework/web/auth/schema-mssql.sql
/**
* Database schema required by CDbAuthManager.
*
* @author Qiang Xue <qiang.xue@gmail.com>
* @link http://www.yiiframework.com/
* @copyright Copyright © 2008 Yii Software LLC
* @license http://www.yiiframework.com/license/
* @since 1.0
*/
drop table if exists [AuthAssignment];
drop table if exists [AuthItemChild];
drop table if exists [AuthItem];
create table [AuthItem]
(
[name] varchar(64) not null,
[type] integer not null,
[description] text,
[bizrule] text,
[data] text,
primary key ([name])
);
create table [AuthItemChild]
(
[parent] varchar(64) not null,
[child] varchar(64) not null,
primary key ([parent],[child]),
foreign key ([parent]) references [AuthItem] ([name]) on delete cascade on update cascade,
foreign key ([child]) references [AuthItem] ([name]) on delete cascade on update cascade
);
create table [AuthAssignment]
(
[itemname] varchar(64) not null,
[userid] varchar(64) not null,
[bizrule] text,
[data] text,
primary key ([itemname],[userid]),
foreign key ([itemname]) references [AuthItem] ([name]) on delete cascade on update cascade
);
But if I try to run this sql on my sqlserver 2008 I get two different errors:
-
the drop table statement has syntax error
-
the create authitemchild throws an error with the foreign key
Basically it tells me that sql server cannot have a double reference (parent and child) on same field (AuthItem.name).
So I’m wondering if I’m missing something or if it is not possible to have a db based rbac on sql server 2008.
Any help would be appreciated!