Home > Database > Mysql Tutorial > body text

Daily SQL scripts in GreenPlum

一个新手
Release: 2017-09-19 09:40:46
Original
2122 people have browsed it

--1. Specify the total number of days in the month

SELECT EXTRACT(DAY FROM CAST('2017-08-01' AS DATE) + INTERVAL '1 month' - INTERVAL '1 day')

SELECT EXTRACT(DAY FROM CAST(TO_CHAR(NOW(),'YYYY-MM')||'-01' AS DATE) + INTERVAL '1 month' - INTERVAL '1 day')
Copy after login

--2. Modify the default search (search_path) path

SHOW search_path;
SET search_path TO your_schema_name;
Copy after login

--3.Change the schema owner

ALTER SCHEMA your_schema_name OWNER TO other_user;
Copy after login

--4.Modify the table owner

ALTER TABLE your_table_name OWNER TO other_user;
Copy after login

--5. Grant query or all permissions to the specified user

GRANT SELECT ON TABLE your_table_name TO other_user;
GRANT ALL ON TABLE your_table_name TO other_user;
Copy after login

--6. Grant the specified user Schema usage permissions

GRANT USAGE ON SCHEMA your_schema_name TO other_user;
Copy after login

--7. Recycle the specified user’s schema usage permissions

REVOKE ALL ON SCHEMA your_schema_name FROM other_user;
Copy after login

--Different object authorization keywords

TABLES,VIEWS,SEQUENCES: SELECT INSERT UPDATE DELETE RULE ALL
EXTERNAL TABLES: SELECT RULE ALL
DATABASES: CONNECT CREATE TEMPORARY|TEMP ALL
FUNCTIONS: EXECUTE
PROCEDURAL LANGUAGES: USAGE
SCHEMAS: CREATE USAGE ALL
Copy after login

--8. Grant function execution permissions in batches

SELECT 'GRANT EXECUTE ON FUNCTION '||routines.routine_name||'('||STRING_AGG(parameters.data_type,',' ORDER BY parameters.ordinal_position)||') TO other_user;' batchgrant
FROM information_schema.routines
JOIN information_schema.parameters ON routines.specific_name=parameters.specific_name
WHERE routines.specific_schema='product'
GROUP BY routines.routine_name
Copy after login

The above is the detailed content of Daily SQL scripts in GreenPlum. For more information, please follow other related articles on the PHP Chinese website!

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template