When my postgres function is tries to create a new sequence, it returns a permission denied error. It looks like the only way to make it work is to give Create permission on schema using the below statement. GRANT CREATE ON SCHEMA public to "myuser" But, this will allow the user to create any other type of object as well, including tables. How can we control this? I want my user to be able to create a sequence, but not tables. Note: Create is not a valid grant on Sequences.

How can I find all sequences (and more widely - all objects in given schema), which are owned not by certain user, on PostgreSQL (10-15)? I found such query:

SELECT relname, relacl 
FROM pg_class pgc
WHERE relkind = 'S'
AND relacl is not null
AND relnamespace IN (
    SELECT oid
    FROM pg_namespace
    WHERE nspname NOT LIKE 'pg_%'
    AND nspname != 'information_schema'
)
and relname = 'my_table_id_seq';

But any further attempts to work with a relacl column fails with errors, f.e.:

array_to_string(array(relacl))

gives an error: ERROR: syntax error at or near "relacl" Also I've found a explodeacl function, which returns rowset of aclitem[], but I cannot get how to use it. Thanks in advance!

How do I view the contents of a network ACL? For example, if I create this ACL, how can I view what settings have been applied to it? DBMS_NETWORK_ACL_ADMIN.CREATE_ACL( acl => 'www.xml', description => 'WWW ACL', principal => 'SCOTT', is_grant => true, privilege => 'connect' );

I read about Oracle ACL, but on first sight, it doesn't look like it limits the access to a schema on terminal, program level. Is it even possible? I did experience such limitation once. I was connecting by sqlplus from unix box, the same box the application scripts were connecting from. Is it, perhaps, related to Oracle Wallet? Goal: I want to limit direct access to an application schema. I already enabled proxy access. However web application on localhost does access application schema, from the same host that I want to limit direct access through any IDE. Is it possible to limit connectivity to schema from the same machine, depending on what terminal, or program wants to connect?

EDIT: I can't create or use native MySQL users. I have to use users stored in table with their password hash. I am new sql stuff so please forgive me for asking dumb question. I am creating my first real life application for college project. At its core, It need to handle more than thousands of users which should not able to read or write to each others data unless given privileges. like Linux does with user and groups. in below schema which I tried , a user can view(read) and edit(write) other users if they have read permissions.( r=2 w=1 r+w=3 ). for example if cgroup_1 is admin and cgroup_2 is managers and unixperm is 32 then it means users in admin group can read+write(3) and users in managers group can only read(2) create table cgroups ( id int unsigned primary key auto_increment, title varchar(100) not null unique, cunixperm tinyint unsigned not null default 32 ,# r=2 w=1 cgroup_1 int unsigned not null default 1 references cgroups (id) on delete cascade on update cascade, cgroup_2 int unsigned references cgroups (id) on delete cascade on update cascade ); create table users ( id int unsigned auto_increment primary key, username varchar(255) not null unique, cunixperm tinyint unsigned not null default 30, # r=2 w=1 3=r+w cgroup_1 int unsigned default 1 not null references cgroups (id) on delete cascade on update cascade , cgroup_2 int unsigned references cgroups (id) on delete cascade on update cascade ); create table many_users_in_many_cgroups ( user_id int unsigned references users(id), cgroup_id int unsigned references cgroups(id), primary key (user_id,cgroup_id) ); insert into cgroups(title) values ('admins'),('managers'),('writers'); insert into users(username, cunixperm, cgroup_1, cgroup_2) values ('user1',30,1,null), ('user2',30,1,2), ('user3',22,2,2), ('user4',02,3,3); insert into many_users_in_many_cgroups values (1,1),(2,2),(3,3),(4,4); Now suppose user 2 has logged into my app, How can I only show the user rows where he has read (2) or read+write(3) permissions. if above schema is not (probably) appropriate pls give me an example with appropriate scheme I am currently using MariaDB but open for solutions for others too.

I have a table t and some functions which access t. For example: create function list_t() returns setof t as $$ select * from t; $$ language sql stable; Is it possible to define role permissions to only allow the execution of list_t(), while not allowing plain SELECT, UPDATE, DELETE and INSERT queries on t? I tried the following: grant execute on all functions in schema public to my_user; revoke all on t from my_user; While this indeed disallows SELECT * FROM t, it also disallows SELECT list_t().

When using pg_dump with a database with non-default schema foo like so:

pg_dump --schema-only --schema='foo' -U myuser -d mydb -h myhost -W > mydb.sql

The SQL dump file is full of these seemingly redundant commands like this:

REVOKE ALL ON schema foo FROM PUBLIC;  -- Doesn't PUBLIC have no privileges by
                                       -- default on a schema created by myuser?
REVOKE ALL ON schema foo FROM myuser;
GRANT ALL ON schema foo TO myuser;  -- Why revoke at all if GRANT ALL is done after?

--
-- Again, why each of these revocation statements when myuser already owns
-- this schema? And why all of these duplicate revocation statements for PUBLIC?
--

REVOKE ALL ON {FUNCTION|TABLE|SEQUENCE} foo.some_thing(...) FROM PUBLIC;
REVOKE ALL ON {FUNCTION|TABLE|SEQUENCE} foo.some_thing(...) FROM myuser;
GRANT ALL ON {FUNCTION|TABLE|SEQUENCE} foo.some_thing(...) TO myuser;

There are numerous functions, sequences, and tables within the schema. So these commands take up a lot of lines and appear almost completely redundant. I am ***not*** asking to remove all ACL commands with the --no-acl flag. I am asking why the ACL commands are so noisy and appear to be mostly redundant. I'd like to simplify them in the database or in pgAdmin3 so that a dump does not have all these lines. In other words, why can't it just be something like:

REVOKE ALL ON SCHEMA foo FROM PUBLIC;  -- If there are any privileges by
                                       -- default for PUBLIC, remove them.

I do ***not*** want to manually write this each time, I am asking if pg_dump can give the desired behaviour. Clearly I can write it myself, but that is not very helpful. Note that this is for a database where I am not the superuser, myuser does not own mydb or the public schema of mydb.

Running Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production. Have a wallet setup containing a basic authentication credential. PL/sql package is executing a request over https to an external server. The acl seems to be setup correctly, as when I issue a request as below, and do some gets, I get results back. D_Request_context := UTL_HTTP.CREATE_REQUEST_CONTEXT( wallet_path => D_Path, wallet_password => NULL, enable_cookies => FALSE ); D_Req := UTL_HTTP.BEGIN_REQUEST( url => D_Url, method => C_METHOD, http_version => C_VERSION, request_context => D_Request_context ) If I call the same code, but include this call UTL_HTTP.SET_AUTHENTICATION_FROM_WALLET( r => D_Req, alias => 'myalias', scheme => C_SCHEME, for_proxy => false ); I get this error: ORA-29273: HTTP request failed ORA-06512: at line 52 ORA-24247: network access denied by access control list (ACL) ORA-06512: at "SYS.UTL_HTTP", line 450 ORA-06512: at "SYS.UTL_HTTP" So why does the call to BEGIN_REQUEST get through the ACL, but the SET_AUTHENTICATION_FROM_WALLET, gets an error?