GRANT

GRANT#

Syntax#

grant ::=#

grant_image171

grant_system_privilege ::=#

grant_system_privilege

grant_object_privilege ::=#

grant_object_privilege

Prerequisites#

Only the SYS user and users with the GRANT ANY ROLE system privilege can grant system privileges to a role. In addition, an object privilege can be granted to other users only by the owner of the object or who has been granted object privilege with WITH GRANT OPTION.

Description#

This statement is used to grant privileges to access the database or specified objects to one or more specified users.

Access privileges are classified as either system privileges or object privileges.

grant_system_privilege#

System privileges are usually managed by the SYS user. The SYS user can grant limited system privileges to allow users to perform specific database tasks. System privileges can be seen as broad privileges that control objects in any schema.

System privileges are required in order to execute DDL statements and DCL statements.

System Privileges#

This is used to specify the name of the system access privilege that will be granted.

role#

This is used to specify the name of the role to be granted.

A role cannot be granted to another role or PUBLIC.
A maximum number of 126 roles can be granted per user.
A user cannot immediately use a role which has been granted. The privileges of a role are enabled after the user has connected to a database.

ALL PRIVILEGES#

This is used to grant all system privileges to the specified user or users.

TO user#

This is used to specify name of the user or users to whom the system privilege(s) will be granted.

TO role#

This is used to specify the name of the role to which system privileges are to be granted.

TO PUBLIC#

This is used to specify that the system privilege(s) is/are to be granted to all users.

Notes:#

Just like the SYS user, any user to whom the GRANT ANY PRIVILEGES system is granted can grant all system access privileges to other users.
The SYS user has all system access privileges.
The presence of the ANY keyword in the name of a system privilege indicates that the privilege pertains to all schema. For example, the SELECT ANY TABLE privilege will allow the user to whom it is granted to run a SELECT statement on any table in the database.
The CREATE privilege is granted to allow users to create objects, and includes permission to DROP (i.e. remove) the objects they have created.
The CREATE TABLE object privilege allows users to create indexes as well as tables. The authority to create indexes is an object privilege, not a system privilege.
When a new user is created, the following privileges are typically granted to the user: CREATE DATABASE LINK, CREATE LIBRARY, CREATE MATERIALIZED_VIEW, CREATE PROCEDURE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE TRIGGER, CREATE VIEW.

The following query can be used to display the list of system privileges supported in Altibase:

iSQL> SELECT * FROM SYSTEM_.SYS_PRIVILEGES_ where PRIV_TYPE = 2;

Altibase supports the following system privileges.

PrivID	System privilege	Name	Purpose
1		ALL	Even if this privilege is granted to a user, the user will still not have the ALTER DATABASE, DROP DATABASE, or MANAGE TABLESPACE privileges.
201	DATABASE	ALTER SYSTEM	For changing Altibase property settings using ALTER SYSTEM statements when Altibase is online
233		ALTER DATABASE	Cannot be granted to any users other than the SYS user
234		DROP DATABASE	Cannot be granted to any users other than the SYS user
202	INDEX	CREATE ANY INDEX	For creating indexes not only in the user's own schema but also in other users' schema
203		ALTER ANY INDEX	For altering the definition of any index in the database.
204		DROP ANY INDEX	For dropping any index from the database
205	PROCEDURE	CREATE PROCEDURE	For creating stored procedures and stored functions in the user's own schema
206		CREATE ANY PROCEDURE	For creating stored procedures and stored functions not only in the user's own schema but also in other users' schema
207		ALTER ANY PROCEDURE	For recompiling any stored procedure or function in the database
208		DROP ANY PROCEDURE	For dropping any stored procedure or function in the database
209		EXECUTE ANY PROCEDURE	For executing any stored procedure or function in the database
210	SEQUENCE	CREATE SEQUENCE	For creating sequences in the user's own schema
211		CREATE ANY SEQUENCE	For creating sequences not only in the user's own schema but also in other users' schema
212		ALTER ANY SEQUENCE	For changing the definition of any sequence in the database
213		DROP ANY SEQUENCE	For deleting any sequence in the database
214		SELECT ANY SEQUENCE	For querying any sequence in the database
215	SESSION	CREATE SESSION	For connecting to the database
216		ALTER SESSION	Granted automatically to every user
217	TABLE	CREATE TABLE	For creating tables in the user's own schema
218		CREATE ANY TABLE	For creating tables not only in the user's own schema but also in other users' schema
219		ALTER ANY TABLE	For truncating all records from any table or changing the definition of any table in the database
220		DELETE ANY TABLE	For deleting any table from the database
221		DROP ANY TABLE	For dropping any table in the database
222		INSERT ANY TABLE	For inserting new records into any table in the database
223		LOCK ANY TABLE	For locking any table in the database
224		SELECT ANY TABLE	For querying any table in the database
225		UPDATE ANY TABLE	For changing the data in any table in the database
226	USER	CREATE USER	For creating new users
227		ALTER USER	For changing the definition of any user in the database
228		DROP USER	For dropping users
229	VIEW	CREATE VIEW	For creating views in the user's own schema
230		CREATE ANY VIEW	For creating views not only in the user's own schema but also in other users' schema
231		DROP ANY VIEW	For deleting any view in the database
232	MISCELLANEOUS	GRANT ANY PRIVILEGES	For granting any system privilege to other users
235	TABLESPACES	CREATE TABLESPACE	For creating tablespaces
236		ALTER TABLESPACE	For changing the definition of a tablespace
237		DROP TABLESPACE	For deleting tablespaces
238		MANAGE TABLESPACE	Cannot be granted to any users other than the SYS user
240		SYSDBA	Cannot be granted to any users other than the SYS user
241	TRIGGER	CREATE TRIGGER	For creating new triggers
242		CREATE ANY TRIGGER	For creating triggers not only in the user's own schema but also in other users' schema
243		ALTER ANY TRIGGER	For changing the definition of any trigger in the database
244		DROP ANY TRIGGER	For deleting any trigger in the database
245	SYNONYM	CREATE SYNONYM	For creating new triggers
246		CREATE PUBLIC SYNONYM	For creating a PUBLIC synonym
247		CREATE ANY SYNONYM	For creating private synonyms not only in the user's own schema but also in other users' schema
248		DROP ANY SYNONYM	For dropping any private synonym
249		DROP PUBLIC SYNONYM	For dropping public synonyms
250	DIRECTORY	CREATE ANY DIRECTORY	A directory object can be created to control a file within the stored procedure.
251		DROP ANY DIRECTORY	This privilege can drop a directory object.
252	MATERIALIZED VIEW	CREATE MATERIALIZED VIEW	This privilege can create a new materialized view in the user's own schema
253		CREATE ANY MATERIALIZED VIEW	This privilege can create a materialized view not only in the user's own schema, but also in other user's schemas.
254		ALTER ANY MATERIALIZED VIEW	This privilege can be modified any materialized view in the database.
255		DROP ANY MATERIALIZED VIEW	Any materialized view in the database can be dropped with this privilege.
256	LIBRARY	CREATE LIBRARY	For creating new library objects in the user's own schema
257		CREATE ANY LIBRARY	For creating library objects not only in the user's own schema but also in other users' schema
258		ALTER ANY LIBRARY	For recompiling any library object in the database
259		DROP ANY LIBRARY	For dropping any library object in the database
260	DATABASE LINK	CREATE DATABASE LINK	New database link can be created.
261		CREATE PUBLIC_DATABASE LINK	Public database link can be created.
262		DROP PUBLIC DATABASE LINK	Public database link can be dropped.
263	ROLE	CREATE ROLE	For creating a new role
264		DROP ANY ROLE	For dropping all roles existing in the database
265		GRANT ANY ROLE	For granting all roles to other users
266	JOB	CREATE ANY JOB	This privilege can create a new job not only in the user's own schema, but also in other user's schemas.
268		ALTER ANY JOB	Any job in the database can be modified with this privilege.
267		DROP ANY JOB	Any JOB in the database can be dropped with this privilege.

grant_object_privilege#

Once a user has been granted privileges for a particular object, the user can access and/or manipulate the object. Object access privileges are typically managed by the owner of the object.

If system privileges have not been granted, object access privileges are required in order to execute DML statements.

Object Privileges#

This clause is used when it is desired to grant only particular privileges for the object the table later in this section shows which privileges are supported for which objects).

ALL [PRIVILEGES]#

This clause is used to grant all possible privileges for the object.

ON object#

This is used to specify the object, such as a table, sequence, or stored procedure, for which to grant privileges.

ON DIRECTORY directory_name#

This clause is used to specify the name of the directory object, which is used in stored procedures to manipulate directories and files in the file system, for which to grant privileges.

TO user#

This is used to specify the name of the user or users to whom the object privilege(s) will be granted.

TO PUBLIC#

This is used to specify that the object privilege(s) is/are to be granted to all users.

TO role#

This is used to specify the name of the role to which object privileges are to be granted.

WITH GRANT OPTION#

The WITH GRANT OPTION is used to enable the grantee to grant the object privileges to other users. However, this option cannot be used to grant object privileges to a role.

Summary#

The term "object owner" refers to the user who created an object.
In order to grant object access privileges, it is necessary to be the SYS user, the owner of the object, or a user to whom the relevant object access privileges have been granted with the WITH GRANT OPTION.
The owner of an object automatically has all privileges for the object.

The following query can be used to display all of the object privileges supported in Altibase.

SELECT * FROM SYSTEM_.SYS_PRIVILEGES_ where PRIV_TYPE = 1;

Altibase supports the following object privileges:

Priv ID	Object privileges	Table	Sequence	PSM/ External Procedure	View	directory	External Library
101	ALTER	O	O
102	DELETE	O
103	EXECUTE			O			O
104	INDEX	O
105	INSERT	O
106	REFERENCES	O
107	SELECT	O	O		O
108	UPDATE	O
109	READ					O
110	WRITE					O

All users automatically have SELECT privileges for meta tables.

Example 1: System Privileges#

<Query 1> In the following example, the EXECUTE ANY PROCEDURE, SELECT ANY TABLE, ALTER ANY SEQUENCE, INSERT ANY TABLE, and SELECT ANY SEQUENCE system privileges are granted to the user user5.

iSQL> CREATE TABLE seqtbl(i1 INTEGER);
Create success.
iSQL> CREATE OR REPLACE PROCEDURE proc1
AS
BEGIN
  FOR i IN 1 .. 10 LOOP
    INSERT INTO seqtbl VALUES(i);
  END LOOP;
END;
/
Create success.

iSQL> CREATE USER uare5 IDENTIFIED BY rose5;
Create success.
iSQL> GRANT EXECUTE ANY PROCEDURE, SELECT ANY TABLE TO uare5;
Grant success.
iSQL> CONNECT uare5/rose5;
Connect success.
iSQL> EXEC sys.proc1;
Execute success.
iSQL> SELECT * FROM sys.seqtbl;
SEQTBL.I1   
--------------
1           
2           
3           
4           
5           
6           
7           
8           
9           
10          
10 rows selected.

iSQL> CONNECT sys/manager;
Connect success.
iSQL> CREATE SEQUENCE seq1
  START WITH 13
  INCREMENT BY 3
  MINVALUE 0 NOMAXVALUE;
Create success.
iSQL> INSERT INTO seqtbl VALUES(seq1.NEXTVAL);
1 row inserted.
iSQL> INSERT INTO seqtbl VALUES(seq1.NEXTVAL);
1 row inserted.
iSQL> SELECT * FROM seqtbl;
SEQTBL.I1   
--------------
1           
2           
3           
4           
5           
6           
7           
8           
9           
10          
13          
16          
12 rows selected.

iSQL> GRANT ALTER ANY SEQUENCE, INSERT ANY TABLE, SELECT ANY SEQUENCE TO uare5;
Grant success.
iSQL> CONNECT uare5/rose5;
Connect success.    
iSQL> ALTER SEQUENCE sys.seq1
  INCREMENT BY 50
  MAXVALUE 100
  CYCLE;
Alter success.

iSQL> INSERT INTO sys.seqtbl VALUES(sys.seq1.NEXTVAL);
1 row inserted.
iSQL> INSERT INTO sys.seqtbl VALUES(sys.seq1.NEXTVAL);
1 row inserted.
iSQL> INSERT INTO sys.seqtbl VALUES(sys.seq1.NEXTVAL);
1 row inserted.
iSQL> INSERT INTO sys.seqtbl VALUES(sys.seq1.NEXTVAL);
1 row inserted.
iSQL> SELECT * FROM sys.seqtbl;
SEQTBL.I1   
--------------
1           
2           
3           
4           
5           
6           
7           
8           
9           
10          
13          
16          
66          
0           
50          
100         
16 rows selected.

Example 2: Granting System Privileges to a Role#

<Query 2> Create a role with the name alti_role, and then grant system privileges to it, such as CREATE USER, DROP USER, etc.

iSQL> create role alti_role;
Create success.
iSQL> grant create user, drop user to alti_role;
Grant success.
iSQL> create user user01 identified by user01;
Create success.
iSQL> grant alti_role to user01;
Grant success.
iSQL> connect user01/user01
Connect success.
iSQL> create user user02 identified by user02;
Create success.
iSQL> drop user user02;
Drop success.

Example 3: Granting Object Privileges with WITH GRANT OPTION#

<Query 3> In the following example, the SELECT and DELETE object privileges on the table employees are granted to the user uare6 with the WITH GRANT OPTION. This user then passes these privileges on to the uare7 and uare8 users.

iSQL> CREATE USER uare6 IDENTIFIED BY rose6;
Create success.
iSQL> GRANT CREATE USER TO uare6;
Grant success.
iSQL> @ ?/sample/APRE/schema/schema

iSQL> CONNECT sys/manager;
iSQL> GRANT SELECT, DELETE ON employees TO uare6 WITH GRANT OPTION;
Grant success.

iSQL> CONNECT uare6/rose6;
Connect success.
iSQL> CREATE USER uare7 IDENTIFIED BY rose7;
Create success.
iSQL> GRANT SELECT, DELETE ON sys.employees TO uare7;
Grant success.
iSQL> CONNECT uare7/rose7;
Connect success.
iSQL> DELETE FROM SYS.employees WHERE eno = 12;
1 row deleted.
iSQL> SELECT eno, e_lastname FROM sys.employees WHERE eno = 12;
ENO         E_LASTNAME                 
-------------------------------------
No rows selected.

iSQL> CONNECT sys/manager;
Connect success.
iSQL> CREATE USER uare8 IDENTIFIED BY rose8;
Create success.
iSQL> CONNECT uare6/rose6;
Connect success.
iSQL> GRANT SELECT, DELETE ON sys.employees TO uare8;
Grant success.

Because the uare6 user was granted object access privileges using the WITH GRANT OPTION, this user can grant these privileges not only to the user uare7, who was created by uare6, but also to the user uare8, who was created by the original grantor (the SYS user).

iSQL> CONNECT uare8/rose8;
Connect success.
iSQL> DELETE FROM sys.employees WHERE eno = 13;
1 row deleted.

iSQL> SELECT eno, e_lastname FROM sys.employees WHERE eno = 13;
ENO         E_LASTNAME                 
-------------------------------------
No rows selected.

Example 4: Granting System and Object Privileges with WITH GRANT OPTION#

<Query 4> The following is an exmple of granting system and object privileges to a user and then revoking each privilege.

1) The SYS user has granted all system privileges to uare9.

iSQL> CONNECT sys/manager;
Connect success.
iSQL> CREATE TABLE book(
  isbn CHAR(10) PRIMARY KEY,
  title VARCHAR(50),
  author VARCHAR(30),
  edition INTEGER DEFAULT 1,
  publishingyear INTEGER,
  price NUMBER(10,2),
  pubcode CHAR(4));
Create success.
iSQL> CREATE TABLE inventory(
  subscriptionid CHAR(10) PRIMARY KEY,
  storecode CHAR(4),
  purchasedate DATE,
  quantity INTEGER,
  paid CHAR(1));
Create success.

iSQL> CREATE USER uare9 IDENTIFIED BY rose9;
Create success.
iSQL> GRANT ALL PRIVILEGES TO uare9;
Grant success.

2) The SYS user is granting user uare9 the REFERENCES privilege on the object book as the WITH GRANT OPTION.

iSQL> GRANT REFERENCES ON book TO uare9 WITH GRANT OPTION;
Grant success.

- The user uare9 receives the REFERENCES object privilege for the book object from the SYS user h the WITH GRANT OPTION, and thus uare9 is able to grant another user (uare10) the REFERENCES ect privilege for the book object.

3) The user uare9 inputs data into the book and inventory table, which is owned by the SYS user.

iSQL> CONNECT uare9/rose9;
Connect success.

iSQL> INSERT INTO sys.book VALUES ('0070521824', 'Software Engineering', 'Roger S. Pressman', 4, 2, 100000, 'CHAU');
1 row inserted.
iSQL> INSERT INTO sys.book VALUES ('0137378424', 'Database Processing', 'David M. Kroenke', 6, 2, 80000, 'PREN');
1 row inserted.

iSQL> INSERT INTO sys.inventory VALUES('BORD000002', 'BORD', '12-Jun-2003', 6, 'N');
1 row inserted.
iSQL> INSERT INTO sys.inventory VALUES('MICR000001', 'MICR', '07-Jun-2003', 7, 'N');
1 row inserted.

The user uare9 inputs data into the inventory table, which is owned by the SYS user.

4) The user uare9 queries the book and inventory table, which is owned by the SYS user.

iSQL> SELECT * FROM sys.book;
ISBN                  TITLE                 AUTHOR                EDITION     PUBLISHINGYEAR PRICE       PUBCODE
-------------------------------------------------------------------------------------------------------------
0070521824            Software Engineering  Roger S. Pressman     4           1982        100000      CHAU
0137378424            Database Processing   David M. Kroenke      6           1972        80000       PREN
2 rows selected.

iSQL> SELECT * FROM sys.inventory;
SUBSCRIPTIONID        STORECODE             PURCHASEDATE QUANTITY    PAID                  
-------------------------------------------------------------------------------------------------
BORD000002            BORD                  12-JUN-2003  6           N                     
MICR000001            MICR                  07-JUN-2003  7           N                     
2 rows selected.

iSQL> CREATE TABLE book(
  isbn CHAR(10) PRIMARY KEY,
  title VARCHAR(50),
  author VARCHAR(30),
  edition INTEGER DEFAULT 1,
  publishingyear INTEGER,
  price NUMBER(10,2),
  pubcode CHAR(4));
Create success.

iSQL> CREATE TABLE inventory(
  subscriptionid CHAR(10) PRIMARY KEY,
  isbn CHAR(10) CONSTRAINT fk_isbn REFERENCES book(isbn), 
  storecode CHAR(4),
  purchasedate DATE,
  quantity INTEGER,
  paid CHAR(1));
Create success.

5) Because the SYS user granted ALL PRIVILEGES to the user uare9, uare9 can create other users.

iSQL> CREATE USER uare10 IDENTIFIED BY rose10;
Create success.

6) Because the SYS user granted the REFERENCES privilege to the user uare9 with the WITH GRANT OPTION, uare9 can pass this privilege on to other users.

iSQL> GRANT REFERENCES ON sys.book TO uare10;
Grant success.

7) Because the SYS user granted the GRANT ANY PRIVILEGES privilege to the user uare9, uare9 can grant system privileges to other users.

iSQL> GRANT ALTER ANY TABLE, INSERT ANY TABLE, SELECT ANY TABLE, DELETE ANY
TABLE TO uare10;
Grant success.

8) Because the user uare10 has the ALTER ANY TABLE and REFERENCES privileges, uare10 can create a constraint in a table belonging to another user.

iSQL> CONNECT uare10/rose10;
Connect success.
iSQL> ALTER TABLE sys.inventory
  ADD COLUMN (isbn CHAR(10) CONSTRAINT fk_isbn REFERENCES sys.book(isbn));
Alter success.

9) Because the user uare10 has the INSERT ANY TABLE privilege, uare10 can enter data into a table belonging to uare9.

iSQL> INSERT INTO uare9.book VALUES('0471316156', 'JAVA and CORBA', 'Robert Orfali', 2, 1998, 50000, 'PREN');
1 row inserted.
iSQL> INSERT INTO uare9.inventory VALUES('TOWE000001', '0471316156', 'TOWE', '01-Jun-2003', 5, 'N');
1 row inserted.

Because the user uare10 has the INSERT ANY TABLE privilege, uare10 can enter data into a table belonging to the SYS user.

iSQL> INSERT INTO sys.book VALUES('053494566X', 'Working Classes', 'Robert Orfali', 1, 1999, 80000, 'WILE');
1 row inserted.
iSQL> INSERT INTO sys.inventory VALUES('MICR000005', 'WILE', '28-JUN-1999', 8, 'N', '053494566X');
1 row inserted.

10) Because the user uare10 has the SELECT ANY TABLE privilege, uare10 can query a table belonging to uare9.

iSQL> SELECT * FROM uare9.book;
ISBN                  TITLE                 AUTHOR                EDITION     PUBLISHINGYEAR PRICE       PUBCODE
------------------------------------------------------------------------------------------------------------------------
0471316156            JAVA and CORBA        Robert Orfali         2           1998        50000       PREN
1 row selected.

iSQL> SELECT * FROM uare9.inventory;
SUBSCRIPTIONID        ISBN                  STORECODE             PURCHASEDATE QUANTITY    PAID
------------------------------------------------------------------------------------------------------------------------
TOWE000001            0471316156            TOWE                  01-JUN-2003  5           N
1 row selected.

Because the user uare10 has the SELECT ANY TABLE privilege, uare10 can query a table belonging to the SYS user.

iSQL> SELECT * FROM sys.book;
ISBN                  TITLE                 AUTHOR                EDITION     PUBLISHINGYEAR PRICE       PUBCODE
------------------------------------------------------------------------------------------------------------------
0070521824            Software Engineering  Roger S. Pressman     4           1982        100000      CHAU
0137378424            Database Processing   David M. Kroenke      6           1972        80000       PREN
053494566X            Working Classes       Robert Orfali         1           1999        80000       WILE
3 rows selected.

iSQL> SELECT * FROM sys.inventory;
SUBSCRIPTIONID        STORECODE             PURCHASEDATE QUANTITY    PAID                  ISBN
------------------------------------------------------------------------------------------------------------------------
BORD000002            BORD                  12-JUN-2003  6           N
MICR000001            MICR                  07-JUN-2003  7           N
MICR000005            WILE                  28-JUN-1999  8           N                     053494566X
3 rows selected.

11) Because the user uare10 has the DELETE ANY TABLE privilege, uare10 can delete data from a table belonging to the SYS user.

iSQL> DELETE FROM uare9.inventory WHERE subscriptionid = 'TOWE000001';
1 row deleted.
iSQL> SELECT * FROM uare9.inventory;
SUBSCRIPTIONID        ISBN                  STORECODE             PURCHASEDATE QUANTITY    PAID
--------------------------------------------------------------------------------------------------------------------
No rows selected.

iSQL> DELETE FROM sys.inventory WHERE subscriptionid = 'MICR000005';
1 row deleted.
iSQL> SELECT * FROM sys.inventory;
SUBSCRIPTIONID        STORECODE             PURCHASEDATE QUANTITY    PAID                  ISBN
--------------------------------------------------------------------------------------------------------------------
BORD000002            BORD                  12-JUN-2003  6           N
MICR000001            MICR                  07-JUN-2003  7           N
2 rows selected.

12) The user uare9 revokes all privileges that uare9 granted to uare10 without executing the REVOKE ALL statement.

iSQL> CONNECT uare9/rose9;
Connect success.
iSQL> REVOKE ALTER ANY TABLE, INSERT ANY TABLE, SELECT ANY TABLE, DELETE ANY TABLE FROM uare10;
Revoke success.

13) When uare10's REFERENCES privilege is revoked, referential integrity constraints that refer to primary key or unique keys in the sys.book table, which belongs to uare10's schema, are also dropped.

iSQL> REVOKE REFERENCES ON sys.book FROM uare10 CASCADE CONSTRAINTS;
Revoke success.

14) All of uare9's system privileges are revoked.

iSQL> CONNECT sys/manager;
Connect success.
iSQL> REVOKE ALL PRIVILEGES FROM uare9;
Revoke success.

15) The GRANT ANY PRIVILEGES privilege is revoked from uare9.

iSQL> REVOKE GRANT ANY PRIVILEGES FROM uare9;
Revoke success.

16) The REFERENCES privilege on the book table is revoked from uare9.

iSQL> REVOKE REFERENCES ON book FROM uare9;
Revoke success.

Example 5: Granting Object Privileges Using Roles#

<Query 5> Grant the SELECT, UPDATE, INSER, and DELETE object privileges on the T1 table of user01 to the alti_role. Then, grant the alti_role role to the user user02.

iSQL> create role alti_role;
Create success.
iSQL> create user user01 identified by user01;
Create success.
iSQL> create user user02 identified by user02;
Create success.
iSQL> connect user01/user01
Connect success.
iSQL> create table t1 (i1 integer);
Create success.
iSQL> grant select,insert,update,delete on t1 to alti_role;
Grant success.
iSQL> connect sys/manager
Connect success.
iSQL> grant alti_role to user02;
Grant success.
iSQL> connect user02/user02;
Connect success.
iSQL> insert into user01.t1 values (1);
1 row inserted.
iSQL> insert into user01.t1 values (2);
1 row inserted.
iSQL> select * from user01.t1;
T1.I1       
--------------
1           
2           
2 rows selected.
iSQL> update user01.t1 set i1=3 where i1=1;
1 row updated.
iSQL> select * from user01.t1;
T1.I1       
--------------
2           
3           
2 rows selected.
iSQL> delete from user01.t1 where i1=2;
1 row deleted.
iSQL> select * from user01.t1;
T1.I1       
--------------
3           
1 row selected.

Administrator

Reference

Developer

Tools

External Tools

GRANT

GRANT#

Syntax#

grant ::=#

grant_system_privilege ::=#

grant_object_privilege ::=#

Prerequisites#

Description#

grant_system_privilege#

System Privileges#

role#

ALL PRIVILEGES#

TO user#

TO role#

TO PUBLIC#

Notes:#

grant_object_privilege#

Object Privileges#

ALL [PRIVILEGES]#

ON object#

ON DIRECTORY directory_name#

TO user#

TO PUBLIC#

TO role#

WITH GRANT OPTION#

Summary#

Example 1: System Privileges#

Example 2: Granting System Privileges to a Role#

Example 3: Granting Object Privileges with WITH GRANT OPTION#

Example 4: Granting System and Object Privileges with WITH GRANT OPTION#

Example 5: Granting Object Privileges Using Roles#