Oracle学习笔记—-创建用户及授权

创建用户

创建用户 dev并指定表空间 EPRKDB，临时表空间TESTDB_TEMP

CREATE USER "eprk"
IDENTIFIED BY "eprk123"
DEFAULT TABLESPACE EPRKDB
TEMPORARY TABLESPACE TESTDB_TEMP;

授权用户

授权 dev用户有连接数据库和操作表和操作视图数据等权限。

• CONNECT 连接权限
• RESOURCE 操作表、视图、数据权限
• DBA 管理员权限

GRANT CONNECT, RESOURCE,DBA TO "TESTDB";

查看连接oracle 连接用户信息

select SID,AUDSID,USER#,USERNAME,STATUS,SCHEMANAME,
    OSUSER,MACHINE,TERMINAL,PROGRAM,MODULE,LOGON_TIME,PREV_EXEC_START as "last_time",
    BLOCKING_SESSION_STATUS as "bss",SERVICE_NAME,SQL_ID
    from v$session
    where audsid not in (0) and userName = 'I2TEST'
    order by LOGON_TIME desc;

断开当前连接的用户

SELECT 'alter system kill session ' || '''' ||t.sid ||','||t.SERIAL#|| ''';' FROM v$session t WHERE t.USERNAME='CGBCRM';