Database Permissions

Incubusaurus · April 3, 2010, 2:45pm

Hi,

I have recently been looking into using Yii, and have been very impressed with it. I have looked through the documentation, and am working my way through the blog tutorial. A question that struck me early on was how do I use the Yii database application component to access my database with the minimum permissions necessary for a particular operation.

Previously, I have always created at least two users in a database. One for read/write operations, and one for read only operations. In the context of the blog application, the read user would be used to display the posts, while the read/write user would be used to create/update/delete posts.

I realise that by using CDbConnection I could create two connections:

&#036;conn_read=new CDbConnection(&#036;dsn,&#036;username_r,&#036;password_r);


&#036;conn_write=new CDbConnection(&#036;dsn,&#036;username_w,&#036;password_w);

However, I was wondering if there is already a built-in option to do this using the application component? Something along the lines of:

'dbRead'=&gt;array(


    'connectionString' =&gt; 'mysql:host=localhost;dbname=testdb',


    'emulatePrepare' =&gt; true,


    'username' =&gt; 'db_read',


    'password' =&gt; 'password',


    'charset' =&gt; 'utf8',


    'tablePrefix' =&gt; 'tbl_',


),





'dbWrite'=&gt;array(


    'connectionString' =&gt; 'mysql:host=localhost;dbname=testdb',


    'emulatePrepare' =&gt; true,


    'username' =&gt; 'db_write',


    'password' =&gt; 'password',


    'charset' =&gt; 'utf8',


    'tablePrefix' =&gt; 'tbl_',


)

I am new to Yii, so may be missing something obvious, but it appears as though the default is to have just one all-powerful user for database connections.

lgoss007 · April 3, 2010, 10:25pm

Yii is flexible enough to be done either way, but the default is to set up a user with create, read, update, and delete, and you’ll be doing some extra work to work around the default way. One all powerful user is made in the backend while the front end handles permissions (check out RBAC).

This is basically database vs application level control, and who should be doing what. The way Yii works is quite consistent with most open source web apps and frameworks.

Incubusaurus · April 4, 2010, 7:26am

Hi,

Thanks for the reply. When I’ve spent a bit more time with Yii, I may revisit the issue. My previous home-brewed framework provided both RBAC and multiple database users. For each database action, I would specify in the code which database user should be used to perform the action. Even if logged in as a user with write access, read operations would always be performed by by a database user with read-only permissions. It’s a belt and braces approach.

Alas, the time has finally come where I have had to admit to myself that the features and time savings offered by frameworks such as Yii are a serious advantage over keeping my own framework up-to-date.