用户权限数据库下载

import sqlite3

# 创建数据库和表
conn = sqlite3.connect('user_permissions.db')
c = conn.cursor()
c.execute('''CREATE TABLE IF NOT EXISTS users (
                    id INTEGER PRIMARY KEY,
                    username TEXT NOT NULL,
                    password TEXT NOT NULL
                )''')
c.execute('''CREATE TABLE IF NOT EXISTS roles (
                    id INTEGER PRIMARY KEY,
                    name TEXT NOT NULL
                )''')
c.execute('''CREATE TABLE IF NOT EXISTS permissions (
                    id INTEGER PRIMARY KEY,
                    name TEXT NOT NULL
                )''')
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)
                )''')
conn.commit()

# 添加用户、角色和权限
c.execute("INSERT INTO users (username, password) VALUES (?, ?)", ('admin', 'admin123'))
c.execute("INSERT INTO roles (name) VALUES (?)", ('admin_role',))
c.execute("INSERT INTO permissions (name) VALUES (?)", ('download_permissions',))
user_id = c.lastrowid
role_id = c.lastrowid
permission_id = c.lastrowid
c.execute("INSERT INTO user_roles (user_id, role_id) VALUES (?, ?)", (user_id, role_id))
c.execute("INSERT INTO role_permissions (role_id, permission_id) VALUES (?, ?)", (role_id, permission_id))
conn.commit()

# 检查用户权限
def check_permission(username, permission):
    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.username = ? AND permissions.name = ?''', (username, permission))
    return c.fetchone()[0] > 0

# 示例检查
if check_permission('admin', 'download_permissions'):
    print("User has permission to download permissions database.")
else:
    print("User does not have permission to download permissions database.")

conn.close()