insufficient privileges

最新推荐文章于 2024-10-10 15:45:19 发布

原创最新推荐文章于 2024-10-10 15:45:19 发布 · 367 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#grant

本文详细介绍了如何使用SQL创建用户、授权特定权限（如创建表、连接、资源等），并通过实例展示了权限管理和角色授权的过程。其中包括了授权、撤销权限的步骤以及查看用户权限的方法。

1.创建用户并授权
SQL> create user test identified by xifenfei;

User created.

SQL> grant connect,resource to test;

Grant succeeded.

2、查看权限
SQL> SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE=’TEST’;

GRANTEE PRIVILEGE ADM
—————————— —————————————- —
TEST UNLIMITED TABLESPACE NO

SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE=’TEST’;

GRANTEE GRANTED_ROLE ADM DEF
—————————— —————————— — —
TEST CONNECT NO YES
TEST RESOURCE NO YES

SQL> CONN TEST/XIFENFEI
Connected.
SQL> SELECT * FROM SESSION_PRIVS;

PRIVILEGE
—————————————-
CREATE SESSION
ALTER SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE DATABASE LINK
CREATE PROCEDURE
CREATE TRIGGER

PRIVILEGE
—————————————-
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE

14 rows selected.
注：目前create table权限是通过resource role授予test用户的

3、授权（create table权限）
SQL> conn / as sysdba
Connected.
SQL> grant create table to test;

Grant succeeded.

SQL> conn test/xifenfei
Connected.
SQL> EXEC CREATE_table(‘c’);

PL/SQL procedure successfully completed.

4.
grant connect to schema_owner;
grant unlimited tablespace to schema_owner;
grant create any trigger to schema_owner;
grant create any procedure to schema_owner;
grant create public database link to schema_owner;
grant drop public database link to schema_owner;
grant create public synonym to schema_owner;
grant drop public synonym to schema_owner;
grant create role to schema_owner;
grant drop any role to schema_owner;