CUBRID GRANT ALL TABLES

 

Author: Junsu Yoon

 

Currently, CUBRID does not provide the GRANT ... ON ALL TABLES syntax. You must write and use GRANT syntax manually. With the thought of ‘What would you do if you created this inconvenience automatically?’, I am presenting you with this script.

 

HOW to do GRANT ... ON ALL TABLES .....?

$ sh cub_grant.sh

--------------------------------------------------------------------------------------------------------

CUBRID DBMS, auto-generator for grant all tables
usage : 
                sh cub_grant.sh <dbname> <grantee user> <grantor user> <grantor user password> <option>

        <option>
                -view : grantee user all grant view
                -dml : default SELECT, DELETE, UPDATE, INSERT
                -ddl : default ALTER, INDEX, EXECUTE
                -all : ALL PRIVILEGES(dml+ddl)

        <file creation info>
                default path : .
                -dml : ./GRANT_DML.sql
                -ddl : ./GRANT_DDL.sql
                -all : ./GRANT_ALL.sql

--------------------------------------------------------------------------------------------------------

 

• Only available in Linux Environment
• It is written in bash script
• It is possible to perform CUBRID engine owned account (requires rwx permission) regardless of location.
• How to use: sh cub_grant.sh
  • <dbname>: Database name to use<grantee user>: User name to be granted authority
  • <grantor user>: Owner user name of the authority to be granted
  • <grantor user password>: the password of the owner user of the authority to be granted
  • <option>view: Check the authorization information of grantee user
    • dml: DML GRANT creation of all tables of grantor user
    • ddl: DDL GRANT generation of all tables of grantor user
    • all: Create ALL PRIVILEGES (DML+DDL) GRANT of all tables of grantor user
  • <File creation info>: The location to be created is the script execution directory
    • dml : GRANT_DML.sql
    • ddl : GRANT_DDL.sql
    • all : GRANT_ALL.sql

 

$ cat cub_grant.sh |more

--------------------------------------------------------------------------------------------------------

  1 #!/bin/bash
  2 ## encoding : utf-8
  3 ## Create by, CUBRID INC
  4 
  5 ## User Configuration Parameters
  6 FILE_PATH="."
  7 GRANT_DML="SELECT, DELETE, UPDATE, INSERT"
  8 GRANT_DDL="ALTER, INDEX, EXECUTE"
  9 GRANT_ALL="ALL PRIVILEGES"
 10 ##
 11 
 12 
 13 ## Tool Configuration Parameters
 14 DB_NM=$1
 15 GRANTEE_USER=$2
 16 GRANTOR_USER=$3
 17 GRANTOR_USER_PSW=$4
 18 GRANT_OPTION=$5
 19 ##

--------------------------------------------------------------------------------------------------------

• You can modify the User Configuration Parameters at the top of the script as desired.
  • FILE_PATH: You can change the default path where GRANT files are created.
  • GRANT_DML: If only a part of DML is needed, it can be modified as "SELECT, INSERT".
  • GRANT_DDL: If only a part of DDL is needed, it can be modified as "ALTER,INDEX".
  • GRANT_ALL: No need to change

 

$ ls -rlth

--------------------------------------------------------------------------------------------------------
합계 20K
-rw-rw-r-- 1 cubrid cubrid  341  6월 25 13:45 GRANT_ALL.sql
-rw-rw-r-- 1 cubrid cubrid  469  6월 25 13:45 GRANT_DML.sql
-rw-rw-r-- 1 cubrid cubrid  397  6월 25 13:45 GRANT_DDL.sql
-rw-rw-r-- 1 cubrid cubrid 7.5K  6월 25 13:48 cub_grant.sh

-------------------------------------------------------------------------------------------------------

< File is created as above according to the options>

 

1. GRANT_DML.sql

                

GRANT SELECT, DELETE, UPDATE, INSERT ON  tbl1   TO  DBA ;
GRANT SELECT, DELETE, UPDATE, INSERT ON  tbl2   TO  DBA ;
GRANT SELECT, DELETE, UPDATE, INSERT ON  [data]  TO  DBA ;
GRANT SELECT, DELETE, UPDATE, INSERT ON  [add]  TO  DBA ;
GRANT SELECT, DELETE, UPDATE, INSERT ON  [primary]  TO  DBA ;

 

 

2. GRANT_DDL.sql

                

GRANT ALTER, INDEX, EXECUTE ON  tbl1   TO  DBA ;
GRANT ALTER, INDEX, EXECUTE ON  tbl2   TO  DBA ;
GRANT ALTER, INDEX, EXECUTE ON  [data]  TO  DBA ;
GRANT ALTER, INDEX, EXECUTE ON  [add]  TO  DBA ;
GRANT ALTER, INDEX, EXECUTE ON  [primary]  TO  DBA ;

 

 

3. GRANT_ALL.sql

                

GRANT ALL PRIVILEGES ON  tbl1   TO  DBA ;
GRANT ALL PRIVILEGES ON  tbl2   TO  DBA ;
GRANT ALL PRIVILEGES ON  [data]  TO  DBA ;
GRANT ALL PRIVILEGES ON  [add]  TO  DBA ;
GRANT ALL PRIVILEGES ON  [primary]  TO  DBA ;

 

 

<CUBRID has 347 number of reserved word. and If you want to use the reserved word as a table name, brackets should be enclosed.>

 

1. If it is not a reserved word

 

GRANT SELECT, DELETE, UPDATE, INSERT ON test_tbl TOdev_user ;

 

2. In the case of reserved words

GRANT SELECT, DELETE, UPDATE, INSERT ON[primary]TOdev_user ;​

 

 The script is attached below cub_grant (1).sh