13

I want to use Apache Shiro and MySQL for my Realm. What tables and fields does Shiro need to work?

Pops
  • 30,199
  • 37
  • 136
  • 151
mehdi shahdoost
  • 1,469
  • 5
  • 17
  • 27

3 Answers3

17

Shiro doesn't require database tables to function. Shiro users can use whatever datastore they want to model or manage users, groups, permissions etc.

It is a Shiro Realm's responsibility to act as the bridge to your data (however you want to represent it) and return it in a format that Shiro understands.

Because Shiro does not impose a data model upon you, this is what allows it to work with so many backends (LDAP, Active Directory, RDBMS, File System, etc).

If you want to use an RDBMS as your backing data store, check out Shiro's JdbcRealm source code to give you an idea of what your tables might look like. This is just an example though. You could have any table structure you wish.

Marek Grzenkowicz
  • 17,024
  • 9
  • 81
  • 111
Les Hazlewood
  • 18,480
  • 13
  • 68
  • 76
7

as i understand, it only requires 3 i have 3 tables and 2 views that I use as source of data for the framework. i have this on a Postgresql, but you can adapt it to use in other dbs.

try this

CREATE SCHEMA app;

CREATE TABLE app.sec_permissions ( 
    permission_id int4 NOT NULL,
    permission_name varchar( 64 ) NOT NULL,
    role_id int4 NOT NULL,
CONSTRAINT pk_sec_permissions PRIMARY KEY ( permission_id ),
CONSTRAINT idx_sec_permissions_unq_name UNIQUE ( permission_name )
 );

CREATE INDEX idx_sec_permissions ON app.sec_permissions ( role_id );

CREATE TABLE app.sec_roles ( 
    role_id int4 NOT NULL,
    role_name varchar( 32 ) NOT NULL,
    user_id int4 NOT NULL,
CONSTRAINT pk_sec_roles_0 PRIMARY KEY ( role_id ),
CONSTRAINT idx_sec_roles_unq_name UNIQUE ( role_name )
 );

CREATE INDEX idx_sec_roles ON app.sec_roles ( user_id );

CREATE TABLE app.sec_users ( 
    user_id int4 NOT NULL,
    user_loginname varchar( 32 ) NOT NULL,
    user_password varchar( 254 ) NOT NULL,
    user_passsalt varchar( 254 ) NOT NULL,
CONSTRAINT pk_sec_users PRIMARY KEY ( user_id ),
CONSTRAINT idx_sec_users_unq_loginname UNIQUE ( user_loginname )
 )
;

ALTER TABLE app.sec_permissions ADD CONSTRAINT fk_sec_permissions FOREIGN KEY ( role_id ) REFERENCES app.sec_roles( role_id ) ON DELETE CASCADE ON UPDATE CASCADE
;

ALTER TABLE app.sec_roles ADD CONSTRAINT fk_sec_roles FOREIGN KEY ( user_id ) REFERENCES app.sec_users( user_id ) ON DELETE CASCADE ON UPDATE CASCADE
;

CREATE VIEW app.sec_loginname_roles AS SELECT 
  su.user_loginname
, su.user_password
, su.user_passsalt
, sr.role_name

FROM 
app.sec_users su 
    INNER JOIN app.sec_roles sr 
    ON 
           ( su.user_id = sr.user_id  );

CREATE VIEW app.sec_role_permissions AS SELECT sr.role_name, sp.permission_name
FROM app.sec_roles sr 
    INNER JOIN app.sec_permissions sp ON ( sr.role_id = sp.role_id  );

if you find something wrong, please post back

Marek Grzenkowicz
  • 17,024
  • 9
  • 81
  • 111
Nicolas
  • 71
  • 1
  • 1
  • This sql has been executed with no error in my MySQL 5.5 database. Looks like this is syntactically O.K. for MySQL, not if losing any specific meaning or functionality.. – saygley Nov 29 '19 at 14:05
1

A skim of Shiro's quick startup page and Realm documentation page doesn't bring up anything about MySQL or databases at all. Based on that, it doesn't seem like any particular tables are required.

Pops
  • 30,199
  • 37
  • 136
  • 151