Oracle PL/SQL Tutorial/User Privilege/User Permission

Create user and grant permission

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.

CREATE USER userName IDENTIFIED BY password and grant permission

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.

Get user name from all_users

SQL>
SQL> select substr( username, 1, 1 )
  2    from all_users au1
  3   where rownum = 1;
S
-
S
1 row selected.

grant administer database trigger to userName

SQL>
SQL> grant administer database trigger to userName;
SQL>
SQL>

Grant all possible permissions

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>

Grant connect and resource permission to a user

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

Grant connect permission to a user

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.

Grant connect to permission to all name in a table

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>

grant create any context to userName

SQL>
SQL> grant create any context to userName;

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

SQL>
SQL>
SQL> GRANT CREATE SYNONYM, CREATE VIEW, CREATE DATABASE LINK,
  2     CREATE PUBLIC SYNONYM, DROP PUBLIC SYNONYM TO RESOURCE;
Grant succeeded.
SQL>
SQL>

grant execute on dbms_rls to userName

SQL>
SQL>
SQL> grant execute on dbms_rls to userName;
SQL>
SQL>

grant select on tableName to userName

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

SQL>

Grant with admin option

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>

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

SQL>
SQL>
SQL> REVOKE CREATE TABLE, CREATE CLUSTER, CREATE SYNONYM, CREATE VIEW,
  2     CREATE SEQUENCE, CREATE DATABASE LINK FROM CONNECT;

SQL>

Revoke Permissions

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>

Revoke Permissions with Cascade

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>

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:

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.