通用权限数据库

import sqlite3

# 创建数据库和表
conn = sqlite3.connect('permissions.db')
c = conn.cursor()
c.execute('''CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)''')
c.execute('''CREATE TABLE IF NOT EXISTS roles (id INTEGER PRIMARY KEY, name TEXT)''')
c.execute('''CREATE TABLE IF NOT EXISTS permissions (id INTEGER PRIMARY KEY, name TEXT)''')
c.execute('''CREATE TABLE IF NOT EXISTS user_roles (user_id INTEGER, role_id INTEGER, FOREIGN KEY(user_id) REFERENCES users(id), FOREIGN KEY(role_id) REFERENCES roles(id))''')
c.execute('''CREATE TABLE IF NOT EXISTS role_permissions (role_id INTEGER, permission_id INTEGER, FOREIGN KEY(role_id) REFERENCES roles(id), FOREIGN KEY(permission_id) REFERENCES permissions(id))''')

# 插入示例数据
c.execute("INSERT INTO users (name) VALUES ('Alice')")
c.execute("INSERT INTO users (name) VALUES ('Bob')")
c.execute("INSERT INTO roles (name) VALUES ('Admin')")
c.execute("INSERT INTO roles (name) VALUES ('User')")
c.execute("INSERT INTO permissions (name) VALUES ('Read')")
c.execute("INSERT INTO permissions (name) VALUES ('Write')")
c.execute("INSERT INTO user_roles (user_id, role_id) VALUES (1, 1)")
c.execute("INSERT INTO user_roles (user_id, role_id) VALUES (2, 2)")
c.execute("INSERT INTO role_permissions (role_id, permission_id) VALUES (1, 1)")
c.execute("INSERT INTO role_permissions (role_id, permission_id) VALUES (1, 2)")
c.execute("INSERT INTO role_permissions (role_id, permission_id) VALUES (2, 1)")

conn.commit()

# 检查用户权限
def check_permission(user_id, permission_name):
    c.execute('''SELECT COUNT(*) FROM users 
                 JOIN user_roles ON users.id = user_roles.user_id 
                 JOIN roles ON user_roles.role_id = roles.id 
                 JOIN role_permissions ON roles.id = role_permissions.role_id 
                 JOIN permissions ON role_permissions.permission_id = permissions.id 
                 WHERE users.id = ? AND permissions.name = ?''', (user_id, permission_name))
    return c.fetchone()[0] > 0

# 示例检查
print(check_permission(1, 'Read'))  # 输出: True
print(check_permission(2, 'Write'))  # 输出: False

conn.close()