关于授权

####授权处系统用户的全部表权限
```
GRANT select all tables  TO jintingting;

BEGIN
    FOR t IN (SELECT table_name, owner FROM dba_tables 
              WHERE owner NOT IN ('SYS', 'SYSTEM')) LOOP
        BEGIN
            EXECUTE IMMEDIATE 'GRANT SELECT ON ' || t.owner || '.' || t.table_name || ' TO sjgx';
        EXCEPTION
            WHEN OTHERS THEN
                -- 在此处处理可能发生的错误
                DBMS_OUTPUT.PUT_LINE('Error granting SELECT on ' || t.owner || '.' || t.table_name || ': ' || SQLERRM);
        END;
    END LOOP;
END;
/

```

```sql
-- 创建用户
CREATE USER jintingting IDENTIFIED BY "vQxt%xjJ";

--查询其他用户权限
SELECT granted_role FROM dba_role_privs WHERE grantee = 'WANGXH_DBM01';
SELECT privilege FROM dba_sys_privs WHERE grantee = 'WANGXH_DBM01';
SELECT * FROM dba_tab_privs WHERE grantee = 'WANGXH_DBM01';

SELECT granted_role FROM dba_role_privs WHERE grantee = 'WANGXH_DBM01';
CONNECT
R_MGRALL
R_CMS_DML
R_CMC_DML
RESOURCE

SELECT privilege FROM dba_sys_privs WHERE grantee = 'WANGXH_DBM01';
SELECT ANY TABLE
UNLIMITED TABLESPACE

SELECT * FROM dba_tab_privs WHERE grantee = 'WANGXH_DBM01';
GRANTEE                        OWNER                          TABLE_NAME                     GRANTOR                        PRIVILEGE                                GRA HIE
WANGXH_DBM01                   YUSL_DBM01                     FUNC_GET_EXPDATE_CM            YUSL_DBM01                     EXECUTE                                  NO  NO

--授权

-- 授予角色
GRANT CONNECT TO jintingting;
GRANT R_MGRALL TO jintingting;
GRANT R_CMS_DML TO jintingting;
GRANT R_CMC_DML TO jintingting;
GRANT RESOURCE TO jintingting;

-- 授予系统权限
GRANT SELECT ANY TABLE TO jintingting;
GRANT UNLIMITED TABLESPACE TO jintingting;

-- 授权来自 YUSL_DBM01 的权限
GRANT EXECUTE ON YUSL_DBM01.FUNC_GET_EXPDATE_CM TO jintingting;
GRANT EXECUTE ON YUSL_DBM01.FUNC_GET_ZG_VALUE TO jintingting;

-- 授权来自 AIOP 的权限
GRANT UPDATE ON AIOP.AOP_SRV_APP_NODE TO jintingting;
GRANT SELECT ON AIOP.AOP_SRV_APP_NODE TO jintingting;
GRANT INSERT ON AIOP.AOP_SRV_APP_NODE TO jintingting;
GRANT DELETE ON AIOP.AOP_SRV_APP_NODE TO jintingting;

-- 授权来自 AIOP_IN 的权限
GRANT UPDATE ON AIOP_IN.AOP_APP_IPMLINFO TO jintingting;
GRANT SELECT ON AIOP_IN.AOP_APP_IPMLINFO TO jintingting;
GRANT INSERT ON AIOP_IN.AOP_APP_IPMLINFO TO jintingting;
GRANT DELETE ON AIOP_IN.AOP_APP_IPMLINFO TO jintingting;

--验证
SELECT granted_role FROM dba_role_privs WHERE grantee = 'JINTINGTING';
SELECT privilege FROM dba_sys_privs WHERE grantee = 'JINTINGTING';
SELECT * FROM dba_tab_privs WHERE grantee = 'JINTINGTING';

```
####将 GHGMS的表的查询权限授权给ANALYSIS
```
BEGIN
   FOR rec IN (
      SELECT table_name 
      FROM all_tables 
      WHERE owner = 'GHGMS'
   ) LOOP
      EXECUTE IMMEDIATE 'GRANT SELECT ON GHGMS.' || rec.table_name || ' TO ANALYSIS';
   END LOOP;
END;
/
```

DBLOG

导航1234

最近发表

友情链接