![]() ![]() ![]() TO joe Code language: PostgreSQL SQL dialect and PL/pgSQL ( pgsql ) Grant SELECT on all tables The following statement grants all privileges on all tables in the public schema of the dvdrental sample database to the role joe: GRANT ALL ON ALL TABLES IN SCHEMA "public" TO joe Code language: PostgreSQL SQL dialect and PL/pgSQL ( pgsql ) Grant all privileges on all tables in a schema to a role The following statement grants all privileges on the candidates table to the role joe: GRANT ALL ON candidates Grant all privileges on a table to a role Let’s takes some more examples of using the GRANT statement. In addition, it can update or delete data from the table. Now, joe can insert data into the candidates table. Ninth, execute the INSERT statement again from the joe‘s session: INSERT INTO candidates(first_name, last_name, email, phone) TO joe Code language: PostgreSQL SQL dialect and PL/pgSQL ( pgsql ) PostgreSQL issued the following error because joe does not have the INSERT privilege on the candidates table: ERROR: permission denied for table candidates Code language: PostgreSQL SQL dialect and PL/pgSQL ( pgsql )Įighth, grant INSERT, UPDATE, and DELETE privileges on the candidates table to the role joe: GRANT INSERT, UPDATE, DELETE ON candidates ![]() VALUES( 'Joe', 'Com', '40') Code language: PostgreSQL SQL dialect and PL/pgSQL ( pgsql ) Seventh, execute the following INSERTstatement: INSERT INTO candidates(first_name, last_name, email, phone) PostgreSQL returns an empty result set instead of an error. Sixth, execute the SELECT statement from the joe‘s session: SELECT * FROM candidates To grant the SELECT privilege on the candidates table to the role joe, you execute the following GRANT statement in the postgres‘ session: GRANT SELECT ON candidates PostgreSQL issued an error: ERROR: permission denied for table candidates Third, create a new table called candidates: create table candidates (Ĭandidate_id int generated always as identity,įourth, use the role joe to log in to the PostgreSQL database server in a separate session.įifth, attempt to select data from the candidates table from the joe‘s session: SELECT * FROM candidates Second, create a new user role called joe that can login to the PostgreSQL database server: create role joeĬode language: PostgreSQL SQL dialect and PL/pgSQL ( pgsql ) Third, specify the name of the role to which you want to grant privileges.įirst, use the postgres user to connect to the PostgreSQL database server using any client tool of your choice.Second, specify the name of the table after the ON keyword.You use the ALL option to grant all privileges on a table to the role. First, specify the privilege_list that can be SELECT, INSERT, UPDATE, DELETE, TRUNCATE, etc.The following shows the simple form of the GRANT statement that grants one or more privileges on a table to a role: GRANT privilege_list | ALL ON table_name TO role_name Code language: PostgreSQL SQL dialect and PL/pgSQL ( pgsql ) To allow the user role to interact with database objects, you need to grant privileges on the database objects to the user role by using the GRANT statement. ![]() However, it cannot do anything to the database objects like tables, views, functions, etc.įor example, the user role cannot select data from a table or execute a specific function. Introduction to PostgreSQL GRANT statementĪfter creating a role with the LOGIN attribute, the role can log in to the PostgreSQL database server. Summary: in this tutorial, you will learn how to use the PostgreSQL GRANT statement to grant privileges on database objects to a role. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |