通用权限管理数据库

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)''')
c.execute('''CREATE TABLE IF NOT EXISTS role_permissions (role_id INTEGER, permission_id INTEGER)''')

# 插入示例数据
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 role_permissions ON user_roles.role_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

# 示例检查Alice是否有写权限
print(check_permission(1, 'write'))  # 输出: True
print(check_permission(2, 'write'))  # 输出: False

conn.close()