How to control table ownership

[blade83lee]

I ceate user like that：
root@localhost:8000/default/default> create database bladedb;

create database bladedb

root@localhost:8000/default/default> use bladedb;

use bladedb

root@localhost:8000/default/bladedb> create user bladeuser identified by 'bladeuser';

create user bladeuser identified by 'bladeuser'

root@localhost:8000/default/bladedb> create role bladerole;

create role bladerole

root@localhost:8000/default/bladedb> grant ownership on bladedb.* to role bladerole;

grant ownership on bladedb.* to role bladerole
root@localhost:8000/default/bladedb> grant create,update,insert,delete,alter on bladedb.* to role bladerole;
grant create,
update
,
insert
,
delete,
alter on bladedb.* to role bladerole
root@localhost:8000/default/bladedb> grant role bladerole to bladeuser;

grant role bladerole to bladeuser

then run go scrpts
password := "bladeuser"
encodedPassword := url.QueryEscape(password)
dsn := fmt.Sprintf("http://%s:%s@%s/bladedb", "bladeuser", encodedPassword, "0.0.0.0:8000")
db, err := sql.Open("databend", dsn)
if err != nil {
log.Fatal(err)
}
defer db.Close()
err = db.Ping()
fmt.Println(err,"zzzzzzzzzzzzzzzzzz")
if err != nil {
log.Fatal(err)
}
log.Println("Connected")
_,err = db.Exec("create table testxx(id int)")
fmt.Println(err)
_,err= db.Exec("insert into testxx values(1)")
fmt.Println(err)
fmt.Println(err,"mmmmmmmmmmmmmmmmm")
take a mistake
#1: query error: code: 1063, message: Permission denied: privilege [Create] is required on 'default'.'bladedb'.* for user 'bladeuser'@'%' with roles [public]. Note: Please ensure that your current role have the appropriate permissions to create a new Object

so i do another grant
grant all on bladedb.* to bladeuser;

then run succecc
but other user can use this table

This results in the need to manually modify ownership every time a table is created in the program. Is there any way to automatically isolate it so that it is not visible to other users

[TCeason]

This error: query error: code: 1063, message: Permission denied: privilege [Create] is required on 'default'.'bladedb'.* for user 'bladeuser'@'%' with roles [public]

means the go driver session confirm the user bladeuser only has role public.

You can try this on your local bendsql:

create user bladeuser identified by 'bladeuser' with default_role='bladerole';
create role bladerole;
grant ownership on bladedb.* to role bladerole;
grant role bladerole to user bladerole;
-- use user bladerole create or do any operator under database `default.bladedb`

[TCeason]

For more info about ownership and privilege, can ref:

https://docs.databend.com/guides/security/access-control/ownership
https://docs.databend.com/guides/security/access-control/roles
https://docs.databend.com/guides/security/access-control/privileges

[TCeason]

1: query error: code: 1063, message: Permission denied: privilege [Create] is required on 'default'.'bladedb'.* for user 'bladeuser'@'%' with roles [public]. Note: Please ensure that your current role have the appropriate permissions to create a new Object

If you want to know why the session only has one role public, please provide the complete go code.

[wubx]

I ceate user like that： root@localhost:8000/default/default> create database bladedb;

create database bladedb

root@localhost:8000/default/default> use bladedb;

use bladedb

root@localhost:8000/default/bladedb> create user bladeuser identified by 'bladeuser';

create user bladeuser identified by 'bladeuser'

root@localhost:8000/default/bladedb> create role bladerole;

create role bladerole

root@localhost:8000/default/bladedb> grant ownership on bladedb.* to role bladerole;

grant ownership on bladedb.* to role bladerole root@localhost:8000/default/bladedb> grant create,update,insert,delete,alter on bladedb.* to role bladerole; grant create, update , insert , delete, alter on bladedb.* to role bladerole root@localhost:8000/default/bladedb> grant role bladerole to bladeuser;

grant role bladerole to bladeuser

then run go scrpts password := "bladeuser" encodedPassword := url.QueryEscape(password) dsn := fmt.Sprintf("http://%s:%s@%s/bladedb", "bladeuser", encodedPassword, "0.0.0.0:8000") db, err := sql.Open("databend", dsn) if err != nil { log.Fatal(err) } defer db.Close() err = db.Ping() fmt.Println(err,"zzzzzzzzzzzzzzzzzz") if err != nil { log.Fatal(err) } log.Println("Connected") _,err = db.Exec("create table testxx(id int)") fmt.Println(err) _,err= db.Exec("insert into testxx values(1)") fmt.Println(err) fmt.Println(err,"mmmmmmmmmmmmmmmmm") take a mistake #1: query error: code: 1063, message: Permission denied: privilege [Create] is required on 'default'.'bladedb'.* for user 'bladeuser'@'%' with roles [public]. Note: Please ensure that your current role have the appropriate permissions to create a new Object

so i do another grant grant all on bladedb.* to bladeuser;

then run succecc but other user can use this table

This results in the need to manually modify ownership every time a table is created in the program. Is there any way to automatically isolate it so that it is not visible to other users

The user bladeuser default role is public;

You can use:

alter user bladeuser default_role=''bladerole';

or create user assign default role:

create user bladeuser identified by 'bladeuser' with default_role='bladerole';

[blade83lee]

OK solved 3Q！