Oracle PL/SQL Tutorial/User Privilege/User Permission

Create user and grant permission

   <source lang="sql">

SQL> SQL> create user oracle_admin

 2      identified by oracle_admin;

User created. SQL> SQL> grant create session, dba

 2  to oracle_admin;

Grant succeeded. SQL> SQL> drop user oracle_admin; User dropped.</source>

CREATE USER userName IDENTIFIED BY password and grant permission

   <source lang="sql">

SQL> CREATE USER user1 IDENTIFIED BY pass1; User created. SQL> CREATE USER user2 IDENTIFIED BY pass2; User created. SQL> GRANT CREATE SESSION TO user1, user2; Grant succeeded. SQL> SQL> DROP USER user1; User dropped. SQL> DROP USER user2; User dropped.</source>

Get user name from all_users

   <source lang="sql">

SQL> SQL> select substr( username, 1, 1 )

 2    from all_users au1
 3   where rownum = 1;

S - S 1 row selected.</source>

grant administer database trigger to userName

   <source lang="sql">

SQL> SQL> grant administer database trigger to userName; SQL> SQL></source>

Grant all possible permissions

   <source lang="sql">

SQL> SQL> SQL> create user verifyit identified by verify ; User created. SQL> SQL> SQL> grant create session, create table, create procedure,

 2      create sequence, create view, create trigger,
 3      create synonym, create materialized view,
 4      query rewrite, create any directory, create type,
 5      dba, aq_administrator_role, javasyspriv to verifyit ;
   dba, aq_administrator_role, javasyspriv to verifyit
                               *

ERROR at line 5: ORA-01919: role "JAVASYSPRIV" does not exist

SQL> SQL> drop user verifyit; User dropped. SQL></source>

Grant connect and resource permission to a user

   <source lang="sql">

SQL> SQL> CREATE USER tempc IDENTIFIED BY tempc; User created. SQL> GRANT CONNECT, RESOURCE to tempc; Grant succeeded. SQL> drop user tempc; User dropped.</source>

Grant connect permission to a user

   <source lang="sql">

SQL> SQL> SQL> CREATE USER tempb IDENTIFIED BY tempb; User created. SQL> SQL> GRANT CONNECT TO tempb; Grant succeeded. SQL> SQL> drop user tempb; User dropped.</source>

Grant connect to permission to all name in a table

   <source lang="sql">

SQL> SQL> SQL> begin

 2      for x in (select ename from scott.emp  where ename <> "SCOTT")
 3      loop
 4          execute immediate "grant connect to " || x.ename  ||
 5                  " identified by " || x.ename;
 6      end loop;
 7  end;
 8  /

SQL> SQL></source>

grant create any context to userName

   <source lang="sql">

SQL> SQL> grant create any context to userName;</source>

Grant CREATE SYNONYM, CREATE VIEW, CREATE DATABASE LINK, CREATE PUBLIC SYNONYM, DROP PUBLIC SYNONYM

   <source lang="sql">

SQL> SQL> SQL> GRANT CREATE SYNONYM, CREATE VIEW, CREATE DATABASE LINK,

 2     CREATE PUBLIC SYNONYM, DROP PUBLIC SYNONYM TO RESOURCE;

Grant succeeded. SQL> SQL></source>

grant execute on dbms_rls to userName

   <source lang="sql">

SQL> SQL> SQL> grant execute on dbms_rls to userName; SQL> SQL></source>

grant select on tableName to userName

   <source lang="sql">

SQL> SQL> SQL> grant select on tableName to userName;

SQL></source>

Grant with admin option

   <source lang="sql">

SQL> SQL> SQL> create user demo_ddl identified by demo_ddl; User created. SQL> SQL> grant connect, resource to demo_ddl with admin option; Grant succeeded. SQL> SQL> grant create user to demo_ddl; Grant succeeded. SQL> grant drop user to demo_ddl; Grant succeeded. SQL> SQL> --@connect demo_ddl/demo_ddl SQL> SQL> drop user demo_ddl cascade; User dropped. SQL> SQL></source>

REVOKE CREATE TABLE, CREATE CLUSTER, CREATE SYNONYM, CREATE VIEW, CREATE SEQUENCE, CREATE DATABASE LINK FROM CONNECT

   <source lang="sql">

SQL> SQL> SQL> REVOKE CREATE TABLE, CREATE CLUSTER, CREATE SYNONYM, CREATE VIEW,

 2     CREATE SEQUENCE, CREATE DATABASE LINK FROM CONNECT;

SQL></source>

Revoke Permissions

   <source lang="sql">

SQL> SQL> SQL> CREATE USER Alice IDENTIFIED BY simplepassword; User created. SQL> SQL> GRANT SELECT, INSERT ON emp TO Alice WITH GRANT OPTION; SQL> SQL> REVOKE INSERT ON emp FROM Alice; SQL> SQL> SQL> drop user alice; User dropped. SQL></source>

Revoke Permissions with Cascade

   <source lang="sql">

SQL> SQL> SQL> CREATE USER Alice IDENTIFIED BY simplepassword; User created. SQL> SQL> GRANT SELECT, INSERT ON emp TO Alice WITH GRANT OPTION; SQL> SQL> SQL> REVOKE INSERT ON emp FROM Alice CASCADE; SQL> SQL> drop user alice; User dropped. SQL></source>

To connect to the database a user must be granted the permission to create a session, which is the CREATE SESSION system privilege.

Permissions are granted by a privileged user (system, for example) using the GRANT statement.

The following example grants the CREATE SESSION permission to jason:

   <source lang="sql">

CONNECT system/manager SQL> CREATE USER jason IDENTIFIED BY pass; User created. SQL> SQL> GRANT CREATE SESSION TO jason; Grant succeeded. SQL> SQL> DROP USER jason; User dropped.</source>